Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

PostgreSQL INSERT, UPDATE, and DELETE Commands

Introduction
INSERT with SELECT
7. Complex SELECT with INSERT
Referring to other tables
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

Great! A SELECT query used with an INSERT can be as complex as you need it to be. For example, we could add a WHERE clause to filter records. Check out the following query:

INSERT INTO product_history (id, name, price)
SELECT id, name, price
FROM product
WHERE delivered_timestamp >= '2017-01-01' and delivered_timestamp < '2018-01-01'

This chooses the ID, name, and price of products that were delivered in 2017. All these rows are inserted into the report table product_history.

The SELECT query in an INSERT can be complicated. For example, it can contain an aggregate function and a GROUP BY to compute statistics that will be used in later reports.

Exercise

Look! There are new orders in the purchase table. The manager would like to add all purchases from October 2017 into the purchase_history table.

Stuck? Here's a hint!

You need this expression in the WHERE clause:

order_date >= '2017-10-01' AND order_date < '2017-11-01'