> ## Documentation Index
> Fetch the complete documentation index at: https://www.pgschema.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 5 lesser known Postgres syntax

> Building a declarative schema migration tool for Postgres means you eventually meet syntax most schema files never write. Five intriguing clauses from 200+ closed user reports.

## 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)

```sql theme={null}
PRIMARY KEY (id, valid_period WITHOUT OVERLAPS)
```

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

```sql theme={null}
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:

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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?