How to Insert Multiple Rows with a Foreign Key Using a Cte in Postgres

How do I insert multiple rows with a foreign key using a CTE in Postgres?

The following is a reasonable interpretation of what you want to do:

with i as (
      insert into products (title, description, price)
          values ('Dope product 1', 'Buy diz', 9.99),
                 ('Dope product 2', 'Buy diz', 8.99),
                 ('Dope product 3', 'Buy diz', 7.99)
          returning *
     ) 
insert into product_metadata (product_id, sales_volume, date)
    select i.product_id, v.sales_volume, v.date
    from (values ('Dope product 1', 80, '2017-03-21'),
                 ('Dope product 2', 50, '2017-03-21'), 
                 ('Dope product 3', 70, '2017-03-21')
         ) v(title, sales_volume, date) join
         i
         on i.title = v.title;

The basic answer is "use returning * and use a join to get the values". I needed to change the titles so they are unique.

Insert multiple rows using the same foreign key that needs to be selected

Your query works just fine. The only way it inserts zero rows is if there is no product in the table products for a given string - in your query named My product name. However, @a_horse_with_no_name's suggestion to use a CROSS JOIN might simplify your query a bit. You can combine it with a CTE to collect all comments and then CROSS JOIN it with the record you filtered in from table products.

CREATE TABLE products (id SERIAL, name TEXT);
CREATE TABLE comments (id SERIAL, product_id INT, txt TEXT);    
INSERT INTO products VALUES (1, 'My product name'),(2,'Another product name');

WITH j (txt) AS (
  VALUES ('Great product'),('I love it'),('another comment')
) 
INSERT INTO comments (product_id,txt)
SELECT id,j.txt FROM products
CROSS JOIN j WHERE name = 'My product name';

SELECT * FROM comments;  
 id | product_id |       txt       
----+------------+-----------------
  1 |          1 | Great product
  2 |          1 | I love it
  3 |          1 | another comment

Check this db<>fiddle

Postgres - insert and composite foreign keys

You have to use the below syntax to insert records, if 'my code' is always is the static

INSERT INTO table(a, b, code)
SELECT a, b, 'my code' FROM other-table WHERE ...

If you have multiple table, then you can use syntax like this using CTE

INSERT INTO table(a, b, c, d, code)
WITH t1 AS (
    SELECT a, b FROM other-table-1 WHERE ...
  ), t2 AS (
    SELECT c, d FROM other-table-2 WHERE ...
  )
select t1.a, t1.b, t2.c, t2.d, 'my code' from t1,t2

Inserting a row and related rows with autoincrement primary key together

Use a data-modifying CTE:

WITH ins1 AS (
   INSERT INTO image  ...
   RETURNING id
   )
INSERT INTO imagesize (..., image_id)
SELECT ..., i.id
FROM   ins1 i;

More explanation:

• PostgreSQL store value returned by RETURNING
• INSERT rows into multiple tables in a single query, selecting from an involved table

Your solution

CROSS JOIN to a VALUES expression instead of multiple SELECT statements:

WITH new_img AS (
   INSERT INTO image (description)
   VALUES ('An awesome image')
   RETURNING id
   )
INSERT INTO imagesize (image_id, name, url) 
SELECT i.id, v.name, v.url
FROM   new_img i
CROSS  JOIN (
   VALUES
     ('small' , 'http://example.com/img_2_small.jpg')
    ,('medium', 'http://example.com/img_2_medium.jpg')
    ,('large' , 'http://example.com/img_2_large.jpg')
   ) v (name, url);

Postgres: inserting as many rows as a value on another table

It could be done via Inner Join Lateral with generate_series.

Insert Into Tickets (user_id)
Select user_id
From Users Inner Join Lateral generate_series(1, Quantity) As t On true
Order by user_id

PostgreSQL - Insert data into multiple tables simultaneously

The idea is to write WITH clauses that contain INSERT ... RETRUNING to return the generated keys. Then these “views for a single query” can be used to insert those keys into the referencing tables.

WITH par_key AS
        (INSERT INTO participante (nome) VALUES ('Laurenz') RETURNING id),
     ven_key AS
        (INSERT INTO venda (inicio) VALUES (current_date) RETURNING id),
     item_key AS
        (INSERT INTO item (nome) VALUES ('thing') RETURNING id)
INSERT INTO lances_vendas (venda_id, item_id, participante_id, valor)
   SELECT ven_key.id, item_key.id, par_key.id, numeric '3.1415'
   FROM par_key, ven_key, item_key;

---

Related Topics