Project Work Session 3

Goals

By the end of this session you should have:

• A CTE + INSERT pattern added to the script
• All edge cases resolved: missing ON CONFLICT, wrong TRUNCATE order, unrealistic data
• Peer-tested script — a classmate ran your script on a clean database
• All pre-submission checklist items checked off

Session Structure

CTE + INSERT Pattern (Required)

Your script must include at least one CTE + INSERT. This is the pattern where you use a WITH block to prepare or filter data before inserting it.

The order_summary table has already been populated using INSERT INTO ... SELECT. A good candidate for the CTE pattern is inserting a derived record — for example, tagging high-value customers in a separate table, or updating summary data based on a calculation.

If your schema has a vip_customers table, here is the pattern:

-- ============================================================
-- vip_customers  (CTE + INSERT pattern)
-- Tag customers whose total order value exceeds 500
-- ============================================================
WITH high_value_customers AS (
    SELECT
        os.customer_id,
        os.total_value
    FROM order_summary os
    WHERE os.total_value > 500
)
INSERT INTO vip_customers (customer_id, tier, added_date)
SELECT
    customer_id,
    'Gold',
    CURRENT_DATE
FROM high_value_customers
ON CONFLICT (customer_id) DO NOTHING;

If your schema does not have a vip_customers table, add it:

CREATE TABLE IF NOT EXISTS vip_customers (
    vip_id      SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id) UNIQUE,
    tier        VARCHAR(50) NOT NULL DEFAULT 'Gold',
    added_date  DATE NOT NULL DEFAULT CURRENT_DATE
);

And add it to the TRUNCATE block (it goes before order_summary since it depends on customers):

TRUNCATE TABLE vip_customers   CASCADE;
TRUNCATE TABLE order_summary   CASCADE;
-- ... rest of TRUNCATE block

Another valid CTE + INSERT uses the CTE to calculate which products to restock:

-- ============================================================
-- restock_log  (CTE + INSERT pattern — alternative example)
-- ============================================================
CREATE TABLE IF NOT EXISTS restock_log (
    log_id      SERIAL PRIMARY KEY,
    product_id  INTEGER NOT NULL REFERENCES products(product_id),
    logged_date DATE NOT NULL DEFAULT CURRENT_DATE,
    reason      TEXT
);

WITH low_stock AS (
    SELECT product_id, name, stock
    FROM products
    WHERE stock < 100
)
INSERT INTO restock_log (product_id, logged_date, reason)
SELECT
    product_id,
    CURRENT_DATE,
    'Stock below threshold: ' || stock::TEXT
FROM low_stock
ON CONFLICT DO NOTHING;

Use whichever pattern fits your schema. The requirement is: one WITH block that feeds an INSERT.

Peer Testing Protocol

Peer testing is required. You will run your classmate's script and they will run yours.

What to do

1. Drop all project tables in your database (or use a separate clean schema/database):

-- Drop in reverse FK order
DROP TABLE IF EXISTS vip_customers  CASCADE;
DROP TABLE IF EXISTS order_summary  CASCADE;
DROP TABLE IF EXISTS order_items    CASCADE;
DROP TABLE IF EXISTS orders         CASCADE;
DROP TABLE IF EXISTS products       CASCADE;
DROP TABLE IF EXISTS customers      CASCADE;
DROP TABLE IF EXISTS categories     CASCADE;

2. Run your partner's script from scratch. Record any errors — copy the exact error message.

3. Run the script again without resetting the database. Record the row counts:

SELECT 'categories'    AS tbl, COUNT(*) FROM categories
UNION ALL SELECT 'customers',    COUNT(*) FROM customers
UNION ALL SELECT 'products',     COUNT(*) FROM products
UNION ALL SELECT 'orders',       COUNT(*) FROM orders
UNION ALL SELECT 'order_items',  COUNT(*) FROM order_items
UNION ALL SELECT 'order_summary', COUNT(*) FROM order_summary;

4. Check for hardcoded IDs. Search the script for integer literals used in VALUES clauses where a FK column is expected. Any category_id = 3 or similar is a violation.

5. Check for realistic data. Are the names, prices, and dates plausible? A product named asdf with a price of 0.01 is not acceptable.

6. Report your findings to your partner in a comment block at the top of their script:

-- PEER REVIEW by: Your Name, Date: 2026-04-12
-- First run: OK / ERRORS (paste errors here)
-- Second run: row counts unchanged / ROW COUNT CHANGED (which tables?)
-- Hardcoded IDs found: YES (list them) / NO
-- Data quality: OK / ISSUES (describe)

Pre-Submission Checklist

Go through every item before the wrap-up session:

• Script runs from scratch without errors
• Script runs a second time with no row count changes and no errors
• No hardcoded FK IDs anywhere in the script
• At least one INSERT INTO ... SELECT statement
• At least one WITH ... INSERT (CTE + INSERT) statement
• At least 5 rows in every table
• Data is domain-appropriate and realistic
• Every INSERT on a UNIQUE table has ON CONFLICT DO NOTHING
• TRUNCATE block covers all tables in the correct order
• Script is commented — each section has a header comment
• File is named project_yourname.sql
• File ends with a verification SELECT block

Troubleshooting

CTE produces no rows, INSERT inserts nothing

Run the CTE SELECT alone to verify it returns data:

SELECT
    os.customer_id,
    os.total_value
FROM order_summary os
WHERE os.total_value > 500;

If this returns nothing, your order_summary rows may not have been inserted yet, or the threshold is too high for your test data. Adjust the data or the threshold.

Peer's script fails on your machine but works on theirs

The most common cause is database-specific state: sequences that are at different values, or rows left over from a previous partial run. The fix is always: add or check the TRUNCATE block at the top of the script.

ON CONFLICT conflict target is required for DO UPDATE

If you use ON CONFLICT DO UPDATE, you must specify the conflict column: ON CONFLICT (email) DO NOTHING. For DO NOTHING without a target, PostgreSQL accepts it only if there is a single UNIQUE constraint. When in doubt, specify the column explicitly.