Postgres Insert select using WITH clause

Question

I'm trying to create some entries by using an INSERT ... SELECT ..., but also need to use the primary key from the 1st INSERT operation to be included as part of a secondary operation:

WITH dogEntries as (INSERT INTO dog (id, another_id, name, date_created)
    SELECT public.uuid_generate_v4(), 'efd55343', name, date_created
    FROM dog WHERE owner_id = '8921571' RETURNING id as dog_uuid)

INSERT INTO dog_toys (dog_id, bed_id, status)
SELECT (SELECT dog_uuid FROM dogEntries), bed_id, status FROM dog_toys 
WHERE dog_id IN(SELECT dog_id FROM dogs WHERE another_id = '21571');

public.uuid_generate_v4() will generate the id column.

But I'm getting an error, not sure but I guess is because the WITH dogEntries is of course returning multiple entries.

The error:

[21000] ERROR: more than one row returned by a subquery used as an expression

Is there any alternative of returning and iterate over the dog table inserted rows, or maybe a hint on how to traverse each of the entries from the WITH dogEntries statement?

But I'm getting an error ... So show the verbatim error message. And always your version of Postgres. And table definitions. Is public.uuid_generate_v4() supposed to go into id or another_id? — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 12, 2021 at 22:37
Show your CREATE TABLE statements. Then we know where multiple rows can originate. Seems like your owners can have multiple dogs. And tell us what the statement is supposed to achieve exactly. Is it supposed to make a single entry in each table? — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 12, 2021 at 22:50
I'm trying to create copies for all the entries related to the owner_id = '8921571', then use those recently created dog entries (using its dog_id) to create dog_toys entries. — Diego Nieto
– Diego Nieto, Commented Aug 12, 2021 at 22:58

Erwin Brandstetter · Accepted Answer · 2021-08-13 00:20:10Z

1

(SELECT dog_uuid FROM dogEntries) in the SELECT list of the 2nd INSERT causes the error message as it returns multiple rows. It might work like this:

WITH dogentries AS (
   INSERT INTO dog (id, another_id, name, date_created)
   SELECT public.uuid_generate_v4(), 'efd55343' name, date_created
   FROM   dog
   WHERE  owner_id = 8921571
   RETURNING id AS dog_uuid
   )
INSERT INTO dog_toys (dog_id, bed_id, status)
SELECT de.dog_uuid, dt.bed_id, dt.status
FROM   dogentries de
JOIN   dog d ON d.another_id = 21571
JOIN   dog_toys dt USING (dog_id);

This is a shot in the dark. The objective is not entirely clear, and table definitions are undisclosed.

edited Aug 13, 2021 at 0:20

answered Aug 12, 2021 at 22:45

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Insert select using WITH clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related