How to fix 42P12: invalid_database_definition in PostgreSQL

Verify your CREATE DATABASE statement matches PostgreSQL syntax. The basic form is:

CREATE DATABASE database_name
  WITH
  OWNER = owner_name
  ENCODING = encoding_name
  LOCALE = locale_name
  TEMPLATE = template_name;

Ensure all options are valid and spelled correctly.

Check that your ENCODING and LOCALE values are supported:

# List available encodings
psql -c "SELECT name FROM pg_catalog.pg_encoding_list()"

# List available locales (on Linux)
locale -a

Use only valid encoding names like UTF8, LATIN1, and locale strings like en_US.UTF-8.

If using a TEMPLATE, ensure the template database exists:

-- List available templates
SELECT datname FROM pg_database WHERE datistemplate = true;

-- Create database with valid template
CREATE DATABASE mydb TEMPLATE template0 ENCODING 'UTF8';

Common templates are template0 and template1.

Remove conflicting or duplicate options:

-- WRONG: both LOCALE and LCCOLLATE specified incorrectly
CREATE DATABASE mydb LOCALE 'en_US' LCCOLLATE 'en_US';

-- CORRECT: use LOCALE or individual LC_* options
CREATE DATABASE mydb LOCALE 'en_US.UTF-8';

Consult your PostgreSQL version documentation for the correct option names.

Start with the simplest valid CREATE DATABASE statement and add options one by one:

-- Minimal: should always work
CREATE DATABASE testdb;

-- Add options gradually
CREATE DATABASE testdb ENCODING 'UTF8';
CREATE DATABASE testdb ENCODING 'UTF8' LOCALE 'C';

This helps identify which option is causing the error.

Error messages and supported options vary by PostgreSQL version. Check the official documentation:

- PostgreSQL 15+: https://www.postgresql.org/docs/current/sql-createdatabase.html
- PostgreSQL 13: https://www.postgresql.org/docs/13/sql-createdatabase.html
- PostgreSQL 12: https://www.postgresql.org/docs/12/sql-createdatabase.html

Ensure your options match the documentation for your specific version.