Multiple row insert or select if exists

Question

CREATE TABLE object (
  object_id serial,
  object_attribute_1 integer,
  object_attribute_2 VARCHAR(255)
)

-- primary key object_id
-- btree index on object_attribute_1, object_attribute_2

Here is what I currently have:

SELECT * FROM object 
WHERE (object_attribute_1=100 AND object_attribute_2='Some String') OR
(object_attribute_1=200 AND object_attribute_2='Some other String') OR
(..another row..) OR
(..another row..)

When the query returns, I check for what is missing (thus, does not exist in the database).

Then I will make an multiple row insert:

INSERT INTO object (object_attribute_1, object_attribute_2) 
VALUES (info, info), (info, info),(info, info)

Then I will select what I just inserted

SELECT ... WHERE (condition) OR (condition) OR ...

And at last, I will merge the two selects on the client side.

Is there a way that I can combine these 3 queries, into one single queries, where I will provide all the data, and INSERT if the records do not already exist and then do a SELECT in the end.

score 1 · Accepted Answer · edited May 23 '17 at 11:57

Your suspicion was well founded. Do it all in a single statement using a data-modifying CTE (Postgres 9.1+):

WITH list(object_attribute_1, object_attribute_2) AS (
   VALUES
      (100, 'Some String')
    , (200, 'Some other String')
    ,  .....
   )
, ins AS (
   INSERT INTO object (object_attribute_1, object_attribute_2)
   SELECT l.*
   FROM   list l
   LEFT   JOIN object o1 USING (object_attribute_1, object_attribute_2)
   WHERE  o1.object_attribute_1 IS NULL
   RETURNING *
   )
SELECT * FROM ins   -- newly inserted rows

UNION ALL           -- append pre-existing rows
SELECT o.*
FROM   list l
JOIN   object o USING (object_attribute_1, object_attribute_2);

Note, there is a tiny time frame for a race condition. So this might break if many clients try it at the same time. If you are working under heavy concurrent load, consider this related answer, in particular the part on locking or serializable transaction isolation:
Postgresql batch insert or ignore

I had a feeling that you would come to my postgresql rescue once again. Thank you again! Do you have a twitter account? — samol, Feb 27 '14 at 22:50
@alumns: Consider the update. My first draft returned only newly inserted rows. — Erwin Brandstetter, Feb 27 '14 at 22:55

Multiple row insert or select if exists

1 Answers1