CODEWITHSUNDEEP
.netpostgresql
user34537
user34537

Reputation:

How do I insert and return the ID in postgresql?

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)

Upvotes: 0

Views: 61

Answers (2)

Frank Heikens
Frank Heikens

Reputation: 127456

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);

Upvotes: 0

klin
klin

Reputation: 121834

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;

Upvotes: 1

Related Questions