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

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