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.
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.
Modify your schema creation statement to only create the schema if it does not already exist. This is the safest and most idempotent approach:
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.
If you need to conditionally run code based on schema existence, query the information_schema:
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.
If restoring a pg_dump backup, ensure you're restoring to a clean state:
dropdb -U postgres mydb
creatdb -U postgres -T template0 -E utf8 -O myuser mydb
pg_restore -U postgres -d mydb backup.dumpAlternatively, use pg_restore with the --clean flag to drop objects before recreating them:
pg_restore --clean -U postgres -d mydb backup.dumpList all existing schemas to verify what's already present:
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.
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.
0LP01: invalid_grant_operation
How to fix "Invalid grant operation" (0LP01) in PostgreSQL
aggregate functions are not allowed in WHERE clause
How to fix "aggregate functions are not allowed in WHERE clause" in PostgreSQL
2200L: not_an_xml_document
How to fix "2200L: not_an_xml_document" in PostgreSQL
ERROR: ambiguous_parameter
42P08: Ambiguous parameter error
2201F: invalid_argument_for_power_function
Invalid argument for power function (2201F)