Incident brief
Indeterminate collation
When PostgreSQL cannot derive a collation for a string operation, it raises this error and hints to add an explicit COLLATE clause.
What lands in your log
ERROR: could not determine which collation to use for string comparison
In 10 seconds
- What triggers it
- Perform a string comparison where no collation can be derived from the inputs.
- Fix
- Add an explicit COLLATE clause to set the collation for the operation.
- Proof
- Reproduced on PostgreSQL 18.4 → A single query reproduces SQLSTATE 42P22; without a derivable collation the comparison is rejected with a hint.
Fix
What to do right now
Application-level steps for this error.
- Add an explicit COLLATE clause to set the collation for the operation.
- Apply the same collation to both operands so the comparison is well-defined.
-- Set the collation explicitly with COLLATE.
SELECT (label COLLATE "C") < (note COLLATE "C") FROM coll_demo2;For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Indeterminate collation 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;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual, not paraphrased.
PostgreSQL 18 Documentation, Appendix A. PostgreSQL Error Codes (Table A.1, Class 42, Syntax Error or Access Rule Violation)
42P22 → indeterminate_collationRead the full section on postgresql.org →
Comparing with no derivable collation
PostgreSQL could not choose a collation for the comparison, so it raises 'could not determine which collation to use' and hints to use COLLATE.The session continues normally
PostgreSQL rejected the comparison outside a transaction block, so session B continues normally with no ROLLBACK required.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.
- 1Perform a string comparison where no collation can be derived from the inputs.
- 2PostgreSQL fails to pick a collation for the operation.
- 3The statement aborts with SQLSTATE 42P22 and a hint to use COLLATE.
A comparison over expressions with unresolvable collations failed until an explicit COLLATE was supplied.
CREATE TABLE coll_demo2(label text COLLATE "C", note text COLLATE "POSIX");
INSERT INTO coll_demo2 VALUES ('a','b');SELECT label < note FROM coll_demo2;SELECT 'ok' AS session_after_error;What PostgreSQL actually returned
CREATE TABLE
INSERT 0 1ERROR: could not determine which collation to use for string comparison
HINT: Use the COLLATE clause to set the collation explicitly. session_after_error
---------------------
ok
(1 row)The comparison succeeds once an explicit COLLATE resolves the collation.
Without this
Before: the collation is indeterminate
With this, tested
After: COLLATE makes it definite
- A second operational test: exact SQL, raw output, measured result, and engineer notes
Card required. Cancel before day 7 and you are not charged.
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.
Verification
- 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
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.