How to combine 2 Postgres queries

Question

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


INSERT INTO uploads ( id, order, name, url, file_name, is_main ) VALUES
  (DEFAULT, orders.customer, "warrior_workout_nonhomeworkout","https://example.org","6 Week Warrior Workout Non-Home Workout.pdf",true),
  (DEFAULT, orders.customer, "warrior_workout_homeworkout","https://example.org","6 Week Warrior Workout Home Workout.pdf",true),
  (DEFAULT, orders.customer, "men_six_week_summer_shredder","https://example.org","Men's Six Week Summer Shredder.pdf",true);

I have 2 postgres queries, and I need to combine them into one.

Can you explain a bit more? You're showing us two queries, but one is an straightforward insert, the other is a select -- you probably don't want to combine those two. Can you show us what queries you have, what you've tried, and what you want the output to look like? — jmelesky
– jmelesky, Commented Feb 12, 2018 at 20:08
This is it. I want to do 3 INSERT per record returned by the first query. — DigitalDesignDj
– DigitalDesignDj, Commented Feb 12, 2018 at 20:10
I actually made a mistake in my example. One of the rows to populate is named "order". I made an edit, but it just made it works. — DigitalDesignDj
– DigitalDesignDj, Commented Feb 12, 2018 at 23:06

Gordon Linoff · Accepted Answer · 2018-02-12 20:13:32Z

2

I think you want this:

INSERT INTO uploads (customer, name, url, file_name, is_main ) 
    SELECT o.customer, v.*
    FROM orders o LEFT JOIN
         uploads u
         ON u.order = o.id CROSS JOIN
         (VALUES ('warrior_workout_nonhomeworkout', 'https://example.org', '6 Week Warrior Workout Non-Home Workout.pdf', true),
                 ('warrior_workout_homeworkout', 'https://example.org', '6 Week Warrior Workout Home Workout.pdf', true),
                 ('men_six_week_summer_shredder', 'https://example.org', 'Men''s Six Week Summer Shredder.pdf', true)
         ) v(name, url, file_name, is_main)
    WHERE u.order IS NULL AND o.product_type = 'warrior_workout';

For each of the customers missing the upload, this inserts those three values.

answered Feb 12, 2018 at 20:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

jmelesky Over a year ago

Gordon is using the VALUES statement to create a fake table called v. v.* just means "all the columns in v".

Collectives™ on Stack Overflow

How to combine 2 Postgres queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related