PostgreSQL 42P07 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42P07: duplicate_table

PostgreSQL error code 42P07, duplicate_table, is raised when you attempt to create a table using a name that already exists within the same schema. This is a common error encountered during database migrations, repeated deployment scripts, or ORM auto-creation logic. Fortunately, it is straightforward to fix and even easier to prevent with proper scripting habits.

---

Top 3 Causes

1. Running Migration Scripts More Than Once

The most frequent cause is executing a CREATE TABLE statement in a script that has already been run successfully. Without idempotency guards, the second run will always fail with 42P07.

-- This will fail on the second run
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    total NUMERIC(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ERROR: relation "orders" already exists (42P07)

2. Table Name Collision Between Developers or Branches

In team environments, two developers may independently create tables with the same name in the same schema without coordination. This is especially common when feature branches are merged without proper schema conflict reviews.

-- Developer A already created this table
CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    message TEXT NOT NULL
);

-- Developer B runs this on the same DB instance
CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    sent_at TIMESTAMPTZ
);
-- ERROR: relation "notifications" already exists (42P07)

3. ORM Auto-DDL Configuration Conflicts

Frameworks like Django (syncdb/migrate), Hibernate (create DDL mode), or SQLAlchemy (create_all) can attempt to create tables automatically on application startup. If those tables already exist in the target database, error 42P07 is triggered.

-- Simulating what an ORM might generate automatically
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    bio TEXT
);
-- Fails if the table already exists in the target environment

---

Quick Fix Solutions

Use IF NOT EXISTS (Recommended)

The cleanest and most idiomatic fix in PostgreSQL is to add IF NOT EXISTS to your CREATE TABLE statement. The command simply does nothing if the table already exists, with no error raised.

-- Safe, idempotent table creation
CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    total NUMERIC(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Use Exception Handling in PL/pgSQL

When you need finer control over the error and want to log or branch logic around it, use a DO block with exception handling.

DO $$
BEGIN
    CREATE TABLE notifications (
        id SERIAL PRIMARY KEY,
        message TEXT NOT NULL,
        created_at TIMESTAMPTZ DEFAULT NOW()
    );
    RAISE NOTICE 'Table notifications created successfully.';
EXCEPTION
    WHEN duplicate_table THEN
        RAISE NOTICE 'Table already exists, skipping creation.';
END
$$;

Drop and Recreate (Development Only)

In development or test environments where data loss is acceptable, you can drop the existing table first. Never use this in production.

-- Development/test environments only
DROP TABLE IF EXISTS notifications CASCADE;

CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    message TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

---

Prevention Tips

1. Always write idempotent DDL scripts.
Every CREATE TABLE in your migration or deployment scripts should use IF NOT EXISTS. Apply the same principle to indexes and constraints. This ensures your scripts can be safely re-run in any environment without failure.

-- Full idempotent migration example
CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGSERIAL PRIMARY KEY,
    event_type TEXT NOT NULL,
    occurred_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_audit_logs_event_type
    ON audit_logs(event_type);

2. Adopt a database migration tool.
Use tools like Flyway or Liquibase to version-control your schema changes. These tools track which migrations have been applied and prevent duplicate execution at the system level, eliminating the root cause of 42P07 in automated pipelines.

---

Related Errors

• 42P01 undefined_table — The opposite of 42P07; raised when referencing a table that does not exist.
• 42701 duplicate_column — Raised when a column name is duplicated within a CREATE TABLE or ALTER TABLE statement.
• 42P06 duplicate_schema — Same concept at the schema level; preventable with CREATE SCHEMA IF NOT EXISTS.

---

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