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.

CodePudding user response：

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;

CodePudding user response：

No magic necessary. Use returning and union all:

with inparms as (
  select %s::bigint as user_id
), cond_insert as (
  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 *
  from cond_insert
union all
select s.*
  from inparms i
       join settings s on s.user_id = i.user_id;