Postgres: Insert if not exists, otherwise return the row [duplicate]

Question

In one SQL statement, I am trying to insert a row, and if that fails due to a constraint, then return the existing row.

I have:

INSERT INTO session (token, expiry) SELECT 'abcdefg', '2014-05-14 20:25:12.279667' WHERE  NOT EXISTS (SELECT * FROM session where token= 'abcdefg');

The token column has a unique constraint. I've tried appending RETURNING * at the end, but that still does not return the existing row.

Why is this? I thought my last SELECT statement will be executed and returned. Any ideas?

Note: I cannot use a Postgres function or multiple SQL statements due to some complicated race condition.

Eelke · Accepted Answer · 2023-12-15 16:39:13Z

5

WITH d(t, e) AS ( VALUES ('abcdefg', '2014-05-14 20:25:12.279667')),
  t AS (SELECT token FROM session, d WHERE token=t),
  i AS (INSERT INTO session (token, expiry) 
           SELECT t, e FROM d WHERE t NOT IN (SELECT token FROM t))
SELECT t,e FROM d WHERE t IN (SELECT token FROM t);

The query first makes a CTE (Common Table Expression) d with the data you want to insert

Then it makes a CTE t with the token in session that match the token in d (or empty if no match).

Then it uses CTE i to insert the row from d if it has no match in t into session.

And finally it returns the row from d if it did have a match in t.

BTW this also works with multiple rows.

edited Dec 15, 2023 at 16:39

answered May 10, 2014 at 6:52

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SepSol Over a year ago

What does CTE mean?

Eelke Over a year ago

@SepSol Common Table Expression, I have added it in the answer also

Collectives™ on Stack Overflow

Postgres: Insert if not exists, otherwise return the row [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related