SQL foundations

Think in layers when diagnosing a problem: can the client connect, is it connected to the right database, and does that database contain the expected schema and table? A database name is not a schema name, and a psql meta-command is not SQL.

Choose a type and constraint that express the rule you need: exact decimal, real-world instant, required value, or generated identifier. Defaults handle omissions; they do not validate every possible value.

Use a clear column list, a narrow predicate, and RETURNING when the caller needs the changed values. This keeps the write and its result together, reducing ambiguity and unnecessary round-trips.

Select only what the caller needs, define an ordering with a unique tie-breaker, and use a cursor for deep result sets. The database cannot promise repeatable pages if the query leaves ordering implicit.

Use IS NULL for presence checks, COALESCE for an intentional fallback, and IS [NOT] DISTINCT FROM when equality semantics should include missing values. Ordinary = and <> leave the result unknown if either operand is NULL.

Ask whether a condition applies to individual rows or to groups. Put row conditions in WHERE and group conditions in HAVING; compute output expressions in SELECT, then sort and limit the result.