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

Recursive Queries in PostgreSQL

Introduction
Using CTEs with data-modifying statements
7. CTEs with INSERT statement 1
Summary

Instruction

OK. Now let's begin to tackle using CTEs with INSERT. Look at the code below.

WITH plus30 AS (
  SELECT
    id,
    first_name,
    last_name,
    city
  FROM customer
  WHERE age >= 30 
)

INSERT INTO customer_30plus 
SELECT
  *
FROM plus30;

In the WITH query, we select the id, first_name, last_name, and city of customers who are over 30 years old.

In the outer query, the data returned by the CTE plus30 is inserted into the table customer_30plus. Note that the outer INSERT statement can refer to the CTE plus30.

Exercise

We created another table called groceries_customers. Fill it with the id, name, and city of all grocery storeslocated in a city where customers live. If no customers live in a city, do not list that grocery store.

Stuck? Here's a hint!

In the CTE query, select data from the grocery_store table. In the outer query, select all data from the CTE and insert it into our new groceries_customers table.