Sridhar N Bamandlapally <sridhar....@gmail.com> writes:
> postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR);
> postgres=# INSERT INTO emp VALUES (null, 'aaa');
> ...
> postgres=# INSERT INTO emp SELECT * FROM (SELECT 5::integer id,
> 'eee'::varchar ename) nr WHERE id NOT IN (SELECT id FROM emp);
> INSERT 0 0
This is expected. NOT IN can never succeed if there are any nulls
returned by the sub-select, because the nulls represent "unknown",
and so it's unknown whether there is a match to the outer "id"
value, and WHERE takes a null (unknown) result as false not true.
Certainly there are things to quibble with in that behavior, but
it's what's been required by the SQL standard since 1992.
> but this is working with other databases
Really? None that are compliant with the SQL standard, for sure.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
