Hi Just for info.
Actual query in Oracle (below) ---------------------------------- MERGE INTO relDocumentTypeMetaDataName t USING (SELECT ? as DocumentTypeID, ? as DocumentContextID, ? as MetaDataNameID, ? as DocumentAbstractionIndexID FROM DUAL) s ON (t.MetaDataNameID = s.MetaDataNameID AND t.DocumentTypeID = s.DocumentTypeID) WHEN NOT MATCHED THEN INSERT (DocumentTypeID, DocumentContextID, MetaDataNameID, FlatColumnNo) values (s.DocumentTypeID, s.DocumentContextID, s.MetaDataNameID, (select nvl(max(FlatColumnNo), 0) + 1 FROM relDocumentTypeMetaDataName WHERE DocumentTypeID = ?)); Migrated query in PG (phase 1) --------------------------------- WITH s AS (SELECT ? as DocumentTypeID, ? as DocumentContextID, ? as MetaDataNameID, ? as DocumentAbstractionIndexID) INSERT INTO relDocumentTypeMetaDataName (DocumentTypeID, DocumentContextID, MetaDataNameID, FlatColumnNo, DocumentAbstractionIndexID) SELECT s.DocumentTypeID, s.DocumentContextID, s.MetaDataNameID, (SELECT coalesce(MAX(FlatColumnNo), 0) + 1 FROM relDocumentTypeMetaDataName WHERE DocumentTypeID = ?), s.DocumentAbstractionIndexID FROM s WHERE s.DocumentTypeID NOT IN (SELECT DocumentTypeID FROM relDocumentTypeMetaDataName); Migrated query in PG (phase 2) - after Tom Lane reply -------------------------------------------------------- WITH s AS (SELECT ? as DocumentTypeID, ? as DocumentContextID, ? as MetaDataNameID, ? as DocumentAbstractionIndexID) INSERT INTO relDocumentTypeMetaDataName (DocumentTypeID, DocumentContextID, MetaDataNameID, FlatColumnNo, DocumentAbstractionIndexID) SELECT s.DocumentTypeID, s.DocumentContextID, s.MetaDataNameID, (SELECT coalesce(MAX(FlatColumnNo), 0) + 1 FROM relDocumentTypeMetaDataName WHERE DocumentTypeID = ?), s.DocumentAbstractionIndexID FROM s WHERE s.DocumentTypeID NOT IN (SELECT DocumentTypeID FROM relDocumentTypeMetaDataName WHERE DocumentTypeID IS NOT NULL); Thanks Sridhar OpenText On Wed, Jun 29, 2016 at 6:04 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Jun 29, 2016 at 2:07 AM, Sridhar N Bamandlapally < > sridhar....@gmail.com> wrote: > >> 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 >> > > Subjectively, you are allowing an ID field to be NULL. That, for me, is > wrong. > > Given this, as a follow-up to what Tom said, you need to decide what you > wish to happen for your NULL IDs. Until you explain that behavior it is > not possible to provide valid alternatives. > > Usually you want to use "EXISTS", not "IN" > > > https://www.postgresql.org/docs/9.6/static/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS > > Oh, and try adding "WHERE id IS NOT NULL" > > David J. > >
