sluice

Your UNIQUE constraint survived the migration; its semantics didn't

NULLS NOT DISTINCT, DEFERRABLE, and WITHOUT OVERLAPS all land on the target as a plain UNIQUE — including Postgres→Postgres. The constraint has the right name and the right columns, and quietly accepts rows the source would have rejected.

Landed in sluice · 2026-07-24 · Postgres

Observed — Postgres source, every sluice target including same-engine Postgres. A read-time WARN shipped in sluice v0.100.0; the weakening itself is real, and faithful carry remains an open follow-up.

What happened #

A UNIQUE constraint is not one thing. Postgres lets it carry three attributes that change what it actually rejects: NULLS NOT DISTINCT (PG 15+), DEFERRABLE, and WITHOUT OVERLAPS (PG 18+). Every migration target was landing all three as a plain UNIQUE — and the surprising part is that this includes Postgres → Postgres, the direction people reasonably assume is lossless.

Nothing errors. The constraint exists on the target, with the same name and the same columns. It simply admits data the source would have refused.

What the target now accepts #

All three share the shape that makes constraint differences dangerous: the target is strictly more permissive than the source, so nothing breaks at migration time. It breaks later, when data the source's schema made impossible finally arrives.

Two catalog surprises worth the detour #

NULLS NOT DISTINCT is not on pg_constraint. Its two siblings are — condeferrable and conperiod — so the natural assumption is a connullsnotdistinct column beside them. There isn't one. The flag lives on the index, as pg_index.indnullsnotdistinct, because that is where the null-handling behavior is implemented. Assuming the symmetry produces a 42703 column does not exist against a real PG 16 — which is the good outcome; the bad one is a version gate that silently never fires.

-- the two that ARE on pg_constraint
SELECT conname, condeferrable, conperiod FROM pg_constraint WHERE contype = 'u';

-- the one that is NOT — it lives on the index
SELECT c.conname, i.indnullsnotdistinct
FROM   pg_constraint c
JOIN   pg_index i ON i.indexrelid = c.conindid
WHERE  c.contype = 'u';

WITHOUT OVERLAPS is PG 18, not PG 17. Temporal constraints landed in the PG 17 development tree and were reverted before GA. Plenty of secondary material still describes them as a 17 feature. Version-gate conperiod at 17 and you read a column that isn't there on every real PG 17 server.

What sluice does about it #

The Postgres schema reader now reads all three attributes — version-gated so pre-15 and pre-18 servers never hit a 42703 — carries them as metadata on its internal index representation, and emits one WARN per affected constraint at schema-read time, naming the attribute, the weaker landing, and a recreate-on-target hint. It is a WARN, not a refusal: the migration completes and the operator decides.

One residual is worth stating plainly, because a note that only advertises the fix is not much use: the WARN is gated on the attribute being constraint-backed. A plain CREATE UNIQUE INDEX … NULLS NOT DISTINCT — an index, not a constraint — is still weakened silently. If you use that form, check it by hand.

The transferable lesson #

When you copy a schema, ask not “does the constraint exist on the target” but “does it reject the same rows.” Name and column list are the easy part, and the part every tool gets right; the modifiers that change the predicate live in catalog columns you have to go looking for — sometimes on a different catalog than the constraint itself. And the direction you trust most deserves the check most: same-engine migrations are where nobody thinks to look, precisely because both sides speak the same dialect.

Primary sources #