Insert (if not present) and return id

Question

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.

https://stackoverflow.com/q/39779145/330315 – Oct 26 '17 at 05:53 — , Oct 26 '17 at 05:53
https://stackoverflow.com/q/24050779/330315 – Oct 26 '17 at 05:53 — , Oct 26 '17 at 05:53

score 4 · Answer 1 · answered Oct 26 '17 at 11:59

4

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

answered Oct 26 '17 at 11:59

Clodoaldo Neto

118,695
26
233
260

Insert (if not present) and return id

1 Answers1

Linked