// PostgreSQL error reference

PostgreSQL 42P07: relation already exists

CREATE TABLE, INDEX, VIEW or SEQUENCE used a name that a table-like object already has in that schema.

SQLSTATE
42P07
Name
duplicate_table
Category
Syntax Error or Access Rule Violation
Error message
relation "orders" already exists

What it means

Tables, views, materialized views, sequences, indexes and foreign tables share one namespace per schema. Creating any of them with a name that is already taken in the schema fails with 42P07, even if the existing object is a different kind (an index named like a table, for example).

Re-running a migration or setup script is the usual trigger.

Common causes

  • A setup or migration script that ran before, partly or fully.
  • An index with the same name as an existing table, view or index in the schema.
  • Two migrations creating the same object.
  • A temporary table left over in the same session.

How to fix it

  1. Make the script idempotent with IF NOT EXISTS.

    CREATE TABLE IF NOT EXISTS orders (id bigint PRIMARY KEY);
    CREATE INDEX IF NOT EXISTS orders_created_at_idx ON orders (created_at);
  2. See what currently has the name.

    SELECT n.nspname, c.relname, c.relkind
    FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relname = 'orders';
  3. If the old object should be replaced, drop it first, after checking nothing depends on it.

    DROP TABLE IF EXISTS orders_old;
    CREATE OR REPLACE VIEW order_summary AS SELECT 1 AS n;  -- views can be replaced in place

Reproduce it safely

CREATE TEMP TABLE t (id int);
CREATE TEMP TABLE t (id int);  -- ERROR: relation "t" already exists

Related errors

More syntax error or access rule violation errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.