PostgreSQL Duplicate Table (SQLSTATE 42P07)

When you attempt to create a table using a name already in use within the same schema, PostgreSQL raises ERROR: relation "<name>" already exists with SQLSTATE 42P07 and condition name duplicate_table. This error belongs to error class 42 — Syntax Error or Access Rule Violation — and is raised synchronously at DDL execution time before any data is touched.

What This Error Means

SQLSTATE class 42 covers syntax and access rule violations in PostgreSQL. The specific code 42P07 is a PostgreSQL-specific extension (the P prefix denotes a vendor-defined code beyond the SQL standard). It is raised exclusively by CREATE TABLE when the target relation name is already taken by an existing table, view, materialized view, foreign table, sequence, or partitioned table within the same schema — PostgreSQL tracks all of these under a unified namespace for relations.

The error is raised during parse/analysis, before the statement executes any writes. If the CREATE TABLE statement is part of a larger transaction that has already performed other work, that work is unaffected by this error, but the error itself aborts the current transaction block. Any subsequent statements in the same transaction will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until you issue a ROLLBACK.

The full error text looks like:

ERROR:  relation "users" already exists
SQLSTATE: 42P07

The term "relation" rather than "table" in the message is intentional — PostgreSQL uses "relation" for any object stored in pg_class, which includes tables, views, indexes, sequences, and more.

Common Causes

1. Running a migration script more than once. A CREATE TABLE statement without IF NOT EXISTS will fail on the second run. This is especially common in ad-hoc or manually applied migration scripts where idempotency was not considered.

2. Schema mismatch between environments. A table was created manually in the target database but not reflected in the migration history, causing the migration tool to attempt to re-create it.

3. Concurrent or partial migrations. A migration partially applied before a failure left the table in place; re-running the migration hits the already-created table.

4. ORM or framework initialization. Some ORMs run CREATE TABLE at startup without checking for existing tables (or with checkfirst=False), causing the error on every restart against an already-initialized database.

5. Temporary table name collision. Creating a temporary table (CREATE TEMP TABLE) using a name that was already created in the same session within pg_temp.

How to Fix duplicate_table

1. Use IF NOT EXISTS for idempotent DDL. This is the safest general fix for migration scripts and initialization code:

   CREATE TABLE IF NOT EXISTS users (
       id   BIGSERIAL PRIMARY KEY,
       email TEXT NOT NULL UNIQUE
   );
   

   With IF NOT EXISTS, PostgreSQL skips creation silently when the relation already exists, returning a notice rather than an error. Note that it does not validate that the existing table's structure matches the CREATE TABLE definition.

2. Drop the existing table before re-creating it. Use this only in development or when you are certain the data can be discarded:

   DROP TABLE IF EXISTS users;
   CREATE TABLE users (
       id   BIGSERIAL PRIMARY KEY,
       email TEXT NOT NULL UNIQUE
   );
   

3. Check whether the table exists before issuing DDL. In plpgsql or application code, query pg_catalog.pg_tables or information_schema.tables:

   DO $$
   BEGIN
       IF NOT EXISTS (
           SELECT 1 FROM pg_tables
           WHERE schemaname = 'public' AND tablename = 'users'
       ) THEN
           CREATE TABLE users (
               id    BIGSERIAL PRIMARY KEY,
               email TEXT NOT NULL UNIQUE
           );
       END IF;
   END $$;
   

4. Fix migration tooling state. If using a migration tool (Flyway, Liquibase, Alembic, golang-migrate), check whether the migration has already been recorded in the tool's history table but the DDL was re-executed outside the tool. Mark the migration as applied in the history table rather than re-running it, or use the tool's repair/resolve commands.

5. For ORM-managed schemas, enable checkfirst or equivalent. In SQLAlchemy, for example, Base.metadata.create_all(engine, checkfirst=True) issues CREATE TABLE IF NOT EXISTS instead of a bare CREATE TABLE.

Additional Information

• 42P07 has been part of PostgreSQL since at least version 7.4 and the behavior has not changed across versions.
• IF NOT EXISTS for CREATE TABLE was introduced in PostgreSQL 9.1.
• Related SQLSTATE codes in class 42 include 42701 (duplicate_column), 42710 (duplicate_object), 42723 (duplicate_function), and 42P06 (duplicate_schema).
• Most PostgreSQL drivers surface this as a ProgrammingError (psycopg2/psycopg3, asyncpg) or StatementError with the SQLSTATE code accessible on the exception object.
• Because this error aborts the current transaction, application code that catches 42P07 must issue a ROLLBACK (or use a savepoint) before attempting any further database work on the same connection.

Frequently Asked Questions

Does IF NOT EXISTS verify the existing table matches my column definitions? No. PostgreSQL only checks whether a relation with that name exists in the schema. If the existing table has a different structure, IF NOT EXISTS silently succeeds without modifying the table or raising an error. To alter an existing table's structure you must use ALTER TABLE.

Why does the error say "relation" instead of "table"? PostgreSQL stores tables, views, sequences, materialized views, indexes, and foreign tables all in pg_class using the umbrella term "relation". The error message reflects this internal terminology. A CREATE TABLE statement will also fail with 42P07 if the name is taken by a view or materialized view, not just another table.

Can this error occur with temporary tables? Yes. If you run CREATE TEMP TABLE foo (...) twice in the same session without dropping it first, you will get 42P07 because the temp table already exists in the session's pg_temp schema. Use CREATE TEMP TABLE IF NOT EXISTS or DROP TABLE IF EXISTS to avoid this.

My migration ran successfully before but now fails with 42P07 — what happened? The most common cause is that someone applied the migration manually (e.g., via psql) without recording it in the migration tool's history table. The tool then sees the migration as pending and tries to run it again. Check your migration history table and mark the migration as applied, or use the tool's repair command.