Pretty straight forward. I guess I could do an insert on conflict do nothing then a select but I like one liners. There may be a conflict because c1 is unique. I'd like to insert then return the id of whatever row has column1 (c1).
insert into foo(c1)
VALUES(@c1, 0)
on conflict (c1) do nothing
RETURNING (select fooid from foo where c1=@c1)
When the clause do nothing is reached the query returns no rows. The only way to return fooid in this case is to execute redundant update:
insert into foo (c1)
values (@c1)
on conflict (c1) do update set c1 = excluded.c1
returning fooid;
excluded, like in the updated answer.You could use a common table expression to avoid the redundant update:
WITH payload(c1) AS (
SELECT @c1 -- your parameters here, you might have to CAST() them
), insert_action(c1) AS (
INSERT INTO foo(c1)
SELECT c1 FROM payload
ON CONFLICT (c1) DO NOTHING
RETURNING c1
)
SELECT c1
FROM insert_action
RIGHT JOIN payload USING(c1);
