DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 42P04 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P04: duplicate_database

PostgreSQL error code 42P04 (duplicate_database) is raised when you attempt to create a database that already exists within the same PostgreSQL cluster. This is a safety mechanism to prevent accidental overwriting of existing databases, but it can be a common pain point in automated deployment pipelines and migration scripts that lack idempotency.

Top 3 Causes

1. Non-Idempotent Initialization Scripts

The most frequent cause is running a CREATE DATABASE command more than once — typically in CI/CD pipelines or retry logic — without checking whether the database already exists.

-- This will throw 42P04 if 'mydb' already exists
CREATE DATABASE mydb;
Enter fullscreen mode Exit fullscreen mode

Quick Fix: Use a conditional check via pg_database:

-- Safe, idempotent approach using \gexec
SELECT 'CREATE DATABASE mydb'
WHERE NOT EXISTS (
  SELECT 1 FROM pg_database WHERE datname = 'mydb'
)\gexec
Enter fullscreen mode Exit fullscreen mode

Or use a DO block for inline scripting:

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT FROM pg_database WHERE datname = 'mydb'
  ) THEN
    PERFORM dblink_exec('dbname=postgres', 'CREATE DATABASE mydb');
  END IF;
END
$$;
Enter fullscreen mode Exit fullscreen mode

2. Case Sensitivity Confusion

PostgreSQL folds unquoted identifiers to lowercase, so MyDB, MYDB, and mydb are treated as the same name. Developers unaware of this behavior may attempt to create what they think is a new database, triggering 42P04.

-- All three refer to the same database name internally
CREATE DATABASE MyDB;   -- stored as 'mydb'
CREATE DATABASE mydb;   -- ERROR: 42P04 duplicate_database
CREATE DATABASE "MyDB"; -- This is different (case-sensitive with quotes)
Enter fullscreen mode Exit fullscreen mode

Quick Fix: Always verify existing databases before creation:

-- Audit existing databases
SELECT datname,
       pg_encoding_to_char(encoding) AS encoding,
       datcollate
FROM pg_database
WHERE datistemplate = false
ORDER BY datname;
Enter fullscreen mode Exit fullscreen mode

3. Restore or Migration Conflicts

When using pg_restore -C or createdb during database migration, error 42P04 occurs if the target database name already exists on the destination server.

-- Check before restoring
SELECT datname FROM pg_database WHERE datname = 'myapp_production';

-- Safely rename the conflicting database before restore
ALTER DATABASE myapp_production
  RENAME TO myapp_production_bak_20240101;
Enter fullscreen mode Exit fullscreen mode

Then proceed with your restore:

-- Terminate active connections before any destructive operation
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'myapp_production'
  AND pid <> pg_backend_pid();

DROP DATABASE IF EXISTS myapp_production;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Situation Solution
Script runs twice Use WHERE NOT EXISTS + \gexec
Name confusion Query pg_database first
Restore conflict RENAME or DROP old DB first

Prevention Tips

1. Always write idempotent database provisioning scripts.
Never use a bare CREATE DATABASE in any automated context. Wrap it in an existence check using pg_database, or use infrastructure tools like Terraform's postgresql_db resource or Ansible's postgresql_db module, which handle idempotency natively.

-- Always prefer this pattern in scripts
SELECT 'CREATE DATABASE myapp'
WHERE NOT EXISTS (
  SELECT 1 FROM pg_database WHERE datname = 'myapp'
)\gexec
Enter fullscreen mode Exit fullscreen mode

2. Enforce a naming registry and convention.
Define a clear naming convention such as {env}_{service}_{purpose} (e.g., prod_payments_main) and maintain a central registry of all databases. Periodically audit your cluster against the registry:

-- Regular audit query
SELECT datname, pg_get_userbyid(datdba) AS owner, datcollate
FROM pg_database
WHERE datistemplate = false
ORDER BY datname;
Enter fullscreen mode Exit fullscreen mode

Related Error Codes

  • 42P07 duplicate_table — Same concept, but for tables. Use CREATE TABLE IF NOT EXISTS.
  • 42P06 duplicate_schema — Duplicate schema creation. Use CREATE SCHEMA IF NOT EXISTS.
  • 42P05 duplicate_cursor — Duplicate cursor name within the same transaction.
  • 23505 unique_violation — Data-level duplicate, conceptually related to the duplicate error family.

📖 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)