PostgreSQL Invalid Database Definition (SQLSTATE 42P12)

When PostgreSQL encounters an invalid or conflicting option in a CREATE DATABASE or ALTER DATABASE statement, it raises:

ERROR:  invalid_database_definition
SQLSTATE: 42P12

The condition name is invalid_database_definition. This error indicates that the database-level DDL command you issued contains an option value or combination of options that PostgreSQL cannot accept.

What This Error Means

SQLSTATE 42P12 belongs to error class 42 — Syntax Error or Access Rule Violation. The P in the subcode marks it as a PostgreSQL-specific extension to the SQL standard, meaning it is not defined in the SQL standard itself but is specific to PostgreSQL's DDL handling.

The error is raised during parsing or semantic validation of CREATE DATABASE and ALTER DATABASE statements. PostgreSQL checks the supplied options — such as encoding, locale, template, tablespace, connection limit, and collation — before any changes are made to pg_database. Because the error occurs before any modification is committed, there are no partial side effects: if you see this error, the database was not created or altered.

The connection remains valid after this error. If you are running inside an explicit transaction block, however, the transaction is aborted and you must issue a ROLLBACK before issuing further commands.

Common Causes

1. Conflicting encoding and locale settings. Specifying an ENCODING that is incompatible with the chosen LC_COLLATE or LC_CTYPE locale (for example, UTF8 encoding with a locale that only supports SQL_ASCII) will trigger this error.

2. Invalid TEMPLATE database. Supplying a template database that has active connections, does not exist, or is itself being modified concurrently causes PostgreSQL to reject the CREATE DATABASE statement.

3. Unsupported or misspelled option keyword. Using an option name that PostgreSQL does not recognize in a CREATE DATABASE or ALTER DATABASE statement — for instance, a new option available in a later PostgreSQL version run against an older server.

4. Invalid CONNECTION LIMIT value. Supplying a non-integer or out-of-range value for CONNECTION LIMIT.

5. Locale provider conflicts (icu vs libc). In PostgreSQL 15 and later, specifying LOCALE_PROVIDER = icu without a valid ICU_LOCALE, or combining LOCALE with ICU_LOCALE when the provider is libc, produces this error.

6. Tablespace does not exist. Referencing a TABLESPACE that has not been created will cause the statement to fail with this SQLSTATE.

How to Fix invalid_database_definition

1. Verify encoding/locale compatibility. Choose an encoding and locale that are mutually compatible. For Unicode databases the standard pairing is:

   CREATE DATABASE mydb
     ENCODING = 'UTF8'
     LC_COLLATE = 'en_US.UTF-8'
     LC_CTYPE   = 'en_US.UTF-8'
     TEMPLATE   = template0;
   

   Always use TEMPLATE = template0 when specifying a non-default encoding or locale, because template1 may already have an incompatible encoding.

2. Check the template database. Ensure no other sessions are connected to the template before running CREATE DATABASE:

   SELECT pid, usename, application_name
   FROM   pg_stat_activity
   WHERE  datname = 'template1';
   

   Terminate any connections, or use template0 which never has active connections.

3. Check available tablespaces. Confirm the tablespace exists before referencing it:

   SELECT spcname FROM pg_tablespace;
   

4. Use the correct locale provider syntax (PostgreSQL 15+). For ICU locales:

   CREATE DATABASE mydb
     LOCALE_PROVIDER = icu
     ICU_LOCALE      = 'en-US'
     TEMPLATE        = template0;
   

   For libc locales, omit ICU_LOCALE and use LC_COLLATE/LC_CTYPE instead.

5. Validate the CONNECTION LIMIT. Supply an integer value of -1 (unlimited) or a positive integer:

   ALTER DATABASE mydb CONNECTION LIMIT 100;
   

6. Check your PostgreSQL server version. If a CREATE DATABASE option such as OID (added in PostgreSQL 14 for pg_upgrade compatibility) or STRATEGY (added in PostgreSQL 15) is referenced against an older server, remove or replace the unsupported option.

Additional Information

• SQLSTATE class 42 sibling conditions include 42601 (syntax_error), 42P01 (undefined_table), 42P07 (duplicate_table), and 42501 (insufficient_privilege). If the problem is a misspelled keyword rather than an invalid option value, you may see 42601 instead.
• PostgreSQL 15 significantly expanded CREATE DATABASE options (ICU locale provider, STRATEGY, BUILTIN locale provider in some builds). Statements generated for PostgreSQL 15+ will fail on older servers with 42P12 or 42601.
• Most PostgreSQL drivers (libpq, psycopg2, asyncpg, JDBC) expose this error through their standard exception hierarchy using the SQLSTATE code. In psycopg2 it surfaces as psycopg2.errors.InvalidDatabaseDefinition; in JDBC as a SQLException with getSQLState() returning "42P12".
• This error cannot be triggered by ordinary DML (SELECT, INSERT, UPDATE, DELETE). It is exclusively a DDL error affecting database-level objects.

Frequently Asked Questions

Why do I need TEMPLATE = template0 when setting a custom encoding? template1 is copied verbatim when creating a new database. If template1 was initialized with a different encoding or locale, PostgreSQL refuses to create a database with conflicting settings. template0 is a pristine, connection-free template that allows any supported encoding/locale combination.

Can this error occur inside a migration script? Yes. Tools like Flyway, Liquibase, or raw psql scripts that include CREATE DATABASE or ALTER DATABASE statements can raise 42P12. Because most migration frameworks run inside a transaction and CREATE DATABASE cannot run inside a transaction block, you may need to execute these statements outside of any transaction (e.g., with psql's \c or by using autocommit=True in your driver).

Does 42P12 mean my database is corrupted? No. This error is raised during statement validation, before any change is applied. Your existing databases and their data are unaffected.

What is the difference between 42P12 and 42601? 42601 is a syntax error — the SQL text could not be parsed at all. 42P12 means the syntax was valid but the combination of option values is semantically invalid for a database definition. Both belong to class 42 (syntax/access rule violations).