INSERT Practice (Session 2)

Goals

Write a complete multi-table re-runnable INSERT script from scratch
Handle FK dependencies correctly using TRUNCATE or ON CONFLICT
Verify idempotency by running the full script twice
Apply CTE + INSERT pattern for complex data preparation

Warm-up

Task (5 min): What is the difference between ON CONFLICT DO NOTHING and TRUNCATE + INSERT? When would you use each?

Hint

`ON CONFLICT DO NOTHING` preserves existing rows and skips duplicates — useful when data accumulates over time. `TRUNCATE + INSERT` resets everything on each run — useful for reference data or test data that should always match the script exactly.

Steps

Step 1 — Design your insert order

Given these tables from a hypothetical schema, write the correct TRUNCATE order (children first):

payment depends on rental, customer
rental depends on customer, staff, inventory
inventory depends on film, store
customer depends on address
address depends on city
city depends on country

Write the TRUNCATE block:

SQL

TRUNCATE TABLE ??? CASCADE;
TRUNCATE TABLE ??? CASCADE;
-- ... continue in correct order

Hint

Start with the most dependent tables: `payment`, then `rental`, then `inventory`, `customer`, `address`, `city`, `country`, `store`, `staff`, `film`.

Or use one TRUNCATE TABLE payment, rental, inventory, customer ... CASCADE; statement.

Step 2 — Write a complete re-runnable population script

Create a small schema and write the complete re-runnable script:

SQL

-- Schema setup
CREATE TABLE IF NOT EXISTS countries (
    country_id   SERIAL PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS cities (
    city_id    SERIAL PRIMARY KEY,
    city_name  VARCHAR(100) NOT NULL,
    country_id INT NOT NULL REFERENCES countries(country_id)
);

-- Reset data
TRUNCATE TABLE cities CASCADE;
TRUNCATE TABLE countries CASCADE;

-- Insert reference data
INSERT INTO countries (country_name) VALUES
    ('Kazakhstan'),
    ('Germany'),
    ('Japan')
ON CONFLICT DO NOTHING;

-- Insert city using subquery for FK
INSERT INTO cities (city_name, country_id) VALUES
    ('Almaty',  (SELECT country_id FROM countries WHERE country_name = 'Kazakhstan')),
    ('Berlin',  (SELECT country_id FROM countries WHERE country_name = 'Germany')),
    ('Tokyo',   (SELECT country_id FROM countries WHERE country_name = 'Japan'))
ON CONFLICT DO NOTHING;

-- Verify
SELECT ci.city_name, co.country_name
FROM cities ci
JOIN countries co ON ci.country_id = co.country_id;

Run this script twice. The second run must produce the same result with no errors.

Step 3 — CTE + INSERT pattern

Extend the script with a CTE-based insert:

SQL

-- Add a population summary table
CREATE TABLE IF NOT EXISTS city_stats (
    city_id      INT PRIMARY KEY REFERENCES cities(city_id),
    city_name    VARCHAR(100),
    country_name VARCHAR(100)
);

TRUNCATE TABLE city_stats CASCADE;

-- Use CTE to prepare data, then insert
WITH city_data AS (
    SELECT ci.city_id, ci.city_name, co.country_name
    FROM cities ci
    JOIN countries co ON ci.country_id = co.country_id
)
INSERT INTO city_stats (city_id, city_name, country_name)
SELECT city_id, city_name, country_name
FROM city_data
ON CONFLICT DO NOTHING;

SELECT * FROM city_stats;

Step 4 — Full re-runability verification

Run your complete script (Steps 2 + 3) from the beginning twice. Record row counts before and after the second run — they must be identical.

SQL

-- Capture counts
SELECT 'countries' AS tbl, COUNT(*) FROM countries
UNION ALL
SELECT 'cities', COUNT(*) FROM cities
UNION ALL
SELECT 'city_stats', COUNT(*) FROM city_stats;

Bonus Tasks

RETURNING + CTE: Rewrite the cities insert to capture the inserted city_id values using RETURNING, then use them to insert into city_stats within the same statement.
Conditional population: Add a CHECK constraint to cities requiring city_name to have at least 2 characters. Add a row that would violate this constraint and handle it with ON CONFLICT.
Assignment prep: Write the TRUNCATE block and FK-resolved INSERT statements for at least 3 of your own Assignment 2 tables.