Is it possible to capture the ID of a created row in postgres to use in the same query?

Question

TL;DR, I’m wondering if it’s possible in postgres to create one resource, then create another resource that references it, all within the same statement. I’d like to do this in order to reduce round-trip times and simplify my backend logic.

I currently have two resources, foo and bar. Bar is a child resource of foo that references it:

CREATE TABLE foos (
    id serial PRIMARY KEY,
    name VARCHAR ( 50 ) UNIQUE NOT NULL
);

CREATE TABLE bars (
    id serial PRIMARY KEY,
    name VARCHAR ( 50 ) NOT NULL,
    description VARCHAR ( 50 ),
    foo_id int NOT NULL,
    FOREIGN KEY (foo_id)
      REFERENCES foos (id),
    UNIQUE (foo_id, name) 
);

I currently have the following sql statement to upsert a foo and bar at an endpoint v1/foos/{foo_name}/bars/{bar_name}:

WITH upsert_foo AS
    (
        INSERT INTO "foos" 
        ("name")
        VALUES 
        ($1)
        ON CONFLICT ("name") DO NOTHING
    ),
     upsert_bar AS
      (
         INSERT INTO "bars" 
         ("name", "description", "foo_id" )
         VALUES 
         ($2, $3,   
            (SELECT "id" FROM "foos" WHERE "name" = $1) )
        ON CONFLICT ("name", "foo_id") DO 
        UPDATE SET
                 "description" = EXCLUDED."description",
            RETURNING *
    )
    SELECT upsert_bar.*;

When a user upserts a bar, a foo with the name from the URI should be created if it doesn’t already exist, then the bar should be created if it doesn’t exist. Otherwise, the bar should be updated.

However, this statement only works in the update case, when the foo resource already exists. It seems that the new foo isn't showing up in (SELECT "id" FROM "foos" WHERE "name" = $1) when it's newly created.

Is there a way that I can capture the ID of the foo that was created (or already exists) and use it when I create the bar object?

First the table names don't match those in the query, `foo/bar` vs `foos/bars`. Second, `ON CONFLICT ("name") DO NOTHING RETURNING id`. — Adrian Klaver, Dec 14 '20 at 20:44
Sure, I'll update it. I changed the table names to be generic and missed that inconsistency! I'll try returning id. — FrolickingFerret445, Dec 14 '20 at 20:47
@AdrianKlaver It seems that if I try returning id, the id is null in the event that the foo already exists. Or am I missing something? — FrolickingFerret445, Dec 14 '20 at 21:02
It would as you have `DO NOTHING` so no `UPDATE` happens and no `id` is returned. It comes down to how are you providing the values to the `$*` parameters? The bigger issue now that I come to think of it, is that in CTEs the `WITH` queries as a practical matter occur at the same time. So the `UPDATE` portion is not going to 'see' the INSERT anyway. Am going to have to think about this some more. — Adrian Klaver, Dec 14 '20 at 21:09

score 3 · Answer 1 · answered Dec 14 '20 at 21:53

3

The only I can think to do is is break it down into separate operations:

INSERT INTO 
   "foos" ("name")
VALUES 
    ($1)
ON CONFLICT ("name") DO UPDATE 
    SET name = foos.name
RETURNING id;

The above has a throw away UPDATE for purpose of capturing id value. Save this value to variable for use as $4 below.

Then:

INSERT INTO 
   "bars" ("name", "description", "foo_id" )
VALUES 
   ($2, $3, $4)
ON CONFLICT ("name", "foo_id") DO UPDATE 
    SET "description" = EXCLUDED."description"
RETURNING *

answered Dec 14 '20 at 21:53

Adrian Klaver

15,886
2
17
28

I like this, but I'm not a fan of updating when there's nothing to update. Check out my answer, and check out the link in my answer. It has an answer that talks about why updating when there's nothing to update might have unintended consequences. But I really appreciate the help with all of this. – FrolickingFerret445 Dec 14 '20 at 22:59

score 1 · Answer 2 · answered Dec 14 '20 at 22:53

I managed to get this working in a single statement, but it was a bit complicated:

WITH insert_foo AS
(
    INSERT INTO "foos" 
    ("name")
    VALUES 
    ($1)
    ON CONFLICT ("name") DO NOTHING RETURNING *
),
upsert_foo AS
(
    SELECT * FROM insert_foo
    UNION
    SELECT * FROM foos WHERE "name"=$1
),
upsert_bar AS
  (
     INSERT INTO "bars" 
     ("name", "description", "foo_id" )
     VALUES 
     ($2, $3, (SELECT "id" FROM upsert_foo WHERE name=$1) )
    ON CONFLICT ("name", "foo_id") DO 
    UPDATE SET
             "description" = EXCLUDED."description"
    RETURNING *
)
SELECT * FROM upsert_bar;

Basically, the result from the insert should be unioned with a regular select query, with only one of them containing a row, depending on whether the foo already existed or not. I can then select the id from the result of this union query.

I want to make a huge shout out to Adrian Klaver for pointing me in the right direction and also this answer: https://stackoverflow.com/a/62205017/4600258

Is it possible to capture the ID of a created row in postgres to use in the same query?

2 Answers2