PostgreSQL: Insert if not exist and then Select

Question

Question

Imagine having the following PostgreSQL table:

CREATE TABLE setting (
  user_id bigint PRIMARY KEY NOT NULL,
  language lang NOT NULL DEFAULT 'english',
  foo bool NOT NULL DEFAULT true,
  bar bool NOT NULL DEFAULT true
);

From my research, I know to INSERT a row with the default values if the row for the specific user did not exist, would look something like this:

INSERT INTO setting  (user_id)
    SELECT %s
    WHERE NOT EXISTS (SELECT 1 FROM setting WHERE user_id = %s)

(where the %s are placeholders where I would provide the User's ID)

I also know to get the user's setting (aka to SELECT) I can do the following:

SELECT * FROM setting WHERE user_id = %s

However, I am trying to combine the two, where I can retrieve the user's setting, and if the setting for the particular user does not exist yet, INSERT default values and return those values.

Example

So it would look something like this:

Imagine Alice has her setting already saved in the database but Bob is a new user and does not have it.

When we execute the magical SQL query with Alice's user ID, it will return Alice's setting stored in the database. If we execute the same identical magical SQL query on Bob's user ID, it will detect that Bob does not have any setting saved in the database , thus it will INSERT a setting record with all default values, and then return Bob's newly created setting.

Answer 1

Given that there is an UNIQUE or PK constraint on user_id as Frank Heikens said then try to insert, if it violates the constraint do nothing and return the inserted row (if any) in the t CTE, union it with a 'proper' select and pick the first row only. The optimizer will take care than no extra select is done if the insert returns a row.

with t as
(
 insert into setting (user_id) values (%s)
 on conflict do nothing
 returning *
)
select * from t
union all
select * from setting where user_id = %s
limit 1;

Answer 2

No magic necessary. Use returning and union all:

with inparms as ( -- Put your input parameters in CTE so you bind only once
  select %s::bigint as user_id
), cond_insert as ( -- Insert the record if not exists, returning *
  insert into settings (user_id)
  select i.user_id
    from inparms i
   where not exists (select 1 from settings where user_id = i.user_id)
  returning *
)
select *              -- If a record was inserted, get it
  from cond_insert
union all
select s.*            -- If not, then get the pre-existing record
  from inparms i
       join settings s on s.user_id = i.user_id;