CODEWITHSUNDEEP
sqlpostgresql
Eric H.
Eric H.

Reputation: 7014

Write a Postgres Get or Create SQL Query

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??

Upvotes: 23

Views: 18114

Answers (5)

Wadson Vaval
Wadson Vaval

Reputation: 111

You can simplify it like this.

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

Upvotes: 0

astef
astef

Reputation: 9518

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

Upvotes: 6

James K. Lowden
James K. Lowden

Reputation: 7837

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.

Upvotes: 36

Brian Ambielli
Brian Ambielli

Reputation: 601

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.

Upvotes: 6

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125564

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

Upvotes: 10

Related Questions