PostgreSQL uniqueness depends on the query's equality

A unique index is evidence about the comparisons it enforces. Before a query planner uses that evidence to conclude that a lookup can match at most one row, the query's equality rules must support the same conclusion. Text comparisons make that qualification consequential: collation can affect which strings count as equal, as well as their ordering.

The PostgreSQL 18.4 release notes, dated 14 May 2026, describe a correction to uniqueness assumptions involving nondeterministic collations. The release notes explain that a unique index and a query condition can use different collations, making a one-row inference unsafe. The announcement on the same date records the historical maintenance release. These are two records of one release, rather than independent discoveries of the problem.

The useful design lesson is to establish the equality contract before relying on an optimization. A fast plan is valuable only when its transformations preserve the answer the query asks for.

The scope of a uniqueness claim

Consider an illustrative application that stores identifiers as text but also offers a forgiving lookup for people entering them. Storage may distinguish spellings that the lookup considers equivalent. In that design, the application has two different questions: whether a stored identifier is distinct, and whether a search term matches it.

A uniqueness constraint can answer the first question under its own comparison rules. It cannot, by its existence alone, answer the second under different rules. If a broader lookup groups several stored values together, uniqueness at storage time does not establish that the lookup returns only one of them. This is a reasoning example, not a report of an executed query or a specific collation configuration.

That distinction matters even when the application usually receives exact identifiers. A convenient search feature, an explicit comparison setting or a later change in how text is interpreted can introduce another equality relation. The visible value in a column is insufficient to explain the contract. You also need to know how the relevant operation compares it.

A proof comes before a cheaper plan

The release notes explain that deterministic collations make string equality require bitwise equality, allowing the relevant inference even when their ordering rules differ. They also explain why a nondeterministic collation on either side can invalidate an assumption of unique matches when the semantics differ. That is a statement about the validity of an inference, not evidence that every text index is broken.

For an application review, start by writing down what a successful lookup means. Does it select an exact stored identifier, or all records that satisfy a reader-friendly comparison? If the latter can produce multiple matches, the interface needs a policy for them. Choosing the first result without an explicit rule would hide ambiguity rather than resolve it.

Next, identify where the application depends on a single match. An account selection screen and an internal reconciliation task may both read the same column while needing different behavior. The question is whether the claim of uniqueness still holds at each boundary. Performance decisions belong after that explanation, because a cheaper execution cannot repair an incorrect premise.

Deterministic comparison can be the right choice

The strongest counterargument is practical: many identifiers should have exact identity. A deterministic collation may fit that requirement better and can permit planner shortcuts whose prerequisites hold. There is no benefit in introducing forgiving equality into a machine identifier merely because the database can express it.

That choice should follow the product's rules. If users are promised that two spellings refer to the same account, switching to stricter comparison solely to regain an optimization may violate the promise. Conversely, treating distinct external identifiers as interchangeable can be wrong even if it makes a search box easier to use. Define the identifier first, then choose the comparison behavior.

It can be useful to separate discovery from selection. A search may offer several plausible entries while the next operation uses an exact identifier chosen by the user. This keeps forgiving search from silently becoming the authority for a unique business object. It is an architectural option, not a requirement for every text field.

Review changes without broad conclusions

A maintenance review should distinguish a framework or database correction from an application migration. The PostgreSQL correction concerns planner reasoning. Deciding to change a column's comparison rules is a separate product decision, with consequences for the values users can distinguish. One does not automatically justify the other.

Useful review evidence would include the relevant column and index definitions, the comparison used by the query, and the business rule that requires one match. Those materials let a reviewer assess the premise. A screenshot showing one result in ordinary use would not establish that the result is unique for every allowed input.

This article reports no benchmark and no database execution. The historical release is a reason to examine an assumption, not a diagnosis of an unspecified installation. Application-specific consequences require evidence from that application's schema and queries.

When planning software maintenance, make the comparison contract part of the work being handed over. Record which values must remain distinct, where tolerant lookup is allowed and what happens when it finds several candidates. That gives future changes a concrete correctness condition. Once the condition is established, the team can consider which shortcuts are valid and what performance evidence it needs.