EDB PostgreSQL Associate: SQL Fundamentals and Data Types

Evaluation order

You write SELECT first, but PostgreSQL logically evaluates FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET. That is why a SELECT alias works in ORDER BY but not in WHERE.

NULLs

NULL means unknown. NULL = NULL yields NULL, so use IS NULL or IS DISTINCT FROM. Aggregates other than count(*) skip NULLs, and sum over zero rows returns NULL, not 0 - wrap it in coalesce. A single NULL inside a NOT IN list makes the predicate unknown and the query returns no rows.

Types that matter

• Exact numerics: smallint, integer, bigint, numeric(p,s). Approximate: real, double precision - never test them with =.
• text, varchar(n), char(n): identical storage; varchar(n) only adds a length check, while char(n) blank-pads and is rarely what you want.
• timestamp versus timestamptz: timestamptz stores UTC and converts on display; plain timestamp stores exactly what you give it.
• json keeps text verbatim; jsonb is binary, GIN-indexable, and discards key order and duplicate keys.

Traps

Integer division: 5/2 returns 2, so cast with 5/2.0 or 5::numeric/2. '1'::boolean is valid but 'yes'::boolean is not. String concatenation with || returns NULL if either operand is NULL.

Rule of thumb

Use numeric for money, timestamptz for instants, date for calendar days, and text unless a length constraint is genuinely required.