Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19646: Unique index with explicit COLLATE bypasses
 nondeterministic collation rule
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: jj-zhang25@mails.tsinghua.edu.cn
Reply-To: jj-zhang25@mails.tsinghua.edu.cn, pgsql-bugs@lists.postgresql.org
Date: Sat, 29 Aug 2026 05:39:49 +0000
Message-ID: <19646-b1cfc299badcbd7e@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19646-b1cfc299badcbd7e%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19646
Logged by:          =E6=94=BE=E7=A9=BA
Email address:      jj-zhang25@mails.tsinghua.edu.cn
PostgreSQL version: 18.6
Operating system:   MacOS
Description:       =20

Summary
=3D=3D=3D=3D=3D=3D=3D

PostgreSQL enforces, for foreign keys, that

  "If either collation is nondeterministic, then both collations have to be
the
   same."

It enforces an equivalent restriction for PRIMARY KEY / UNIQUE table
constraints
built on an index whose column carries a non-default sort specification. It
does
not enforce it for a plain CREATE UNIQUE INDEX with an explicit COLLATE
clause,
nor for an exclusion constraint with one.

The result is a table carrying a unique index on a column while two rows of
that
table are equal under the column's own collation. An equality lookup on the
indexed column returns two rows, which is what a unique index is normally
taken
to rule out.


Reproduction
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

  CREATE COLLATION ci (provider=3Dicu, locale=3D'und-u-ks-level2',
                       deterministic=3Dfalse);

  CREATE TABLE f1(id int primary key, email text COLLATE ci);
  CREATE UNIQUE INDEX f1u ON f1 (email COLLATE "C");

  INSERT INTO f1 VALUES (1,'user@corp.com'),(2,'USER@corp.com');
  INSERT 0 2

  SELECT id, email FROM f1 ORDER BY id;
   id |     email
  ----+---------------
    1 | user@corp.com
    2 | USER@corp.com

The two values are equal under the column's collation:

  SELECT (a.email =3D b.email) FROM f1 a, f1 b WHERE a.id=3D1 AND b.id=3D2;
   t

and an equality lookup on the indexed column returns both:

  SELECT count(*) FROM f1 WHERE email=3D'user@corp.com';
   2

An exclusion constraint has the same gap:

  CREATE TABLE f2(id int, email text COLLATE ci,
                  CONSTRAINT f2x EXCLUDE ((email COLLATE "C") WITH =3D));
  INSERT INTO f2 VALUES (1,'user@corp.com'),(2,'USER@corp.com');
  INSERT 0 2


Where the rule is enforced, and where it is not
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Same column definition (text COLLATE ci) in each case:

  -- foreign key referencing a "C" collated column
  CREATE TABLE r0(c text COLLATE "C" primary key);
  CREATE TABLE r1(c text COLLATE ci REFERENCES r0(c));
    ERROR:  foreign key constraint "r1_c_fkey" cannot be implemented
    DETAIL: Key columns "c" of the referencing table and "c" of the
referenced
            table have incompatible collations: "ci" and "C".  If either
            collation is nondeterministic, then both collations have to be
the
            same.

  -- promoting such an index to a constraint
  CREATE UNIQUE INDEX r3u ON r3 (c COLLATE "C");
  ALTER TABLE r3 ADD PRIMARY KEY USING INDEX r3u;
    ERROR:  index "r3u" column number 1 does not have default sorting
behavior
    DETAIL: Cannot create a primary key or unique constraint using such an
index.

  -- unique index without an explicit COLLATE: correctly uses the column's
  INSERT INTO r5 VALUES (1,'A'),(2,'a');
  CREATE UNIQUE INDEX r5u ON r5 (c);
    ERROR:  could not create unique index "r5u"
    DETAIL: Key (c)=3D(A) is duplicated.

  -- the gap
  CREATE UNIQUE INDEX ... (c COLLATE "C")           -- accepted
  EXCLUDE ((c COLLATE "C") WITH =3D)                  -- accepted

The first three show the restriction exists and that PostgreSQL has the
information needed to apply it at that point. The last two are the gap.

Note the second case in particular: PostgreSQL already refuses to let such
an
index back a constraint, on the grounds that its sort specification is not
the
column's. The same index, left as a bare unique index, enforces uniqueness
under
that non-default specification without objection.


Why this seems worth restricting
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D

For deterministic collations an index collation differing from the column's
is
harmless for uniqueness: equality is byte equality regardless of collation,
so a
unique index under any deterministic collation enforces exactly the same set
of
duplicates. The choice only affects ordering, and thus which queries can use
the
index.

With a nondeterministic collation that is no longer true, because the
collation
defines equality itself. A unique index built under a different collation
enforces a different equality relation from the one the column's operators
use.
That is the situation the foreign key check exists to prevent, and it
appears to
apply equally here.


Note on severity
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

This is not a dump/restore hazard: pg_dump recreates the index with the same
explicit COLLATE, so a restore reproduces the same state rather than
failing.
Query results are also not wrong -- the equality lookup returning two rows
is the
correct answer under the column's collation. The issue is that the declared
constraint does not mean what an equality lookup on the column means, so an
application relying on the unique index to make that lookup single-valued is
mistaken in a way the schema does not reveal.

I am reporting a second, more serious gap in the same family separately: a
partition key with a non-default opclass or COLLATE over a
nondeterministically
collated column, which does produce wrong query results and rows missed by
UPDATE
and DELETE. The two share the shape "a restriction applied at some sites and
not
others", but the fixes would go in different places, so I have not combined
them.


Suggested fix
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Apply the existing check to CREATE UNIQUE INDEX and to exclusion
constraints:
when the index or constraint specifies a collation that differs from the
column's, and either collation is nondeterministic, refuse with the message
already used for foreign keys. Non-unique indexes with an explicit COLLATE
are
unaffected, since they only influence ordering and do not assert a
uniqueness
property.


Documentation
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Section 23.2 (Collation Support) mentions that B-tree deduplication is
unavailable with nondeterministic collations and that some pattern matching
operations are not possible. It does not state the same-collation
requirement
that the foreign key error message gives, nor its absence for unique indexes
and
exclusion constraints. If the current behaviour is intended rather than an
oversight, the documentation gap seems worth closing regardless.


Prior discussion
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

I searched the mailing list archives, the TODO list and the FAQ and found no
prior report of this.