How to fix Collation "name" does not exist in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 153 views

PostgreSQL collation errors occur when you reference a collation that is not available on your system. This typically happens after OS upgrades, during database migrations, or when using locale settings that are not installed. Check available collations and install missing locales to resolve the issue.

Quick fix

Fixes Collation "name" does not exist

SELECT collname FROM pg_collation ORDER BY collname;
  1. Check available collations on your PostgreSQL instance
  2. Check available system locales
  3. Generate the missing locale on Linux
  4. Restart PostgreSQL to refresh collation cache
postgresql — error
SELECT collname FROM pg_collation ORDER BY collname;
Collation "name" does not exist

What this error means

The "collation does not exist" error occurs when PostgreSQL cannot find the specified collation in its catalog. Collations are locale-aware sorting and comparison rules that PostgreSQL uses for string operations. This error means either the collation was never created on your system, the system locale is missing, or there is a version mismatch between PostgreSQL and the operating system's locale database. PostgreSQL relies on the operating system's localization support, so system-level locale configurations directly affect available collations.

Typical symptoms

  • CREATE TABLE or ALTER TABLE statements fail when specifying a collation
  • Queries fail with "collation "name" does not exist" when using COLLATE clause
  • Database migration or restore fails at collation-related operations
  • Application startup fails when trying to create tables with specific collations
  • Error occurs during column creation with non-default collation specifications

Common causes

  1. The specified collation is not available on the operating system
  2. The system locale was not generated or installed (e.g., en_US.UTF-8 not available on Linux)
  3. PostgreSQL version mismatch with OS locale database after an upgrade
  4. Migrating a database from a system with different available locales
  5. Typo in the collation name (collation names are case-sensitive)
  6. Using a collation that is specific to one database without creating it first

How to fix "Collation "name" does not exist"

1Check available collations on your PostgreSQL instance

Connect to your database and query the available collations:

sql
SELECT collname FROM pg_collation ORDER BY collname;

This lists all collations that PostgreSQL recognizes on your system. Look for the collation you're trying to use. If it's not in the list, you need to make it available.

For a more detailed view:

sql
SELECT collname, collencoding, collisvalid 
FROM pg_collation 
WHERE collencoding IN (-1, 6)  -- UTF8
ORDER BY collname;
2Check available system locales

On Linux/Unix, check which locales are installed:

bash
locale -a

This shows all generated locales on your system. If you're looking for en_US.UTF-8 and it's not listed, you need to generate it.

On macOS, use:

bash
locale -a | grep en_US
3Generate the missing locale on Linux

If the locale doesn't exist, generate it:

bash
# On Debian/Ubuntu
sudo locale-gen en_US.UTF-8
sudo update-locale LANG=en_US.UTF-8

# On RHEL/CentOS/Fedora
sudo localedef -i en_US -f UTF-8 en_US.UTF-8

# Verify it was created
locale -a | grep en_US

Note: You need root privileges to generate locales. The specific commands vary by Linux distribution.

4Restart PostgreSQL to refresh collation cache

After installing the locale on the system, restart the PostgreSQL service:

bash
# On systemd (most modern systems)
sudo systemctl restart postgresql

# On other systems
sudo service postgresql restart

# Or via Docker
docker restart <postgres_container_name>

PostgreSQL caches available locales at startup, so you must restart it to pick up newly installed system locales.

5Verify the collation is now available

After restarting PostgreSQL, verify the collation now appears:

sql
SELECT collname FROM pg_collation WHERE collname = 'en_US.utf8' OR collname = 'en_US.UTF-8';

Note: PostgreSQL normalizes collation names, so en_US.UTF-8 may appear as en_US.utf8 in the catalog. You can use either format when creating tables, PostgreSQL will match it correctly.

6Create the table/column with the collation

Once the collation is available, you can now use it:

sql
-- Create a table with a specific collation
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT COLLATE "en_US.utf8",
  email TEXT COLLATE "en_US.utf8"
);

-- Or alter a column
ALTER TABLE users 
ALTER COLUMN name TYPE TEXT COLLATE "en_US.utf8";

-- Create a database with a specific collation
CREATE DATABASE mydb 
  WITH ENCODING 'UTF8' 
  LC_COLLATE = 'en_US.UTF-8' 
  LC_CTYPE = 'en_US.UTF-8';

The collation is now available and the error should be resolved.

Advanced notes

Collation version mismatches can occur after glibc or OS upgrades, where PostgreSQL reports "collation version mismatch" rather than "does not exist." In such cases, refresh collation versions with ALTER DATABASE dbname REFRESH COLLATION VERSION; and reindex affected tables. When migrating databases across systems with different locale configurations, always verify available collations on both source and target systems. PostgreSQL also supports ICU (International Components for Unicode) collations as an alternative to system locales—compile PostgreSQL with --with-icu to enable them. For production deployments, always document required locales in deployment scripts and ensure they're installed before database initialization. Collation selection affects sorting, comparison, and full-text search behavior, so changing collations on existing columns requires careful planning and potential data reindexing.

Frequently asked questions

What does "Collation "name" does not exist" mean?
The "collation does not exist" error occurs when PostgreSQL cannot find the specified collation in its catalog. Collations are locale-aware sorting and comparison rules that PostgreSQL uses for string operations. This error means either the collation was never created on your system, the system locale is missing, or there is a version mismatch between PostgreSQL and the operating system's locale database.
How do I fix "Collation "name" does not exist"?
PostgreSQL collation errors occur when you reference a collation that is not available on your system. This typically happens after OS upgrades, during database migrations, or when using locale settings that are not installed. Check available collations and install missing locales to resolve the issue.
What causes "Collation "name" does not exist"?
The specified collation is not available on the operating system The system locale was not generated or installed (e.g., en_US.UTF-8 not available on Linux) PostgreSQL version mismatch with OS locale database after an upgrade
Is the "Collation "name" does not exist" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading