DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 42P12 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P12: invalid database definition

PostgreSQL error code 42P12 (invalid_database_definition) occurs when a CREATE DATABASE or ALTER DATABASE statement contains an invalid option, an incompatible parameter combination, or references a non-existent object such as a template database or tablespace. This error falls under the SQL standard class 42 (Syntax Error or Access Rule Violation) and is specific to database-level definition problems. Understanding the root cause quickly is essential, as this error typically blocks new database provisioning in production environments.

Top 3 Causes and Fixes

1. Invalid Encoding and Locale Combination

Specifying an encoding/locale pair that is either incompatible or not installed on the operating system is the most common cause of 42P12.

-- BAD: locale not installed on the OS
CREATE DATABASE mydb
    ENCODING 'UTF8'
    LC_COLLATE 'ko_KR.UTF-8'
    LC_CTYPE 'ko_KR.UTF-8';

-- GOOD: use template0 with a compatible locale
CREATE DATABASE mydb
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

-- SAFEST: use C locale for maximum portability
CREATE DATABASE mydb_portable
    ENCODING 'UTF8'
    LC_COLLATE 'C'
    LC_CTYPE 'C'
    TEMPLATE template0;

-- Check what your template0 uses as a reference
SELECT datcollate, datctype, pg_encoding_to_char(encoding)
FROM pg_database
WHERE datname = 'template0';
Enter fullscreen mode Exit fullscreen mode

Always use TEMPLATE template0 when specifying a non-default encoding, as template1 inherits the server's default encoding and locale.

2. Template Database Has Active Connections

PostgreSQL copies the template database to create a new one. If any session is connected to the template database at that moment, the operation fails with 42P12.

-- Check active connections on the template database
SELECT pid, usename, application_name, state
FROM pg_stat_activity
WHERE datname = 'my_template_db';

-- Terminate active connections (requires superuser)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'my_template_db'
  AND pid <> pg_backend_pid();

-- Now safely create the database from the template
CREATE DATABASE newdb
    TEMPLATE my_template_db;

-- Verify available databases and template status
SELECT datname, datistemplate, datallowconn
FROM pg_database
ORDER BY datname;
Enter fullscreen mode Exit fullscreen mode

3. Invalid Option Values (CONNECTION LIMIT, TABLESPACE)

Passing an out-of-range value for CONNECTION LIMIT or referencing a non-existent tablespace will also trigger this error.

-- BAD: invalid connection limit and non-existent tablespace
CREATE DATABASE baddb
    CONNECTION LIMIT -5          -- only -1 (unlimited) is valid for no limit
    TABLESPACE nonexistent_ts;

-- GOOD: valid options
CREATE DATABASE gooddb
    OWNER app_user
    ENCODING 'UTF8'
    CONNECTION LIMIT 100
    TABLESPACE pg_default;

-- Alter an existing database's connection limit
ALTER DATABASE gooddb CONNECTION LIMIT 200;

-- Check existing tablespaces before specifying one
SELECT spcname, pg_tablespace_location(oid)
FROM pg_tablespace;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  1. Verify locale availability on the OS before running CREATE DATABASE (locale -a on Linux).
  2. Always use TEMPLATE template0 when overriding encoding or locale settings.
  3. Terminate active sessions on the template database before using it as a template.
  4. Confirm tablespace existence with SELECT * FROM pg_tablespace before referencing it.
  5. Check PostgreSQL release notes after version upgrades — supported options for CREATE DATABASE change between major versions (e.g., ICU_LOCALE and LOCALE_PROVIDER were added in PostgreSQL 15).

Prevention Tips

  • Standardize your database creation scripts and run them through a test environment first. Include a pre-flight check query to validate that the target locale and tablespace exist before executing CREATE DATABASE.
  • Version-lock your DDL scripts by maintaining separate scripts per PostgreSQL major version, especially around database definition options that evolve between releases.

Related Errors

Code Name Description
42601 syntax_error General SQL syntax error in CREATE DATABASE statement
55006 object_in_use Template database has active connections
3D000 invalid_catalog_name Referencing a database that does not exist
42P01 undefined_table Non-existent tablespace or role referenced as OWNER

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)