PostgreSQL 42P06 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42P06: duplicate_schema

PostgreSQL error code 42P06 (duplicate_schema) is raised when you attempt to execute a CREATE SCHEMA statement for a schema name that already exists in the current database. Unlike some databases that silently ignore the duplicate, PostgreSQL enforces strict uniqueness on schema names and immediately aborts the statement unless you explicitly handle the case. This error belongs to the SQL error class 42 (Syntax Error or Access Rule Violation) and is one of the most common DDL-related errors in automated deployment pipelines.

---

Top 3 Causes

1. Missing IF NOT EXISTS in DDL Scripts

The most frequent cause is simply omitting the IF NOT EXISTS guard when writing CREATE SCHEMA statements. This is especially problematic in shared initialization scripts used across multiple environments where a schema may already exist.

-- ❌ Fails if schema already exists
CREATE SCHEMA analytics;

-- ✅ Safe in all environments
CREATE SCHEMA IF NOT EXISTS analytics;

2. Migration Scripts Executed More Than Once

In CI/CD pipelines, migration scripts that are not idempotent can be re-executed — for example, when migration history tables (used by tools like Flyway or Liquibase) are accidentally reset or corrupted. When the same CREATE SCHEMA runs twice, the second run triggers 42P06.

-- Manually verify if a schema already exists before running migrations
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name = 'analytics';

-- Use a DO block for conditional creation (useful in plain SQL migration files)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_namespace WHERE nspname = 'analytics'
    ) THEN
        CREATE SCHEMA analytics;
    END IF;
END
$$;

3. Race Conditions in Multi-Tenant Schema Provisioning

In schema-per-tenant architectures, two concurrent requests for the same new tenant can both attempt to create the same schema simultaneously. One transaction succeeds; the other throws 42P06.

-- Safe tenant schema creation using Advisory Locks
CREATE OR REPLACE FUNCTION provision_tenant_schema(p_tenant TEXT)
RETURNS VOID AS $$
DECLARE
    v_schema TEXT := 'tenant_' || p_tenant;
BEGIN
    PERFORM pg_advisory_lock(hashtext(v_schema));
    BEGIN
        EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', v_schema);
    EXCEPTION
        WHEN duplicate_schema THEN
            RAISE NOTICE 'Schema % already exists, skipping.', v_schema;
    END;
    PERFORM pg_advisory_unlock(hashtext(v_schema));
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT provision_tenant_schema('acme');

---

Quick Fix Solutions

Add IF NOT EXISTS to your CREATE SCHEMA statement — this is the fastest and safest fix in most situations:

CREATE SCHEMA IF NOT EXISTS my_schema;

If you need explicit error handling inside PL/pgSQL, catch the duplicate_schema exception by name:

DO $$
BEGIN
    CREATE SCHEMA my_schema;
EXCEPTION
    WHEN duplicate_schema THEN  -- SQLSTATE '42P06'
        RAISE NOTICE 'Schema already exists. No action taken.';
END;
$$;

---

Prevention Tips

1. Enforce IF NOT EXISTS as a team standard. Add a linting rule in your CI pipeline to flag any CREATE SCHEMA statement that omits IF NOT EXISTS. This single convention eliminates the vast majority of 42P06 occurrences.

-- Standard schema bootstrap template
CREATE SCHEMA IF NOT EXISTS app_core;
CREATE SCHEMA IF NOT EXISTS app_audit;
CREATE SCHEMA IF NOT EXISTS app_reporting;

1. Never manually modify migration history tables. If you use Flyway or Liquibase, always use the tool's own repair commands (flyway repair, liquibase clearCheckSums) rather than manually editing or deleting rows from the history table. Always validate migration state before deployment with flyway validate or liquibase status.

---

Related Errors

Code	Name	Description
42P07	duplicate_table	CREATE TABLE on an already existing table
42710	duplicate_object	Duplicate index, sequence, or type
42P04	duplicate_database	CREATE DATABASE with an existing database name

---

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