duplicate_table relation “…” already exists — 42P07

Symptoms

A CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, or SELECT … INTO tried to create an object whose name already exists in the target schema. PostgreSQL reports SQLSTATE 42P07 (duplicate_table) and rolls the statement back.

• Migrations fail on re-run when they are not idempotent.
• The name clash is within a single schema; the same name can exist in another schema.
• Nothing is altered — the existing object is left untouched.

What the server log shows

ERROR:  relation "orders" already exists

Why PostgreSQL raises this — what the manual says

the CREATE TABLE reference (IF NOT EXISTS):

“Do not throw an error if a relation with the same name already exists.”

Relation names must be unique within a schema (they share one namespace across tables, views, sequences, indexes, and foreign tables). Creating a second object with an existing name violates that uniqueness and raises 42P07.

Common causes

• Re-running a migration that already applied.
• Two migrations creating the same object name.
• A leftover object from a failed/partial deploy.
• A name collision between a table and a view or sequence.

How to fix it

1. Make creation idempotent: CREATE TABLE IF NOT EXISTS orders (…);.
2. If you intend to replace it, drop first inside a transaction: DROP TABLE IF EXISTS orders; CREATE TABLE orders (…);.
3. Rename the new object if both must coexist, or create it in a different schema.
4. Track applied migrations so they are not executed twice.

Diagnostic query

-- What kind of object currently owns this name, and in which schema?
SELECT n.nspname AS schema, c.relname, c.relkind
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'orders';

Related & next steps

• SQLSTATE 42P07 — Duplicate Table (full reference)
• Message: relation “…” does not exist
• Glossary: Schema

Reference: PostgreSQL 18 — CREATE TABLE.