Write a Postgres Get or Create SQL Query

Question

I want to write a single Postgres SQL statement that says look for a user with color X and brightness Y. If that user exists, return all of its row data. If not, create a new row and pass additional information. The two separate statements would do something like this:

Select (color, brightness, size, age) FROM mytable WHERE color = 'X' AND brightness= 'Y';

If that doesn't return anything, then execute this:

INSERT INTO mytable (color, brightness, size, age) VALUES (X, Y, big, old);

Is there a way to combine these into a single query??

Seeing the `RETURNING` clause extension of postgresql on `INSERT` statements, I initially hoped one could combine an insert with a select in one statement using a union, but I tried it and unfortunately reads and writes are really impossible to mix that way. — didierc, Apr 20 '13 at 19:43

score 34 · Accepted Answer · edited Apr 08 '22 at 23:57

34

In a SQL DBMS, the select-test-insert approach is a mistake: nothing prevents another process from inserting the "missing" row between your select and insert statements. Do this instead:

INSERT INTO mytable (color, brightness, size, age)
SELECT color, brightness, size, age 
FROM mytable
WHERE NOT EXISTS (
    SELECT 1
    FROM mytable
    WHERE color = 'X' AND brightness = 'Y'
);
SELECT (color, brightness, size, age) 
FROM mytable 
WHERE color = 'X' AND brightness= 'Y';

You should be able to pass that entire text as a single "query" to the DBMS. You might want to consider making it into a stored procedure.

edited Apr 08 '22 at 23:57

Laszlo Treszkai

332
1
12

answered Apr 21 '13 at 04:52

James K. Lowden

7,574
1
16
31

1

Related to this answer, and useful for better understanding: http://stackoverflow.com/a/13342031/399726 – BenjaminGolder Nov 11 '15 at 21:55
6

Cannot understand how this answer has collected so many votes containing a syntax error. There is no option `insert ... where ...` for INSERT in Postgres. You probably meant `insert into mytable (color, brightness, size, age) SELECT 'X', 'Y', 1.2, 3.4 where not exists...`. – greatvovan Nov 21 '17 at 17:14
I agree that select-test-insert is not safe but what about atomic insert? I think if you lock the row, then there won't be any problems with the select-test-insert approach. – therealak12 Sep 22 '21 at 08:10
1

@therealak12 If the row doesn't exist then there is nothing to lock, so you'd need a table lock. – Laszlo Treszkai Apr 08 '22 at 23:03
@LaszloTreszkai Good point! – therealak12 Apr 09 '22 at 04:39
I'm not a SQL expert but this query still seems wrong. I'm confused about the third line – `FROM mytable` – which causes every row to be duplicated in my testing. If I remove that line then it works as expected. – Tamlyn Mar 09 '23 at 16:22

score 8 · Answer 2 · answered Apr 20 '13 at 20:51

with sel as (
    select color, brightness, size, age
    from mytable
    where color = 'X' and brightness = 'Y'
), ins as (
    insert into mytable (color, brightness, size, age)
    select 'X', 'Y', 6.2, 40
    where not exists (
        select 1 from sel
    )
    returning color, brightness, size, age
)
select color, brightness, size, age
from ins
union
select color, brightness, size, age
from sel

score 5 · Answer 3 · answered Jan 02 '17 at 16:43

Adding my solution here. It is a tad different than @Clodoaldo Neto and @astef's solutions.

WITH ins AS (
  INSERT INTO mytable (color, brightness, size, age)
  VALUES ('X', 'Y', 'big', 'old')
  ON CONFLICT (color) DO NOTHING
  RETURNING *
)
SELECT * FROM ins
UNION
SELECT * FROM mytable
  WHERE color = 'X';

I found astef's solution inadequate for my purposes: it doesn't perform the "get" portion of "get or create"! If the value already existed, nothing would happen.

The union at the end of the statement ensures that if the value was not inserted (since it already existed) we still retrieve that value from the table.

I found an issue with the above: If there is a conflict, your table's serial ID counter will increment on each execution of the query. This isn't ideal. — Brian Ambielli, Mar 15 '17 at 02:13

score 4 · Answer 4 · edited May 03 '22 at 05:38

4

If your columns participate in unique index constraint you can use an approach which is avaible since version 9.5:

INSERT INTO mytable (color, brightness, size, age)
VALUES ('X', 'Y', 'big', 'old')
ON CONFLICT (color) DO NOTHING;

(assuming you have unique index on color).

Docs are here: postgresql 9.5

edited May 03 '22 at 05:38

Cabrera

1,670
1
16
16

answered Aug 01 '16 at 10:06

astef

8,575
4
56
95

3

This does not answer the question, which asks for how to *get* or *create* a row. – Druska Mar 08 '18 at 17:26
It does. You do this and after this you do SELET, which then always succeeds. So you do "(insert or nothing) and get". – Mitar Nov 19 '20 at 23:03
@Mitar...it does not. You are assuming that there's something to conflict on, thus this is an Upsert query...UPDATE or INSERT. What they're asking for specifically is a GET or CREATE. Meaning you would like to query for some row and if it doesn't exist create a new one...there might not be any conflicts at all. For instance you might want to query for a row with a particular ID and if it doesn't exist you just want create a new one with default values (randomly generated new ID) – Rik Nauta Jun 14 '21 at 08:36

score 0 · Answer 5 · answered Oct 27 '22 at 04:05

0

You can simplify it like this.

INSERT INTO table (col1, col2, col3) 
VALUES (val1, val2, val3)
ON
 CONFLICT conflict_target conflict_action;

answered Oct 27 '22 at 04:05

Wadson Vaval

91
1
7

Write a Postgres Get or Create SQL Query

5 Answers5

Linked