Sridhar N Bamandlapally <sridhar.bn1@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
