When working with PostgreSQL 9.2 on Ubuntu 12.04, you might encounter the frustrating invalid locale name error even after properly generating the locale through locale-gen. Here's what's happening under the hood:

sudo -u postgres psql -Xc "CREATE DATABASE test TEMPLATE template0 ENCODING 'utf8' LC_CTYPE='it_IT.utf8'"
ERROR:  invalid locale name: "it_IT.utf8"

First, confirm the locale is actually available on your system:

locale -a | grep it_IT
it_IT.utf8

If this returns the locale but PostgreSQL still rejects it, we need to dig deeper.

PostgreSQL maintains its own locale mapping independent of system locales. The key points:

• Postgres checks locales against /usr/share/i18n/SUPPORTED
• Service needs restart after new locale generation
• Template0 has specific locale requirements

Here's the step-by-step fix that works for most cases:

# 1. Regenerate locales with explicit encoding
sudo locale-gen it_IT.UTF-8

# 2. Update locale definitions (critical step!)
sudo update-locale LANG=en_US.UTF-8
sudo dpkg-reconfigure locales

# 3. Verify system can see the locale
locale -a | grep -i it_it

# 4. Restart PostgreSQL service
sudo service postgresql restart

# 5. Create database with proper case sensitivity
sudo -u postgres psql -c "CREATE DATABASE test WITH TEMPLATE template0 ENCODING 'UTF8' LC_CTYPE='it_IT.UTF-8' LC_COLLATE='it_IT.UTF-8'"

If the above fails, you might need to reinitialize the cluster:

sudo pg_dropcluster --stop 9.2 main
sudo pg_createcluster --locale it_IT.UTF-8 --start 9.2 main

When all else fails, try these advanced techniques:

# Check PostgreSQL's locale awareness
sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name LIKE '%locale%' OR name LIKE '%encoding%';"

# Inspect system locale files
cat /etc/default/locale
cat /var/lib/locales/supported.d/local

# Test locale generation manually
localedef -i it_IT -f UTF-8 it_IT.UTF-8

The Ubuntu 12.04 + PostgreSQL 9.2 combination has known issues with:

• Locale name case sensitivity (UTF-8 vs utf8)
• Missing locale-archive updates
• Template0's strict validation

Always prefer UTF-8 (uppercase) in PostgreSQL commands even if your system shows utf8.

---

When setting up PostgreSQL 9.2 on Ubuntu 12.04, you might encounter the frustrating "invalid locale name" error even when the locale appears to be properly generated. Here's what's happening under the hood:

sudo -u postgres psql -Xc "CREATE DATABASE test TEMPLATE template0 ENCODING 'utf8' LC_CTYPE='it_IT.utf8'"
ERROR:  invalid locale name: "it_IT.utf8"

First, confirm your locale is actually available on the system:

locale -a
C
C.UTF-8
en_GB.utf8
en_US.utf8
it_IT.utf8
POSIX

If your locale appears here but PostgreSQL still rejects it, we need to dig deeper.

PostgreSQL has specific requirements for locale naming that don't always match the system's locale names. Try these variations:

-- Try without the encoding suffix
CREATE DATABASE test TEMPLATE template0 ENCODING 'utf8' LC_CTYPE='it_IT'

-- Try with different case
CREATE DATABASE test TEMPLATE template0 ENCODING 'utf8' LC_CTYPE='it_IT.UTF-8'

To ensure proper locale support:

# Uncomment the locale in /etc/locale.gen
sudo sed -i 's/^# it_IT.UTF-8 UTF-8/it_IT.UTF-8 UTF-8/' /etc/locale.gen

# Regenerate locales
sudo locale-gen

# Update the system
sudo update-locale LANG=it_IT.UTF-8

If you still face issues, try creating the database with default locale first, then alter it:

CREATE DATABASE test TEMPLATE template0 ENCODING 'utf8';
ALTER DATABASE test SET lc_ctype = 'it_IT.utf8';

PostgreSQL maintains its own list of supported collations. Check them with:

SELECT * FROM pg_collation;

If your desired locale doesn't appear here, you'll need to rebuild PostgreSQL with proper locale support.

As a last resort, reinstall PostgreSQL with all locales:

sudo apt-get --purge remove postgresql-9.2
sudo apt-get install locales-all
sudo apt-get install postgresql-9.2 postgresql-contrib-9.2

This ensures all locale data is available during PostgreSQL installation.

For deeper investigation, check PostgreSQL's locale awareness:

SHOW lc_collate;
SHOW lc_ctype;

Compare these with your system's locale command output to identify mismatches.