[GENERAL] Sub-query having NULL row returning FALSE result

[Sridhar N Bamandlapally]

Hi

Please go through below case

postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR);
CREATE TABLE
postgres=# INSERT INTO emp VALUES (null, 'aaa');
INSERT 0 1
postgres=# INSERT INTO emp VALUES (null, 'bbb');
INSERT 0 1
postgres=# INSERT INTO emp VALUES (3, 'ccc');
INSERT 0 1
postgres=# INSERT INTO emp VALUES (4, 'ddd');
INSERT 0 1
postgres=# SELECT * FROM emp ;
 id | ename
----+-------
    | aaa
    | bbb
  3 | ccc
  4 | ddd
(4 rows)

postgres=# SELECT * FROM (SELECT 5::integer id, 'eee'::varchar ename) nr;
 id | ename
----+-------
  5 | eee
(1 row)

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
postgres=# SELECT * FROM emp ;
 id | ename
----+-------
    | aaa
    | bbb
  3 | ccc
  4 | ddd
(4 rows)

postgres=#


The application is generating SQL-Statement to avoid exception while
inserting

The expected behavior is to INSERT row if the NEW id is not existing in
table, but this is doing FALSE

Please advise me if am doing something wrong here or any alternate

but this is working with other databases


Thanks
Sridhar