SQLSTATE 42P21Severity lowLab verified

Incident brief

Collation mismatch (explicit collations)

A comparison cannot combine two different explicit COLLATE clauses; applying 'C' to one side and 'POSIX' to the other raises this collation-mismatch error.

What lands in your log

ERROR: collation mismatch between explicit collations "C" and "POSIX"

Reproduced on PostgreSQL 18.4Verified 2026-07-24 (isolated lab, PostgreSQL 18.4)Verified against PostgreSQL 18.4 in an isolated lab environment

In 10 seconds

What triggers it: Compare two expressions where each side carries a different explicit COLLATE clause.
Fix: Use a single explicit collation for the comparison, or none and let the column defaults apply.
Proof: Reproduced on PostgreSQL 18.4 → A single query reproduces SQLSTATE 42P21; the two conflicting explicit collations cannot be combined.

Fix

What to do right now

Application-level steps for this error.

Use a single explicit collation for the comparison, or none and let the column defaults apply.
Apply one COLLATE to the whole comparison rather than a different one to each side.

Fix SQL

-- Use one collation (or the column defaults) for the comparison.
SELECT label = note FROM coll_demo;

For this error

See this error live on the server

Run these against the affected instance to confirm the diagnosis before you act.

Collation mismatch (explicit collations) depends on explicit/implicit collation derivation. Inspect the participating columns and database default before adding COLLATE.

Database and column collations

List non-default column collations and the database locale.

SELECT current_database() AS database,
       datcollate, datctype
FROM pg_database
WHERE datname = current_database();
SELECT table_schema, table_name, column_name, collation_name
FROM information_schema.columns
WHERE collation_name IS NOT NULL
ORDER BY table_schema, table_name, ordinal_position;

Reproduce & verify

A real, single-session PostgreSQL reproduction

A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab. The commands below are exactly what was executed.

1Compare two expressions where each side carries a different explicit COLLATE clause.
2PostgreSQL tries to reconcile the two explicit collations for the comparison.
3Because they conflict, the statement aborts with SQLSTATE 42P21.

A comparison hand-applied different COLLATE clauses to each side and PostgreSQL refused to guess which one wins.

Schema setup

CREATE TABLE coll_demo(label text, note text);
INSERT INTO coll_demo VALUES ('a','b');

Combining two explicit collations

SELECT (label COLLATE "C") = (note COLLATE "POSIX") FROM coll_demo;

The session continues normally

SELECT 'ok' AS session_after_error;

What PostgreSQL actually returned

Schema setup output

CREATE TABLE
INSERT 0 1

Combining two explicit collations output

ERROR:  collation mismatch between explicit collations "C" and "POSIX"
LINE 1: SELECT (label COLLATE "C") = (note COLLATE "POSIX") FROM col...
                                           ^

The session continues normally output

 session_after_error 
---------------------
 ok
(1 row)

Connected

Everything this error touches

Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.

Open in the interactive map →

Related errors
Cannot coerce UNION types cannot be matched Indeterminate collation

Verification

PG 18.4

Last verified: 2026-07-24 (isolated lab, PostgreSQL 18.4)
Verification scope: Verified against PostgreSQL 18.4 in an isolated lab environment
Audit status: reviewed

ShareLinkedIn X

Went further?

Pro unlocks the second lab proof

Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.

See Pro on pricing Sign in

FollowSubstack LinkedInnew errors · lab notes · hiring loops