Postgres use SELECT query to INSERT records

Question

I have a query that returns about 1k results. A single column of orders.id

SELECT orders.id
FROM orders
LEFT JOIN uploads
ON uploads.order = orders.id
WHERE uploads.order IS NULL
AND orders.product_type = 'warrior_workout'

I also have this INSERT query:

INSERT INTO uploads ( "order", name, url, created_at, updated_at,  file_name, is_main ) 
VALUES 
    ( orders.id, 'warrior_workout_1','https://example.com/upload1.pdf', now(), now(), 'upload1.pdf', TRUE ),
    ( orders.id, 'warrior_workout_2','https://example.com/upload2.pdf', now(), now(), 'upload2.pdf', TRUE ),
    ( orders.id, 'warrior_workout_3','https://example.com/upload3.pdf', now(), now(), 'upload3.pdf', TRUE )

I need to perform the insert query for each returned ID from the first query. How can I combine these to get the desired result?

Can you mention whether the numerical parts in `warrior_workout_1` and `upload1.pdf` are dependent on the orders id? — Udayraj Deshmukh, Feb 12 '18 at 21:30
Then this is what you want - https://stackoverflow.com/questions/25969/insert-into-values-select-from . I'm writing the relevant excerpt below to answer your question — Udayraj Deshmukh, Feb 12 '18 at 22:51

score 1 · Answer 1 · answered Feb 12 '18 at 22:28

here is comprehensive example:

t=# with i ( name, url, created_at, updated_at,  file_name, is_main ) as
(VALUES
    (  'warrior_workout_1','https://example.com/upload1.pdf', now(), now(), 'upload1.pdf', TRUE ),
    (  'warrior_workout_2','https://example.com/upload2.pdf', now(), now(), 'upload2.pdf', TRUE ),
    (  'warrior_workout_3','https://example.com/upload3.pdf', now(), now(), 'upload3.pdf', TRUE ))
select gs,i.* from i
join generate_series(1,4) gs on true order by gs, name;
 gs |       name        |               url               |          created_at           |          updated_at           |  file_name  | is_main
----+-------------------+---------------------------------+-------------------------------+-------------------------------+-------------+---------
  1 | warrior_workout_1 | https://example.com/upload1.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload1.pdf | t
  1 | warrior_workout_2 | https://example.com/upload2.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload2.pdf | t
  1 | warrior_workout_3 | https://example.com/upload3.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload3.pdf | t
  2 | warrior_workout_1 | https://example.com/upload1.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload1.pdf | t
  2 | warrior_workout_2 | https://example.com/upload2.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload2.pdf | t
  2 | warrior_workout_3 | https://example.com/upload3.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload3.pdf | t
  3 | warrior_workout_1 | https://example.com/upload1.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload1.pdf | t
  3 | warrior_workout_2 | https://example.com/upload2.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload2.pdf | t
  3 | warrior_workout_3 | https://example.com/upload3.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload3.pdf | t
  4 | warrior_workout_1 | https://example.com/upload1.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload1.pdf | t
  4 | warrior_workout_2 | https://example.com/upload2.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload2.pdf | t
  4 | warrior_workout_3 | https://example.com/upload3.pdf | 2018-02-12 22:25:18.142185+00 | 2018-02-12 22:25:18.142185+00 | upload3.pdf | t
(12 rows)

so you need to:

with i ( name, url, created_at, updated_at,  file_name, is_main ) as
(VALUES
    (  'warrior_workout_1','https://example.com/upload1.pdf', now(), now(), 'upload1.pdf', TRUE ),
    (  'warrior_workout_2','https://example.com/upload2.pdf', now(), now(), 'upload2.pdf', TRUE ),
    (  'warrior_workout_3','https://example.com/upload3.pdf', now(), now(), 'upload3.pdf', TRUE ))
, s as (
SELECT orders.id
FROM orders
LEFT JOIN uploads
ON uploads.order = orders.id
WHERE uploads.order IS NULL
AND orders.product_type = 'warrior_workout'
)
insert into uploads
select id,i.* 
from i
join s on true
order by id,name;

I tried it. Didn't get it going. It looks clever but I just don't understand it all. — DigitalDesignDj, Feb 12 '18 at 23:16

Udayraj Deshmukh · Accepted Answer · 2018-02-12T23:15:20.830

1

You can use INSERT INTO SELECT and WHERE col1 IN to make it work.

INSERT INTO uploads ("order", name, url, created_at, updated_at,  file_name, is_main )    
SELECT filtered_orders.id, 'warrior_workout_1','https://example.com/upload1.pdf', now(), now(), 'upload1.pdf', TRUE    
FROM orders as filtered_orders    
WHERE filtered_orders.id IN (    
   SELECT orders.id    
   FROM orders    
   LEFT JOIN uploads    
   ON uploads.order = orders.id    
   WHERE uploads.order IS NULL  
   AND orders.product_type = 'warrior_workout'  
)

Run the query two more times for other entries you want to insert.

Answer inspired from here: https://stackoverflow.com/a/22528220/6242649

edited Feb 12 '18 at 23:15

answered Feb 12 '18 at 23:06

Udayraj Deshmukh

1,814
1
14
22

Reducing the problem to 3 sql queries is a legit approach. – DigitalDesignDj Feb 12 '18 at 23:08
Order is a really dumb column name, it needs to be quoted `'order'` – DigitalDesignDj Feb 12 '18 at 23:14
Updated the dumb column :) – Udayraj Deshmukh Feb 12 '18 at 23:17
1

@DigitalDesignDj actually it needs to be `"order"` - `'order'` is a string constant – Feb 24 '18 at 09:31

Postgres use SELECT query to INSERT records

2 Answers2