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

Answer 1

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

Answer 2

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.

Answer 3

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