The collation_mismatch error (42P21) fires when PostgreSQL evaluates text operands with incompatible collations in the same expression or query clause.
PostgreSQL error 42P21 (collation_mismatch) happens when a query mixes text values that use different collations. Explicitly coerce every text value to the same collation with the COLLATE clause or change the column definitions to share one collation to resolve the error.
PostgreSQL Error 42P21
Error 42P21 tells PostgreSQL that two or more text operands in the same expression carry different collations. PostgreSQL must apply one set of collation rules to compare, sort, or aggregate text. Mixed rules leave the planner unable to decide which to use, so it stops with collation_mismatch.
The error surfaces while comparing text columns, ordering results, grouping, or using text functions where implicit collations are inherited from column definitions or database defaults.
Fixing it is important because it blocks query execution and can hide deeper data-quality issues.
Implicit collations on text columns often differ across schemas or after migrations. When queries join or compare these columns, PostgreSQL inherits incompatible collations and raises 42P21.
Literal strings adopt the database default collation.
Mixing a literal with a differently collated column triggers the mismatch unless the literal is cast with COLLATE.
Force every operand to share one collation. Use the COLLATE clause inline, cast literals, or redefine columns to the same collation. Verify the server’s default collation if literals are involved.
For transient fixes, add COLLATE "en_US" (or your target) to each operand.
For long-term stability, align column collations during schema design and migrations.
JOIN or WHERE clause comparing name columns from two tables: apply COLLATE to one side or alter the column definition.
ORDER BY on a view that selects mixed-collation columns: adjust the SELECT list with COLLATE.
Standardize one collation per database or at least per logical data domain.
Document it in migration files.
Use explicit COLLATE in code whenever a query crosses schemas or databases. Automated linters in tools like Galaxy can flag unqualified text comparisons.
42P22 (indeterminate collation): occurs when the result collation cannot be deduced. Apply explicit COLLATE.
42804 (datatype_mismatch): arises when operands have incompatible data types. Cast explicitly.
0A000 (feature_not_supported) for ICU collations on older versions: upgrade or switch to ICU-enabled build.
.
The migration likely created new tables with the server’s current collation, differing from existing tables. Mixing old and new columns then produced 42P21.
No safe setting turns them off. For correctness, PostgreSQL must enforce one collation per comparison. Always fix the query or schema.
Explicit COLLATE adds negligible overhead. Indexes on the same collation remain usable, so queries stay fast.
Galaxy’s editor detects collation mismatches during linting and suggests the proper COLLATE clause, reducing runtime errors.
.avif)
.avif){kind=logo}
