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 – a_horse_with_no_name Oct 26 '17 at 05:53 — a_horse_with_no_name, Oct 26 '17 at 05:53
https://stackoverflow.com/q/24050779/330315 – a_horse_with_no_name Oct 26 '17 at 05:53 — a_horse_with_no_name, 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

108,856
25
211
247

Insert (if not present) and return id

1 Answers1

Linked