10 — Data Types & NULL Handling

-- numeric: exact arithmetic. 0.1 + 0.2 = 0.3 (stored as decimal digits, not binary float).
SELECT 0.1::numeric + 0.2::numeric;          -- 0.3

-- float: inexact. 0.1 in binary is a repeating fraction → rounded → accumulation error.
SELECT 0.1::float + 0.2::float;              -- 0.30000000000000004

-- The classic float trap: money calculations drift over many additions.
-- Over 1 million additions of $0.10, float drifts by cents; numeric stays exact.
SELECT sum_amount::float AS float_sum,       -- drifts: e.g., 100000.00000001
       sum_amount::numeric AS numeric_sum    -- exact:   100000.00
FROM (SELECT SUM(0.10::numeric) AS sum_amount FROM generate_series(1, 1000000)) t;

-- In PostgreSQL, text and varchar (no limit) are IDENTICAL in performance and storage.
-- Both use variable-length storage. varchar(n) is just a CHECK (length(col) <= n) constraint.
-- Use text by default; use varchar(n) only for real business-rule length limits.

-- char(n) pads with spaces to width n. This causes subtle comparison bugs:
-- 'NY' stored as char(3) becomes 'NY ' (padded).
-- Depending on collation, 'NY' = 'NY ' may be TRUE (trailing spaces ignored) or FALSE.
-- Avoid char(n) except for truly fixed-width codes (ISO country codes = char(2)).
SELECT 'NY'::char(3) = 'NY';                  -- TRUE in PG (trailing spaces trimmed in comparison)
SELECT 'NY'::varchar = 'NY ';                 -- FALSE (varchar does not trim)

-- NULL comparisons — ALL of these evaluate to UNKNOWN, never TRUE or FALSE.
NULL  = NULL    -- UNKNOWN (not TRUE! two NULLs are not "equal")
NULL <> NULL    -- UNKNOWN (not TRUE! can't confirm they're different)
NULL  = 5       -- UNKNOWN
NULL <> 5       -- UNKNOWN
NULL  > 5       -- UNKNOWN
NULL  < 5       -- UNKNOWN

-- WHERE keeps only TRUE → WHERE col = NULL returns ZERO rows (UNKNOWN is not TRUE).
SELECT * FROM customers WHERE city = NULL;    -- ❌ returns nothing, always
SELECT * FROM customers WHERE city IS NULL;   -- ✅ correct NULL test

-- IS NOT DISTINCT FROM: NULL-safe equality (NULL = NULL → TRUE)
NULL IS NOT DISTINCT FROM NULL   -- TRUE   (unlike NULL = NULL which is UNKNOWN)
NULL IS DISTINCT FROM NULL       -- FALSE
5   IS NOT DISTINCT FROM 5       -- TRUE
5   IS DISTINCT FROM 6           -- TRUE
NULL IS DISTINCT FROM 5          -- TRUE   (NULL is distinct from any non-NULL)

-- Practical use: join on nullable columns where NULL should match NULL.
-- Caveat: can't use a plain B-tree index → slower than = on indexed columns.
SELECT * FROM a JOIN b ON a.key IS NOT DISTINCT FROM b.key;

-- Provide a fallback for missing values.
SELECT name, COALESCE(city, 'Unknown') AS city FROM customers;

-- Multiple fallbacks: try preferred_name, then full_name, then email, then 'Anonymous'.
SELECT COALESCE(preferred_name, full_name, email, 'Anonymous') AS display_name
FROM users;

-- Default a SUM to 0 (SUM of no rows is NULL, not 0).
-- Without COALESCE, a customer with no orders shows NULL instead of 0.
SELECT COALESCE(SUM(amount), 0) AS total
FROM orders
WHERE customer_id = 999;                     -- no rows match → SUM = NULL → COALESCE → 0

-- ❌ Division by zero error if any total is 0.
SELECT sales / total FROM metrics;

-- ✅ NULLIF turns 0 into NULL; division by NULL yields NULL (not an error).
SELECT sales / NULLIF(total, 0) AS ratio FROM metrics;

-- Then COALESCE the NULL result if you want 0 or some sentinel.
SELECT COALESCE(sales / NULLIF(total, 0), 0) AS ratio FROM metrics;

-- NULLIF is also useful for normalizing sentinel values to NULL.
SELECT NULLIF(status, '') AS status FROM logs;        -- empty string → NULL
SELECT NULLIF(status, 'unknown') AS status FROM logs; -- 'unknown' → NULL

-- AVG ignores NULLs: AVG of [10, 20, NULL] = 15 (avg of 10 and 20), NOT 10 (avg of 10, 20, 0).
SELECT AVG(amount) FROM orders;

-- COUNT(*) counts ALL rows (including NULL rows). COUNT(col) counts non-NULL values only.
-- If amount has NULLs, COUNT(amount) < COUNT(*).
SELECT COUNT(*) AS total_rows, COUNT(amount) AS non_null_amounts FROM orders;

-- SUM of all-NULL (or empty set) is NULL, not 0. Use COALESCE for 0.
SELECT SUM(amount) FROM orders WHERE FALSE;              -- NULL
SELECT COALESCE(SUM(amount), 0) FROM orders WHERE FALSE; -- 0

-- If NULL means "zero" (not "missing"), average them as 0:
SELECT AVG(COALESCE(amount, 0)) FROM orders;  -- AVG of [10, 20, 0] = 10, not 15
-- But ask: is NULL really 0, or is it "unknown"? Semantically different.

-- String concatenation with || : any NULL operand → entire result is NULL.
SELECT 'Hello ' || NULL;           -- NULL (the whole string becomes NULL)
SELECT 'Hello ' || name || '!' FROM customers;  -- NULL if name is NULL

-- Fix 1: COALESCE each operand to empty string.
SELECT 'Hello ' || COALESCE(name, '') || '!' FROM customers;

-- Fix 2: CONCAT() ignores NULLs (PostgreSQL, MySQL).
SELECT CONCAT('Hello ', name, '!') FROM customers;  -- NULLs skipped, not propagated

-- Fix 3: CONCAT_WS (with separator) — skips NULLs but keeps separators between non-NULLs.
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) FROM customers;
-- 'John Q Public' or 'John Public' (middle_name NULL → separator not doubled)

-- In WHERE, NULL is treated as "not true" (excluded).
WHERE col           -- keeps rows where col IS TRUE (NULL and FALSE excluded)
WHERE NOT col       -- keeps rows where col IS FALSE (NULL and TRUE excluded) — NULL is NOT excluded!
WHERE col = TRUE    -- keeps only TRUE (NULL → UNKNOWN → excluded)
WHERE col IS TRUE   -- keeps only TRUE (clearer, explicit)
WHERE col IS FALSE  -- keeps only FALSE
WHERE col IS UNKNOWN -- keeps only NULL (the only test that finds NULL booleans)

-- The subtle trap: WHERE NOT col EXCLUDES NULLs.
-- NOT NULL = UNKNOWN (not TRUE). So 'WHERE NOT active' drops rows where active IS NULL.
-- English "not active" feels like it should include NULL — SQL doesn't agree.

-- Partial unique index: "at most one non-NULL value per group" (portable across engines).
-- UNIQUE constraint allows multiple NULLs; this index enforces uniqueness only on non-NULL rows.
CREATE UNIQUE INDEX idx_users_email_unique
ON users (email)
WHERE email IS NOT NULL;

-- Production e-commerce schema with type choices annotated with reasoning.
-- Every type is chosen deliberately; every NULL is chosen deliberately.

-- Create the enum type (must be done before the table if used in a column).
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'returned', 'cancelled');

CREATE TABLE customers (
  -- bigint for PK: 8 bytes, supports > 2B rows. int (4B) would overflow at 2.1B.
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  -- text for names: no business-rule length limit, variable-length storage.
  -- varchar(255) would be an arbitrary limit with no perf benefit in PostgreSQL.
  name        text        NOT NULL,

  -- citext (case-insensitive text) for email: queries and unique constraint
  -- are case-insensitive without LOWER() in every query. Requires citext extension.
  email       citext      NOT NULL UNIQUE,

  -- numeric(10,2) for account_balance: exact decimal, 10 total digits, 2 after decimal.
  -- Max value: $99,999,999.99. Never use float for money (0.1+0.2 ≠ 0.3 in float).
  account_balance numeric(10,2) NOT NULL DEFAULT 0,

  -- boolean for flags: 1 byte, clear semantics. NOT NULL with default.
  is_active   boolean     NOT NULL DEFAULT true,

  -- timestamptz for timestamps: stored as UTC, displayed in session timezone.
  -- Never use plain 'timestamp' (without tz) for app timestamps — timezone bugs.
  created_at  timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,

  -- jsonb for flexible metadata: binary, indexable, deduplicated keys.
  -- Use for semi-structured data that doesn't warrant its own table.
  metadata    jsonb
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  -- bigint FK: must match the PK type exactly (bigint → bigint).
  customer_id bigint      NOT NULL REFERENCES customers(id),

  -- numeric(12,2) for amount: 12 total digits, 2 after decimal.
  -- Max: $9,999,999,999.99. Exact decimal for financial accuracy.
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0),

  -- enum for status: small fixed set of labels, 4 bytes, type-safe.
  -- Can't add values without ALTER TYPE (and can't remove values at all).
  status      order_status NOT NULL DEFAULT 'pending',

  -- uuid for external-facing IDs: 16 bytes, globally unique, no sequential leak.
  -- Generated by the application or gen_random_uuid() (PG 13+).
  external_ref uuid       UNIQUE,

  -- timestamp with tz for the order date.
  ordered_on  timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,

  -- text[] array for tags: small, fixed-size list of labels.
  -- For large or frequently-queried collections, use a separate tags table + join table.
  tags        text[]      NOT NULL DEFAULT '{}'
);

-- Index on (customer_id, ordered_on DESC) for "recent orders per customer" queries.
CREATE INDEX idx_orders_customer_date ON orders (customer_id, ordered_on DESC);

-- float for money: 0.1 + 0.2 ≠ 0.3 in IEEE 754 binary floating-point.
-- Over millions of transactions, cents drift. Financial reports don't tie out.
CREATE TABLE bad_orders (
  id     int PRIMARY KEY,
  amount double precision NOT NULL  -- ❌ float: 0.1 + 0.2 = 0.30000000000000004
);

-- The drift is invisible at first, then catastrophic:
SELECT 0.10::float + 0.20::float;           -- 0.30000000000000004
SELECT (0.10::float + 0.20::float) = 0.30::float;  -- FALSE (they're not equal!)

-- numeric for money: exact decimal arithmetic. 0.1 + 0.2 = 0.3, always.
-- numeric(p, s): p = total digits, s = digits after decimal point.
CREATE TABLE good_orders (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)  -- ✅ exact, max $9,999,999,999.99
);

SELECT 0.10::numeric + 0.20::numeric;        -- 0.30 (exact)
SELECT (0.10::numeric + 0.20::numeric) = 0.30::numeric;  -- TRUE

-- char(50) for names: pads with spaces to 50 chars. Wastes storage, causes comparison bugs.
-- 'Alice' stored as 'Alice' + 45 spaces. 'Alice' = 'Alice     ' may be TRUE or FALSE
-- depending on collation and whether trailing spaces are trimmed.
CREATE TABLE bad_customers (
  id   int PRIMARY KEY,
  name char(50) NOT NULL  -- ❌ padded, comparison surprises
);

SELECT 'Alice'::char(50) = 'Alice';          -- TRUE in PG (trailing spaces trimmed in char comparison)
SELECT 'Alice'::char(50) = 'Alice   ';       -- TRUE in PG (both sides trimmed)
-- But in other engines or with certain collations, this may be FALSE.

-- text for variable-length strings: no padding, no comparison surprises, same performance.
-- Use varchar(n) only if there's a real business-rule length limit.
CREATE TABLE good_customers (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL  -- ✅ variable-length, no padding, exact comparison
);

SELECT 'Alice'::text = 'Alice';              -- TRUE
SELECT 'Alice'::text = 'Alice   ';           -- FALSE (trailing spaces are significant in text)

-- Explicit casting with :: (PostgreSQL-specific, concise).
SELECT '2024-01-15'::date;
SELECT 42::text;
SELECT '123.45'::numeric(10,2);

-- Standard SQL CAST (portable to all engines).
SELECT CAST('2024-01-15' AS date);
SELECT CAST(42 AS text);

-- Implicit coercion: PostgreSQL coerces in some contexts, but not all.
SELECT 1 + 1.0;           -- int + numeric → numeric (implicit upcast)
SELECT '5'::text + 3;     -- ERROR: text + int is not valid (no implicit text→int)

-- generate_series for testing: produce a range of values for testing queries.
SELECT * FROM generate_series(1, 10);                    -- 1..10
SELECT * FROM generate_series('2024-01-01'::date, '2024-01-07'::date, '1 day');  -- 7 dates

SELECT
  title || ' ' || first_name || ' ' || last_name AS display_name
FROM customers;