Get or Create record with single query

Question

I have a table with column Id,column1,column2.

I wants to make sure only one record exists with column1 and column2 value (column1+column2 is unique).

If there is an already record exists with column1=v1 and column2=v2 then I wants to select the Id or insert a record with column1=v1 and column2=v2. I do not want to first select and then check and then insert since there could be a chance that 2 request came with same time with same values, both should be success but only one record should get created in DB and after that i want the Id in both request.

How to do this with single SQL ? Or is there any other better approach ?

Database : Postgres

You should have specified the database you are using. In a general way you could use where not exists (...). — Cetin Basoz, Nov 12 '20 at 10:58
Are you looking for something like this: https://stackoverflow.com/questions/6722344/select-or-insert-a-row-in-one-command or https://stackoverflow.com/questions/15939902 — a_horse_with_no_name, Nov 12 '20 at 11:36

score 1 · Answer 1 · answered Nov 12 '20 at 11:47

I think the logic you are describing is a statement that attempts to insert, skips if there are duplicates, and returns the corresponding id in both cases (regardless of whether the insert actually happened or not).

For this to work, you need to set up a unique key on col1 and col2, so duplicates are not allowed. Your create table statement would look like:

create table mytable (
    id serial primary key,
    col1 text,
    col2 text,
    unique (col1, col2)
);

Then, you can write a returning insert query as follows:

with 
    -- query parameters
    params as (select $1 col1, $2 col2),
    -- insert, or skip if the row already exists
    ins as (
        insert into mytable (col1, col2) 
        select * from params
        on conflict (col1, col2) do nothing
    )
-- return the id
select t.id 
from mytable t
inner join params p on p.col1 = t.co1 and p.col2 = t.col2

Get or Create record with single query

1 Answers1