Modifying Table Structure

1. Adding Column

ALTER TABLE customers ADD COLUMN phone text;
ALTER TABLE customers ADD COLUMN status text NOT NULL DEFAULT 'active';

ALTER TABLE customers
  ADD COLUMN phone text,
  ADD COLUMN dob   date,
  ADD COLUMN notes text;

Note: Combine ALTER actions in one statement to avoid multiple table locks/rewrites.

ALTER TABLE customers DROP COLUMN obsolete_field;
ALTER TABLE customers DROP COLUMN IF EXISTS obsolete_field CASCADE;

Mode	Effect
RESTRICT	Default; error if dependents exist
CASCADE	Drop views/constraints referencing it

ALTER TABLE customers RENAME COLUMN email_address TO email;

Impact	Detail
Locks	Brief ACCESS EXCLUSIVE
Views/Funcs	Rename automatically updates dependents only if defined by column ref

ALTER TABLE orders ALTER COLUMN total_cents TYPE bigint;
ALTER TABLE customers ALTER COLUMN email TYPE citext USING email::citext;

Aspect	Detail
USING	Required when cast not implicit
Rewrite	Triggered unless types are binary-compatible
Lock	ACCESS EXCLUSIVE for duration

ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new';

ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;

Behavior	Notes
Existing rows	Unaffected
Future inserts	Must supply value or column allows NULL

-- Backfill first to avoid violation
UPDATE customers SET phone = '' WHERE phone IS NULL;
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;

Warning: Adding NOT NULL scans the whole table; on large tables, validate via CHECK constraint with NOT VALID first.

ALTER TABLE customers ALTER COLUMN phone DROP NOT NULL;

ALTER TABLE events ALTER COLUMN user_id SET STATISTICS 1000;
ANALYZE events;

Value	Meaning
-1	Use default_statistics_target
100	Default
1000-10000	For high-cardinality / skewed columns

ALTER TABLE docs ALTER COLUMN body SET STORAGE EXTERNAL;
ALTER TABLE docs ALTER COLUMN body SET COMPRESSION lz4;

Mode	Effect
PLAIN	No TOAST/compression (fixed-width only)
EXTENDED	Compress + TOAST (default)
EXTERNAL	TOAST without compression
MAIN	Compress inline first
COMPRESSION lz4/pglz	Algorithm PG 14+