DDL and load

What you will be able to do

• Create a staging table with constraints
• Truncate and \\copy reload safely
• Add a simple check constraint

Why this matters for data work

Queries need tables with contracts: types, keys, checks. Labs reload often — TRUNCATE + \copy is the local pattern. Production uses migrations and staging swaps (later).

Concepts

• CREATE TABLE, PRIMARY KEY, REFERENCES, CHECK.
• TRUNCATE … CASCADE — careful with FKs.
• \copy from client paths.
• Idempotent lab: drop/create or truncate then load.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
cd ~/dcubes/sql-lab
psql << 'SQL'
CREATE TABLE IF NOT EXISTS orders_staging (
  LIKE orders INCLUDING ALL
);
TRUNCATE orders_staging;
SQL
psql -c "\copy orders_staging FROM 'data/orders.csv' CSV HEADER"
psql << 'SQL'
SELECT count(*) FROM orders_staging;
SELECT count(*) FROM orders_staging WHERE quantity <= 0;
-- cleanup
DROP TABLE orders_staging;
SQL

What you should see

• 300 rows in staging.
• 0 rows with non-positive quantity on this seed.
• Table dropped after the check.

Common mistakes

• Loading orders before dims with FKs enabled.
• Forgetting CASCADE when truncate order is wrong.
• Server COPY of a laptop path.

Next

Views and EXPLAIN