PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – INSERT WHERE NOT EXISTS query that works for both Postgres and Oracle

I need to create an INSERT INTO ... WHERE NOT EXISTS ... SQL query that works on both Postgres and Oracle. Unfortunately, since oracle does not support SELECT statements without FROM I’m struggling to get this query to work.

I found this answer ( https://stackoverflow.com/a/16639922 ) and implemented my query very similarly:

insert into TABLE (col1, col2) 
select 'A', 100
from dual
where not exists(select * 
                 from TABLE 
                 where (col1='A' and col2=100));

The one part that causes me issues here is the from dual statement. In postgres I would just omit this part and the query works. But for oracle it is necessary to add a FROM statement so I have to select from some table. Unfortunately postgres does not have a DUAL table. Is there any dummy table that I can select from in both cases, or is there an entirely different way to write that query? My initial approach with postgres’ ON CONFLICT DO NOTHING obviously also wasn’t viable for oracle.

Tags:

4

Answers


  1. 0

    this might help

    Equivalence of from dual in PostgreSQL

    seems you don’t need a dual table in Postgresql

    Login or Signup to reply.
  2. 0

    On Postgres you don’t need any actual table or FROM clause:

    INSERT INTO TABLE (col1, col2) 
SELECT 'A', 100
WHERE NOT EXISTS (
    SELECT      -- we don't even need to select anything here
    FROM TABLE 
    WHERE col1 = 'A' AND col2 = 100
);

    Note that in Postgres, we also don’t even need to select anything in the exists clause.

    A general version which should work on both Oracle and Postgres might just put the data into a CTE and then select from that.

    WITH cte AS (
    SELECT 'A' AS col1, 100 AS col2
)

INSERT INTO TABLE (col1, col2) 
SELECT col1, col2
FROM cte
WHERE NOT EXISTS (
    SELECT 1
    FROM TABLE 
    WHERE col1 = 'A' AND col2 = 100
);
    Login or Signup to reply.
  3. 0

    Have a derived table that will always return exactly one row. (A dual similar solution.)

    insert into TABLE (col1, col2) 
select 'A', 100
from (select count(*) from TABLE) dt
where not exists(select * 
                 from TABLE 
                 where (col1='A' and col2=100));

    Demo: https://dbfiddle.uk/m2R2fDOZ

    Login or Signup to reply.
  4. 0

    You can create a view in PostgreSQL to mimic the DUAL table:

    CREATE VIEW dual(dummy) AS
  SELECT dummy FROM (VALUES('X')) AS dual(dummy);

    Then, in both Oracle and PostgreSQL, you can use:

    INSERT INTO table_name (col1, col2) 
SELECT 'A', 100
FROM   DUAL
WHERE  NOT EXISTS(
         SELECT 1
         FROM   table_name
         WHERE  col1='A'
         AND    col2=100
       );

    or, probably more efficiently:

    MERGE INTO table_name dst
USING (
  SELECT 'A' AS col1, 100 AS col2 FROM DUAL
) src
ON (src.col1 = dst.col1 AND src.col2 = dst.col2)
WHEN NOT MATCHED THEN
  INSERT (col1, col2)
  VALUES (src.col1, src.col2);

    Note: From Oracle 23, the FROM clause of a SELECT statement is optional and you can just use SELECT 'A' AS col1, 100 AS col2 and could use identical queries in PostgreSQL and Oracle without the need for a DUAL table.

    PostgreSQL fiddle Oracle fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search