| Lists: | pgsql-novice |
|---|
| From: | Juan Francisco Diaz <j-diaz(at)publicar(dot)com> |
|---|---|
| To: | <pgsql-novice(at)postgresql(dot)org> |
| Subject: | Using NOT IN |
| Date: | 2003-09-03 22:02:01 |
| Message-ID: | BB7BCF09.334%j-diaz@publicar.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-novice |
Hello, ive been all day trying to find out why a jdbc query does not work...
I traced the error to the function I created. The function is written using
plpgsql, returns an integer and the execution code is like:
If CAST(S AS CHAR(6)) not in (SELECT ....)
insert into table(...) values(...)
Endif
The problem is the insert statement never executees. It is not the subquery
after the NOT IN, i already tried it and it works ok, returns wha it is
supposed to return, the same type as 'S' that is CHAR(6).
When i need it executed, the subquery returns 0 rows, maybe th if condition
is false because the not in operator does not 'work' if there are 0 rows...
I dont know what happens, any help greatly appreciated...
JuanF
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Juan Francisco Diaz <j-diaz(at)publicar(dot)com> |
| Cc: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Re: Using NOT IN |
| Date: | 2003-09-04 03:22:50 |
| Message-ID: | 6915.1062645770@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-novice |
Juan Francisco Diaz <j-diaz(at)publicar(dot)com> writes:
> When i need it executed, the subquery returns 0 rows, maybe th if condition
> is false because the not in operator does not 'work' if there are 0 rows...
More likely, you have some null values in the sub-select. NOT IN has
fairly unintuitive behavior in the presence of nulls. See the manual.
regards, tom lane