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 in the current PostgreSQL cluster. Unlike some other object types, CREATE DATABASE does not natively support an IF NOT EXISTS clause in all contexts, making this error particularly common in automated deployment scripts and CI/CD pipelines.

Top 3 Causes

1. Running CREATE DATABASE Without an Existence Check

The most frequent cause is executing CREATE DATABASE unconditionally in setup or migration scripts. When the script runs a second time (e.g., a retry or re-deployment), the database already exists and PostgreSQL throws 42P04.

-- ❌ This will fail if 'mydb' already exists
CREATE DATABASE mydb;

-- ERROR:  42P04: database "mydb" already exists

-- ✅ Check pg_database catalog before creating
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb') THEN
        RAISE NOTICE 'Proceed to create database outside this block.';
    ELSE
        RAISE NOTICE 'Database already exists. Skipping.';
    END IF;
END
$$;

# ✅ Shell script with idempotent guard
DB_NAME="mydb"
EXISTS=$(psql -U postgres -tAc "SELECT 1 FROM pg_database WHERE datname='$DB_NAME'")

if [ "$EXISTS" != "1" ]; then
    psql -U postgres -c "CREATE DATABASE $DB_NAME ENCODING 'UTF8' TEMPLATE template0;"
    echo "Created database: $DB_NAME"
else
    echo "Database $DB_NAME already exists. Skipped."
fi

2. Race Condition in Parallel Deployments

In high-concurrency environments, two or more processes may attempt to create the same database at nearly the same time. The first one succeeds; all subsequent ones receive 42P04. This is intermittent and tricky to reproduce.

-- ✅ Use pg_try_advisory_lock to serialize database creation
DO $$
DECLARE
    lock_ok BOOLEAN;
BEGIN
    SELECT pg_try_advisory_lock(99999) INTO lock_ok;

    IF lock_ok THEN
        IF NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb') THEN
            RAISE NOTICE 'Safe to create database now.';
            -- Run CREATE DATABASE from outside (shell/app layer)
        END IF;
        PERFORM pg_advisory_unlock(99999);
    ELSE
        RAISE EXCEPTION 'Another process is creating the database. Retry later.';
    END IF;
END
$$;

3. Name Collision in Shared PostgreSQL Clusters

When multiple teams or projects share a single PostgreSQL cluster and use generic database names like testdb, app, or main, name collisions are inevitable. Without a naming convention, this error can appear unexpectedly during onboarding of new services.

-- ✅ List all existing databases to spot conflicts
SELECT
    datname        AS database_name,
    pg_get_userbyid(datdba) AS owner,
    pg_encoding_to_char(encoding) AS encoding,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datistemplate = false
ORDER BY datname;

-- ✅ Quick existence check
SELECT EXISTS (
    SELECT 1 FROM pg_database WHERE datname = 'mydb'
) AS already_exists;

Quick Fix Solutions

If the duplicate database is safe to remove, terminate active connections first and then drop it:

-- Step 1: Terminate all connections to the target database
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb'
  AND pid <> pg_backend_pid();

-- Step 2: Drop the database
DROP DATABASE IF EXISTS mydb;

-- Step 3: Recreate it cleanly
CREATE DATABASE mydb
    OWNER = myuser
    ENCODING = 'UTF8'
    TEMPLATE = template0;

⚠️ Warning: Never drop a production database without a verified backup.

Prevention Tips

1. Write idempotent scripts. Always guard your CREATE DATABASE calls with a check against pg_database. Any initialization script should be safe to run multiple times without side effects. Tools like Ansible (postgresql_db module) and Terraform (postgresql_database resource) handle this automatically.

2. Enforce a naming convention and restrict permissions. Adopt a structured naming pattern such as {team}_{project}_{env} (e.g., platform_auth_prod, data_pipeline_staging) and grant CREATEDB only to authorized roles:

-- Grant CREATEDB only to a designated deployment role
ALTER ROLE deploy_bot CREATEDB;

-- Revoke from general developer accounts
ALTER ROLE developer NOCREATEDB;

-- Audit who has CREATEDB privilege
SELECT rolname, rolcreatedb
FROM pg_roles
WHERE rolcreatedb = true
ORDER BY rolname;

Related Errors

Code	Name	Description
`42P06`	`duplicate_schema`	Schema already exists; use `CREATE SCHEMA IF NOT EXISTS`
`42P07`	`duplicate_table`	Table already exists; use `CREATE TABLE IF NOT EXISTS`
`55006`	`object_in_use`	Active connections prevent `DROP DATABASE`

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

DEV Community