How to fix Duplicate schema in PostgreSQL

PostgreSQLBEGINNERMEDIUMUpdated Dec 27, 2025· 138 views

PostgreSQL raises a duplicate schema error when you try to create a schema that already exists. Use CREATE SCHEMA IF NOT EXISTS or check if the schema is already present before creating it.

Quick fix

Fixes 42P06

CREATE SCHEMA IF NOT EXISTS myschema;
  1. Use CREATE SCHEMA IF NOT EXISTS
  2. Check if schema exists before creation
  3. Drop schema before restoring backups
  4. Check current schemas in your database
postgresql — error
CREATE SCHEMA IF NOT EXISTS myschema;
Duplicate schema

What this error means

The "duplicate schema" error (SQLSTATE 42P06) occurs when PostgreSQL rejects your attempt to create a schema because a schema with that name already exists in the database. This commonly happens during database migrations, restores, or when deploying code that assumes a fresh database state. The error is PostgreSQL's way of enforcing schema name uniqueness—each schema in a database must have a unique identifier.

Typical symptoms

  • Migration scripts fail with ERROR: schema "schemaname" already exists
  • Database restore operations fail when pg_restore encounters existing schemas
  • Deployment pipelines error on schema creation in idempotent scripts
  • Version control systems show duplicate schema creation commands

Common causes

  1. Using CREATE SCHEMA without IF NOT EXISTS in migrations that may run multiple times
  2. Restoring a backup to a database that already contains the target schema
  3. Running migration scripts twice or during concurrent deployments
  4. Switching database versions or tools (e.g., pg_dump from PostgreSQL v10 to v11) with different schema handling
  5. Multiple applications or deployment processes trying to create the same schema simultaneously

How to fix "Duplicate schema"

1Use CREATE SCHEMA IF NOT EXISTS

Modify your schema creation statement to only create the schema if it does not already exist. This is the safest and most idempotent approach:

sql
CREATE SCHEMA IF NOT EXISTS myschema;

This prevents the error by silently skipping creation if the schema already exists, making your migrations replayable without errors.

2Check if schema exists before creation

If you need to conditionally run code based on schema existence, query the information_schema:

sql
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'myschema') THEN
    CREATE SCHEMA myschema;
  END IF;
END $$;

This approach allows you to perform additional logic only when the schema doesn't exist.

3Drop schema before restoring backups

If restoring a pg_dump backup, ensure you're restoring to a clean state:

bash
dropdb -U postgres mydb
creatdb -U postgres -T template0 -E utf8 -O myuser mydb
pg_restore -U postgres -d mydb backup.dump

Alternatively, use pg_restore with the --clean flag to drop objects before recreating them:

bash
pg_restore --clean -U postgres -d mydb backup.dump
4Check current schemas in your database

List all existing schemas to verify what's already present:

sql
SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%';

This helps you identify naming conflicts and existing schemas before attempting creation. The filter excludes PostgreSQL system schemas.

Advanced notes

The public schema is treated specially by PostgreSQL and pg_dump; it is created automatically when a database is initialized. Never attempt to drop and recreate the public schema—instead, use CREATE SCHEMA IF NOT EXISTS to handle idempotency. When working with tools like ORM migration systems (Prisma, Sequelize, Alembic), ensure you use their built-in migration framework rather than raw SQL, as they typically handle schema existence checks automatically. For parallel deployments or CI/CD scenarios, consider using PostgreSQL advisory locks to serialize schema creation across multiple processes.

Frequently asked questions

What does "42P06" mean?
The "duplicate schema" error (SQLSTATE 42P06) occurs when PostgreSQL rejects your attempt to create a schema because a schema with that name already exists in the database. This commonly happens during database migrations, restores, or when deploying code that assumes a fresh database state. The error is PostgreSQL's way of enforcing schema name uniqueness—each schema in a database must have a unique identifier.
How do I fix "42P06"?
PostgreSQL raises a duplicate schema error when you try to create a schema that already exists. Use CREATE SCHEMA IF NOT EXISTS or check if the schema is already present before creating it.
What causes "42P06"?
Using CREATE SCHEMA without IF NOT EXISTS in migrations that may run multiple times Restoring a backup to a database that already contains the target schema Running migration scripts twice or during concurrent deployments
Is the "42P06" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading