How to fix Indeterminate collation in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 27, 2025· 126 views

PostgreSQL cannot determine which collation to use when comparing columns with conflicting implicit collations. Resolve by explicitly specifying a collation using the COLLATE clause.

Quick fix

Fixes Indeterminate collation

SELECT column_name, collation_name 
FROM information_schema.columns 
WHERE table_name = 'your_table_name' 
AND collation_name IS NOT NULL;
  1. Identify the conflicting collations
  2. Use the COLLATE clause to specify collation explicitly
  3. Fix UNION queries with collation mismatches
  4. Standardize collations in your schema
postgresql — error
SELECT column_name, collation_name 
FROM information_schema.columns 
WHERE table_name = 'your_table_name' 
AND collation_name IS NOT NULL;
Indeterminate collation

What this error means

PostgreSQL uses collations to determine how strings are sorted and compared, including language-specific rules for alphabetical ordering and case sensitivity. When the database encounters conflicting non-default implicit collations among input expressions, it cannot automatically determine which collation rules to apply. This happens most commonly in comparison operations, ORDER BY clauses, and string functions. PostgreSQL considers distinct collation objects to be incompatible even when they have identical properties, such as the C and POSIX collations.

Typical symptoms

  • Comparison operators (<, >, =) fail between columns with different collations
  • ORDER BY clauses raise errors when combining columns with conflicting collations
  • UNION queries fail when combining result sets with mismatched collations
  • Pattern matching operators and string functions like lower(), upper(), and initcap() fail with the error

Common causes

  1. Columns have conflicting implicit collations (e.g., one column uses de_DE while another uses es_ES)
  2. Comparing or ordering columns that were created with different default collations
  3. Attempting to perform string operations on columns without a common collation
  4. UNION operations combining strings with different collations

How to fix "Indeterminate collation"

1Identify the conflicting collations

First, determine which columns or expressions have conflicting collations. Use this query to check the collations in your table:

sql
SELECT column_name, collation_name 
FROM information_schema.columns 
WHERE table_name = 'your_table_name' 
AND collation_name IS NOT NULL;

Also check your database's default collation:

sql
SELECT datcollate 
FROM pg_database 
WHERE datname = 'your_database_name';
2Use the COLLATE clause to specify collation explicitly

Add a COLLATE clause to one of the conflicting expressions to tell PostgreSQL which collation to use. For comparison operations:

sql
SELECT * FROM test1 
WHERE column_a < column_b COLLATE "en_US";

For ORDER BY:

sql
SELECT * FROM test1 
ORDER BY column_a || column_b COLLATE "en_US";
3Fix UNION queries with collation mismatches

When combining queries with UNION, ensure consistent collations by specifying a COLLATE clause:

sql
SELECT name COLLATE "en_US" FROM table1
UNION
SELECT name COLLATE "en_US" FROM table2;
4Standardize collations in your schema

To prevent this error in the future, ensure columns that will be compared have the same collation. When creating or altering tables:

sql
CREATE TABLE test1 (
  column_a text COLLATE "en_US",
  column_b text COLLATE "en_US"
);

If you need to change an existing column's collation:

sql
ALTER TABLE test1 
ALTER COLUMN column_a TYPE text COLLATE "en_US";

Advanced notes

PostgreSQL provides multiple collations including C, POSIX, and locale-specific collations (de_DE, en_US, etc.). The C and POSIX collations are identical and use byte-order comparison. Locale-specific collations follow language-specific rules. When working with databases that support multiple languages or were migrated from systems with different locales, you may need to use explicit COLLATE clauses frequently. In some cases, you can also cast strings to bytea and back to bypass collation requirements, but this is generally not recommended as it loses language-specific sorting semantics.

Frequently asked questions

What does "Indeterminate collation" mean?
PostgreSQL uses collations to determine how strings are sorted and compared, including language-specific rules for alphabetical ordering and case sensitivity. When the database encounters conflicting non-default implicit collations among input expressions, it cannot automatically determine which collation rules to apply. This happens most commonly in comparison operations, ORDER BY clauses, and string functions.
How do I fix "Indeterminate collation"?
PostgreSQL cannot determine which collation to use when comparing columns with conflicting implicit collations. Resolve by explicitly specifying a collation using the COLLATE clause.
What causes "Indeterminate collation"?
Columns have conflicting implicit collations (e.g., one column uses de_DE while another uses es_ES) Comparing or ordering columns that were created with different default collations Attempting to perform string operations on columns without a common collation
Is the "Indeterminate collation" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading