Postgresql – Postgres complex insert or update with 2 unique constraints

postgresql

I have a table that has these key columns. Other columns are present but these are the key columns for this question.

user_id: string,
external_id: string
disabled_by: string

Additional notes:

  1. All columns have the not null constraint.
  2. unique constraint ('external_id_constraint_1') (external_id, disabled_by)
  3. unique constraint ('external_id_constraint_2') (user_id, external_id)

Operations:

Add external id

Adding an new external id for user 1 it would look like this:

insert into external_ids (user_id, external_id, disabled_by) values ('user1', 'exid2', '');

Disabling an external id

If the external id is later revoked for user 1:

update external_ids set disabled_by='admin1' where user_id='user1' and external_id='exid2';

Reenabling the external_id

A user can't have more than 1 entry for a given external_id. If a row exists ('user1', 'exid2', 'admin1') then the row needs to be updated:

update external_ids set disabled_by='' where user_id='user1' and external_id='exid2';

Rather than a new row being created. However another user between the disable and the reenable attempt could have claimed the external_id. If this has happened the update should do nothing.

Solutions attempted

I am trying to get a single postgres sql statement to handle this case.

Things I tried:

  1. an insert into …. on conflict on constraint … except that I need to handle 2 different constraint violations differently.
  2. update does not have the ability to do the insert if there is nothing to update
  3. update doesn't have the ability to handle a constraint violation.

Question:

Is it possible to do this in a single sql statement or am I doomed to be frustrated.

Best Answer

You can combine multiple data-modifying statements by putting them into WITHs:

WITH constraint_1 AS (
  SELECT 1
  FROM external_ids
  WHERE external_id = $2
    AND disabled_by = ''
),
constraint_2 AS (
  UPDATE external_ids
  SET disabled_by = ''
  WHERE user_id     = $1
    AND external_id = $2
    AND disabled_by <> ''
  RETURNING 1
)
INSERT INTO external_ids (user_id, external_id, disabled_by)
SELECT $1, $2, ''
WHERE NOT EXISTS (SELECT * FROM constraint_1)
  AND NOT EXISTS (SELECT * FROM constraint_2);

Like the equivalent stored procedure, this might have races with concurrent updates, depending on the transaction isolation level.

Related Question