DDL, Data Types and Constraints

Key points

1. 1

   A primary key is UNIQUE plus NOT NULL; UNIQUE allows many NULLs unless declared NULLS NOT DISTINCT (PostgreSQL 15+), and CHECK passes when its condition is NULL.

2. 2

   Use numeric for exact decimals and timestamptz for real-world instants; float is approximate and plain timestamp silently drops offsets.

3. 3

   Prefer GENERATED ALWAYS AS IDENTITY over serial. Neither is gap-free or unique by itself: sequences never roll back, and explicit ids don't advance them.

4. 4

   Foreign keys default to NO ACTION, are not auto-indexed on the referencing column in PostgreSQL, and MATCH SIMPLE skips the check when any key column is NULL.

5. 5

   DELETE fires row triggers and keeps sequences; TRUNCATE is fast, transactional in PostgreSQL, skips row triggers and resets identity only with RESTART IDENTITY; DROP ... CASCADE drops dependent constraints and views, not child tables.

6. 6

   ALTER TABLE ADD COLUMN with a non-volatile default is metadata-only, while a volatile default or a type change with USING rewrites the table under an exclusive lock.

7. 7

   For big tables, add constraints with NOT VALID and VALIDATE them later, and add NOT NULL via a validated CHECK to avoid long blocking scans.

Read the source

• PostgreSQL docs: Constraints
• PostgreSQL docs: ALTER TABLE
• PostgreSQL docs: Data Types