PostgreSQL Duplicate Schema (SQLSTATE 42P06)

When you run CREATE SCHEMA with a schema name that already exists in the current database, PostgreSQL raises ERROR: schema "<name>" already exists with SQLSTATE 42P06 and condition name duplicate_schema. The statement is aborted and, if executed inside a transaction, the transaction is placed in an error state.

What This Error Means

SQLSTATE 42P06 belongs to PostgreSQL's error class 42 — the "Syntax Error or Access Rule Violation" class. Within that class, the P06 suffix is a PostgreSQL-specific (non-standard) extension covering schema-level naming conflicts, as opposed to the SQL-standard 42P07 (duplicate_table) or 42710 (duplicate_object) which cover analogous problems for tables and other objects.

When PostgreSQL processes CREATE SCHEMA, it checks the pg_namespace system catalog for an existing row whose nspname matches the requested schema name. If a match is found, it raises 42P06 before performing any other work — no schema is created, no objects within a CREATE SCHEMA ... CREATE TABLE ... block are created, and any ongoing transaction is aborted.

After the error is raised, the current transaction (if any) enters the aborted state. You must issue ROLLBACK (or ROLLBACK TO SAVEPOINT) before executing further statements in that session. A connection that is not inside an explicit transaction simply has the individual statement rolled back with no further side effects.

Common Causes

1. Running a migration script more than once. A CREATE SCHEMA myapp; statement in a migration that gets re-executed — either because the migration tool lost track of which scripts have run, or because the script was run manually for troubleshooting — will fail on subsequent runs.

2. Missing IF NOT EXISTS guard. The developer wrote CREATE SCHEMA myschema without the idempotency guard, intending the script to be safe to re-run but forgetting to add IF NOT EXISTS.

3. Race condition in concurrent deployments. Two processes (e.g., two application instances starting simultaneously) each check that the schema does not exist, then both attempt to create it. The second one to execute the DDL receives 42P06.

4. Tenant-provisioning code runs twice. In multi-tenant systems that create a schema per tenant, a retry or a double-click on a "provision" button can trigger duplicate CREATE SCHEMA calls for the same tenant slug.

5. Restoring a dump into a non-empty database. pg_dump emits CREATE SCHEMA statements; restoring into a database that already contains those schemas (e.g., a partial earlier restore) will produce this error for every schema that already exists.

How to Fix duplicate_schema

1. Add IF NOT EXISTS to make the statement idempotent. This is the most common fix and the right choice for migration scripts and application startup code:

   CREATE SCHEMA IF NOT EXISTS myschema;
   

   The statement succeeds silently if the schema already exists. PostgreSQL 9.3 and later support this syntax.

2. Drop the existing schema before recreating it (use with caution). If you genuinely need a clean slate (e.g., in a test harness or CI reset script), drop the schema first:

   DROP SCHEMA IF EXISTS myschema CASCADE;
   CREATE SCHEMA myschema;
   

   CASCADE drops all objects contained in the schema. Never do this on production data without a backup.

3. Check for existence before creating (application-level guard). If you cannot change the DDL itself, query pg_namespace first:

   DO $$
   BEGIN
     IF NOT EXISTS (
       SELECT 1 FROM pg_namespace WHERE nspname = 'myschema'
     ) THEN
       CREATE SCHEMA myschema;
     END IF;
   END
   $$;
   

   Note: this pattern has a TOCTOU race in concurrent scenarios — prefer IF NOT EXISTS when possible.

4. Fix the migration tool's state tracking. If the error occurs because a migration script was applied twice, investigate why the migration framework (Flyway, Liquibase, Alembic, golang-migrate, etc.) re-ran the script. Repair the migration history table rather than silencing the error, so the root cause is addressed.

5. Handle the error in application code. When the duplicate schema creation is expected as a normal race condition (e.g., multi-tenant provisioning), catch 42P06 / duplicate_schema specifically and treat it as a non-fatal condition:

   from psycopg2 import errors
   
   try:
       cur.execute("CREATE SCHEMA tenant_42")
       conn.commit()
   except errors.DuplicateSchema:
       conn.rollback()
       # Schema already exists — safe to continue
   

Additional Information

• IF NOT EXISTS for CREATE SCHEMA was introduced in PostgreSQL 9.3. On older versions (now well past end-of-life), you must use the PL/pgSQL DO block workaround or catch the exception in application code.
• Related SQLSTATE codes in the same class:
  • 42P07 — duplicate_table: raised when CREATE TABLE names an already-existing table.
  • 42710 — duplicate_object: raised for duplicate sequences, types, indexes, etc.
  • 42P16 — invalid_table_definition: a different class-42 PostgreSQL extension for structural DDL problems.
• Most PostgreSQL drivers expose this as a typed exception. In psycopg2 it is psycopg2.errors.DuplicateSchema; in psycopg3 (psycopg) it is psycopg.errors.DuplicateSchema; in asyncpg check asyncpg.DuplicateSchemaError; in JDBC inspect SQLState on the SQLException.
• ORMs that manage schema namespacing (e.g., Django with db_schema, or multi-schema Alembic setups) may surface this as a generic django.db.utils.ProgrammingError or sqlalchemy.exc.ProgrammingError — inspect the wrapped pgcode attribute to confirm it is 42P06.
• This error has no performance implications — it is a fast catalog lookup that fails early. The cost concern is the aborted transaction and any retry logic around it.

Frequently Asked Questions

Why does my migration fail with 42P06 even though I only ran it once? Check whether your migration tool is using a case-sensitive comparison for schema names. PostgreSQL folds unquoted identifiers to lowercase, so CREATE SCHEMA MySchema and CREATE SCHEMA myschema refer to the same schema. If your migration history recorded MySchema but the schema was already created as myschema, the framework may consider it a new migration while PostgreSQL sees a duplicate.

Is CREATE SCHEMA IF NOT EXISTS safe to use in production? Yes. When the schema already exists, the statement is a no-op — it does not modify the schema, its objects, or its owner. It is the standard idiomatic way to write re-runnable DDL in PostgreSQL 9.3+.

My pg_restore is failing with 42P06 — how do I handle it? Pass --clean to pg_restore to emit DROP SCHEMA statements before each CREATE SCHEMA, or restore into a freshly created (empty) database. Alternatively, use --if-exists along with --clean to avoid errors when the object does not exist in the target.

Does 42P06 roll back my entire transaction? Yes. If the CREATE SCHEMA that triggers 42P06 is inside an explicit transaction block (BEGIN / START TRANSACTION), PostgreSQL marks the whole transaction as aborted. All subsequent statements in that transaction will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until you issue ROLLBACK. Use a SAVEPOINT before the CREATE SCHEMA if you want to recover without rolling back the entire transaction.