PostgreSQL Duplicate Database (SQLSTATE 42P04)

When you run CREATE DATABASE and a database with that name already exists, PostgreSQL raises:

ERROR:  database "mydb" already exists

The SQLSTATE code is 42P04 and the condition name is duplicate_database. This is a non-retriable error that immediately aborts the statement.

What This Error Means

SQLSTATE 42P04 belongs to PostgreSQL's error class 42 — Syntax Error or Access Rule Violation. The P in the code signals that this is a PostgreSQL-specific condition (not part of the SQL standard), and 04 identifies the specific condition within that class. Sibling conditions in class 42 include 42P07 (duplicate_table) and 42710 (duplicate_object), all of which follow the same pattern of rejecting the creation of an already-existing object.

PostgreSQL checks the system catalog (pg_database) for a matching database name before attempting any file-system work. The check is case-sensitive: mydb and MyDB are treated as different databases. If the name already exists, the error is raised immediately and no new database is created. The connection remains open and fully usable after this error — only the CREATE DATABASE statement itself is aborted.

CREATE DATABASE cannot be issued inside a transaction block. This means there is no surrounding BEGIN/COMMIT to roll back, and the error has no transactional side effects. If you issued it from a client that opens an implicit transaction, the client connection state is unaffected.

Common Causes

1. Running a migration or initialization script more than once. Setup scripts that include CREATE DATABASE are often re-run during deploys, container restarts, or CI pipelines. The first run succeeds; subsequent runs hit 42P04 because the database already exists.

2. Parallel or concurrent provisioning. Two processes or workers both check whether a database exists, both observe it is absent, and both issue CREATE DATABASE. The second one to execute gets 42P04.

3. Case mismatch or environment confusion. A script creates production_db in one environment; later, another script tries to create Production_DB assuming it is distinct. Because PostgreSQL stores unquoted identifiers in lowercase, the names are the same and the second CREATE DATABASE fails.

4. Incomplete cleanup after a previous failure. A database from a prior run was never dropped, so the next attempt to create it fresh raises 42P04.

How to Fix duplicate_database

1. Use IF NOT EXISTS (PostgreSQL 9.3+). The simplest fix for idempotent scripts is to add IF NOT EXISTS, which silently skips creation when the database already exists:

   CREATE DATABASE mydb IF NOT EXISTS;
   

   Note that IF NOT EXISTS for CREATE DATABASE was added in PostgreSQL 9.3. On older versions you must use a conditional approach (see below).

2. Check before creating in a shell script. When driving database setup from a shell script, query pg_database before issuing CREATE DATABASE:

   psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname = 'mydb'" \
     | grep -q 1 || psql -U postgres -c "CREATE DATABASE mydb"
   

   This pattern is safe for single-process setup scripts. For concurrent scenarios, prefer IF NOT EXISTS or handle the error in application code.

3. Handle the exception in PL/pgSQL. When creating a database programmatically from a function or procedure (via dblink or pg_execute), catch the exception:

   DO $$
   BEGIN
     PERFORM dblink_exec(
       'dbname=postgres',
       'CREATE DATABASE mydb'
     );
   EXCEPTION
     WHEN duplicate_database THEN
       -- already exists, nothing to do
       NULL;
   END;
   $$;
   

4. Drop and recreate for test/CI environments. If the database should always start fresh (e.g., in CI), drop it first:

   DROP DATABASE IF EXISTS mydb;
   CREATE DATABASE mydb;
   

   Never use this pattern in production without a deliberate data-loss decision.

5. Catch the error in application code. For application-level database provisioning logic, catch SQLSTATE 42P04 and treat it as a no-op:

   import psycopg2
   from psycopg2 import errors
   
   conn = psycopg2.connect("dbname=postgres user=postgres")
   conn.autocommit = True
   try:
       conn.cursor().execute("CREATE DATABASE mydb")
   except errors.DuplicateDatabase:
       pass  # already exists
   

   Note that CREATE DATABASE requires autocommit = True in psycopg2 because it cannot run inside a transaction block.

Additional Information

• IF NOT EXISTS for CREATE DATABASE was introduced in PostgreSQL 9.3. It is available in all currently supported PostgreSQL versions (12+).
• Related SQLSTATE codes in class 42: 42P07 (duplicate_table), 42710 (duplicate_object), 42701 (duplicate_column), 42723 (duplicate_function).
• Most PostgreSQL drivers expose this as a named exception. In psycopg2 it is psycopg2.errors.DuplicateDatabase; in asyncpg it is asyncpg.DuplicateDatabaseError; in JDBC it maps to PSQLException with SQLState 42P04.
• ORMs like SQLAlchemy do not abstract CREATE DATABASE — you must execute it directly with a raw connection set to autocommit mode. Django's createdb management command handles the check internally and will not surface this error under normal usage.

Frequently Asked Questions

Why does CREATE DATABASE IF NOT EXISTS sometimes still raise an error? On PostgreSQL versions older than 9.3, IF NOT EXISTS is not supported for CREATE DATABASE and will itself cause a syntax error. If you are on a supported version (9.3+) and still see issues, verify you have the correct syntax — IF NOT EXISTS must appear after the database name, not before it.

Can I run CREATE DATABASE inside a transaction block? No. PostgreSQL requires CREATE DATABASE to run outside any transaction block. If you issue it inside a BEGIN/COMMIT, you will receive ERROR: CREATE DATABASE cannot run inside a transaction block. Use autocommit mode or run the statement in a fresh connection.

Does 42P04 mean my database data was lost? No. The error means the CREATE DATABASE command was rejected because the database already exists and is intact. Nothing was created, modified, or deleted. The existing database is unaffected.

Is the name check case-sensitive? Unquoted database names are folded to lowercase before the lookup, so CREATE DATABASE MyDB and CREATE DATABASE mydb refer to the same name. If you quoted the name at creation time (e.g., CREATE DATABASE "MyDB"), then the exact case is preserved and CREATE DATABASE mydb would create a separate database.