CODEWITHSUNDEEP
postgresqlpostgresql-9.5
Hatshepsut
Hatshepsut

Reputation: 6652

Insert (if not present) and return id

I have a unique column. I want to insert a row if it's not already there, and then return the id of that row.

INSERT INTO t(a) VALUES ('a') ON CONFLICT DO NOTHING RETURNING t.id;

returns nothing at all. Here's a fiddle.
I'm looking for how to get 1 each time, whether 'a' was newly inserted or not.

Upvotes: 0

Views: 2189

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

with i as (
    INSERT INTO t(a) VALUES ('a') ON CONFLICT (a) DO NOTHING RETURNING id
)
select id from i
union all
select id from t where a = 'a'
limit 1

Upvotes: 4

Related Questions