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
– Udayraj Deshmukh, Commented Feb 12, 2018 at 21:30
Then this is what you want - stackoverflow.com/questions/25969/… . I'm writing the relevant excerpt below to answer your question — Udayraj Deshmukh
– Udayraj Deshmukh, Commented Feb 12, 2018 at 22:51

Udayraj Deshmukh · Accepted Answer · 2018-02-12 23:15:20Z

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, 2018 at 23:15

answered Feb 12, 2018 at 23:06

Udayraj Deshmukh

1,9241 gold badge17 silver badges25 bronze badges

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

4 Comments

DigitalDesignDj Over a year ago

Reducing the problem to 3 sql queries is a legit approach.

DigitalDesignDj Over a year ago

Order is a really dumb column name, it needs to be quoted 'order'

Udayraj Deshmukh Over a year ago

Updated the dumb column :)

user330315 Over a year ago

@DigitalDesignDj actually it needs to be "order" - 'order' is a string constant

Vao Tsun · Accepted Answer · 2018-02-12 22:28:16Z

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.

Collectives™ on Stack Overflow

Postgres use SELECT query to INSERT records

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related