5 lesser known Postgres syntax - pgschema, Declarative Schema Migration for Postgres

1. UNIQUE NULLS NOT DISTINCT (PG15)

Postgres treats NULL as distinct from NULL. UNIQUE (email) will store two rows with email IS NULL. UNIQUE NULLS NOT DISTINCT (email) makes those two rows collide — the uniqueness most people think they already have.

2. WITHOUT OVERLAPS / PERIOD (PG18)

PRIMARY KEY (id, valid_period WITHOUT OVERLAPS)

One id, several versions, those versions cannot overlap in time.

FOREIGN KEY (contact_id, PERIOD valid_period)
    REFERENCES contacts (id, PERIOD valid_period)

The child period has to sit inside a parent version.

3. BEGIN ATOMIC (PG14)

Most functions are a dollar-quoted string. The SQL-standard form is not:

CREATE FUNCTION transfer_funds(...)
RETURNS void
LANGUAGE sql
BEGIN ATOMIC
    UPDATE accounts SET balance = balance - amount WHERE user_id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE user_id = receiver_id;
END;

No $$ quotes. The parser sees real statements.

4. CHECK (false) NO INHERIT

My personal favorite, since it feels like applying design patterns to SQL. Classic pattern on an INHERITS parent: reject inserts on the parent, let children accept them.

CONSTRAINT no_direct_insert CHECK (false) NO INHERIT

Without NO INHERIT, every child inherits the always-false check and nothing can insert.

5. CREATE CONSTRAINT TRIGGER

A trigger that behaves like a constraint: it can be DEFERRABLE INITIALLY DEFERRED and fire at commit. Regular CREATE TRIGGER cannot.

CREATE CONSTRAINT TRIGGER prevent_code_update
    AFTER UPDATE ON products
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE FUNCTION prevent_code_update();

Fire after the rest of the transaction — or not at all if the row is changed back. Postgres has a lot of syntax. What’s your secret tip?

​1. UNIQUE NULLS NOT DISTINCT (PG15)

​2. WITHOUT OVERLAPS / PERIOD (PG18)

​3. BEGIN ATOMIC (PG14)

​4. CHECK (false) NO INHERIT

​5. CREATE CONSTRAINT TRIGGER

1. UNIQUE NULLS NOT DISTINCT (PG15)

2. WITHOUT OVERLAPS / PERIOD (PG18)

3. BEGIN ATOMIC (PG14)

4. CHECK (false) NO INHERIT

5. CREATE CONSTRAINT TRIGGER