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
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
-
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);
-
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';
-
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
42P01relation does not exist42710duplicate object42P06schema already exists42P04database already exists2714SQL Server: There is already an object named1050MySQL: Table already existsORA-00955Oracle: name is already used by an existing object
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42846cannot coerce42803column must appear in the GROUP BY clause42830invalid foreign key42804datatype mismatch42P18could not determine data type of parameter42703column does not exist
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.
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.