Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

AdvancedBasic SELECT

Add constraint NOT VALID (for large tables)

The query

SQL
ALTER TABLE large_orders 
ADD CONSTRAINT check_total_positive CHECK (total > 0) NOT VALID;

-- Later, validate it without taking an exclusive lock
ALTER TABLE large_orders VALIDATE CONSTRAINT check_total_positive;
Tested against PostgreSQL 16

Note

“NOT VALID adds the constraint for new rows immediately without scanning the whole table. VALIDATE scans it later concurrently.