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

Connect to your database and query the available collations:

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:

SELECT collname, collencoding, collisvalid 
FROM pg_collation 
WHERE collencoding IN (-1, 6)  -- UTF8
ORDER BY collname;

On Linux/Unix, check which locales are installed:

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:

locale -a | grep en_US

If the locale doesn't exist, generate it:

# 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.

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

# 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.

After restarting PostgreSQL, verify the collation now appears:

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.

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

-- 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.