42P21ERRORTier 2 — Caution✅ HIGH confidence

indeterminate collation

Category: Syntax Error or Access Rule ViolationVersions: All Postgres versions

What this means

SQLSTATE 42P21 is a Postgres-specific error raised when Postgres cannot determine a single collation for an operation that requires a collation (e.g., string comparison or sorting) because multiple conflicting collations are involved.

Why it happens

1Comparing or sorting strings that have different explicit collations (e.g., one column has COLLATE "en-US" and another COLLATE "fr-FR")
2An expression combines strings from different collation domains without specifying which collation to use

How to reproduce

Comparison of strings with conflicting collations.

trigger — this will ERROR

SELECT 'hello' COLLATE "en-US" = 'hello' COLLATE "fr-FR";

ERROR: collation mismatch between implicit collations "en-US" and "fr-FR" HINT: You can choose the collation by applying the COLLATE clause to one or both expressions.

Fix 1: Specify the collation explicitly with COLLATE

When comparing strings with different collations.

fix

SELECT 'hello' COLLATE "en-US" = 'hello' COLLATE "en-US"; -- same collation

Why this works

Using the same collation on both sides of the comparison resolves the ambiguity.

Fix 2: Define columns with the same collation or use the database default

When schema design allows collation standardisation.

fix

CREATE TABLE items (
  name TEXT COLLATE "en-US"
);

Why this works

Using a single consistent collation across all string columns in a table eliminates cross-collation comparison issues.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/collation.html

🔧 Source ref: Class 42 — Syntax Error or Access Rule Violation (Postgres-specific)

Confidence assessment

✅ HIGH confidence

Postgres-specific. Stable across versions. ICU collation support expanded in Postgres 10+.