Thread: Using NOT IN

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

Juan Francisco Diaz <j-diaz@publicar.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