Hi The actual statement is MERGE INTO <table> NOT MATCHED, which in PG migrated to WITH - INSERT
however, yes, the SQL-statement in previous does not work in other databases too, I was wrong Thanks, thanks again Sridhar OpenText On Wed, Jun 29, 2016 at 11:58 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > 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 >
