Types and casts

What you will be able to do

• Read column types with \d and information_schema
• Filter timestamps with date bounds
• Cast between text and numeric when needed

Why this matters for data work

line_total is numeric, order_ts is timestamptz, IDs are text. Mixing them without casts causes errors or wrong comparisons — classic pipeline bugs.

Concepts

• Prefer comparing same types.
• col::date, col::numeric — Postgres cast syntax.
• date_trunc('day', order_ts) — bucket timestamps.
• CURRENT_DATE / now() — be explicit about time zone in real warehouses.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

SELECT order_id, order_ts::date AS order_day, line_total
FROM orders
WHERE order_ts >= DATE '2026-06-01'
  AND order_ts < DATE '2026-07-01'
ORDER BY order_ts
LIMIT 5;

SELECT round(avg(line_total), 2) AS avg_line
FROM orders
WHERE status = 'completed';
SQL

What you should see

• Types matching the DDL from Getting Started.
• A handful of June 2026 rows (seed spans ~60 days before 2026-08-01).
• An average line total as numeric.

Common mistakes

• String-comparing numbers — '9' > '10' is true as text.
• Timestamp vs date — order_ts = DATE '2026-06-01' only matches midnight.
• Silent float — prefer numeric for money-like labs.

Next

Aggregates