indeterminate collation
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.
- 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
Comparison of strings with conflicting collations.
SELECT 'hello' COLLATE "en-US" = 'hello' COLLATE "fr-FR";
expected output
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 When comparing strings with different collations.
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 When schema design allows collation standardisation.
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.
Class 42 — Syntax Error or Access Rule Violation (Postgres-specific)
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev