ErrorBase
Independent technical referenceOfficial site
  • Software and digital services
  • Databases

42P07

PostgreSQL 42P07 database error: relation already exists

Verified with sources2 sourcesReviewed Oct 19, 2026
Technician coming? See the 2 steps they must show you
Trace

Evidence linked to this decision

View source: PostgreSQL 18 Appendix A: Error Codes

Risk level

Medium

Proceed carefully and stop if the check is unclear.

Urgency

Medium

Quick answer

SQLSTATE 42P07 (duplicate_table) means PostgreSQL rejected a CREATE statement because a relation with that name already exists in the target schema. Check the current search_path and inspect the existing catalog object before altering DDL.

Safety first

Stop and check this first

  • Never run DROP TABLE or DROP SCHEMA CASCADE in production just to make a failing CREATE statement pass; verify the existing object's contents and take a backup first.
  • Do not add CREATE TABLE IF NOT EXISTS blindly to silence 42P07, because PostgreSQL does not verify that the existing relation matches the columns, data types, or constraints your application expects.

Safe checks you can perform

  • Inspect the ErrorResponse fields and exact failing CREATE statement
  • Check SHOW search_path in the failing session configuration
  • Query information_schema.tables for the conflicting relation

Leave these tasks to a qualified professional

  • Do not attempt this professional task: Schema-qualify the DDL or apply CREATE TABLE IF NOT EXISTS after structure verification
  • Do not attempt this professional task: Resolve relation namespace collisions with a verified DBA backup

What a technician must be able to justify

Technician visit

These are the steps they must carry out and show you. Tick them as they work: if steps are skipped, do not accept a part swap.

These are not instructions for opening or handling the equipment yourself. They are the checks that should support a technical conclusion.

  1. Schema-qualify the DDL or apply CREATE TABLE IF NOT EXISTS after structure verification

    What they doHave the developer and DBA qualify the target schema explicitly (schema.table) and, only after confirming the existing table structure matches the release, make the migration idempotent or reconcile migration tracking.

    They must show youa column-by-column comparison between the existing relation in information_schema and the intended CREATE TABLE definition.

    In writingthe reviewed migration change, the target schema name, and the staging test result.

  2. Resolve relation namespace collisions with a verified DBA backup

    What they doWhen an obsolete table, view, sequence, or index occupies the required name in the schema, have the DBA back up the affected object, verify dependencies, and rename or remove the conflicting relation under change control.

    They must show youthe catalog query proving the conflicting object's type and row count alongside the completed backup file.

    In writingthe backup reference, the exact DDL executed, and the owner approval before modifying the schema.

Before accepting a part or repair

Nothing has been shown yet. Do not accept a part swap based on the code alone.

Follow these steps

Note: Follow the documented order. An error code identifies the affected system, but it does not prove by itself which part has failed.

  1. Inspect the ErrorResponse fields and exact failing CREATE statement

    No special tools

    Who:
    You or your developer reviews the application or PostgreSQL log.
    How:
    Capture the ErrorResponse C field (SQLSTATE 42P07), the primary M message, any s (schema) or t (table) fields if present, and the exact DDL statement that triggered the failure.
    Result that matters:
    Whether the failing DDL statement used an explicit schema.table name or an unqualified relation name.
    If the DDL statement is unqualified, that points to search_path resolution determining the target schema at runtime.
    If the DDL statement specifies an explicit schema.table name, that rules out search_path ambiguity and confirms a name collision inside that named schema.
    Evidence to keep:
    Save the complete error message, SQLSTATE code, timestamp, and exact CREATE statement text.

    Evidence: PostgreSQL 18 Appendix A: Error Codes · PostgreSQL error and notice fields

  2. Check SHOW search_path in the failing session configuration

    Tools required

    Who:
    Your developer or DBA runs a read-only session check.
    How:
    Connect with the same role and database used by the failing job and run SHOW search_path; by default this returns "$user", public.
    Result that matters:
    Which existing schema appears first in search_path and therefore acts as the current schema for unqualified CREATE TABLE commands.
    If a per-user schema matching $user or another custom schema appears before public, that confirms unqualified DDL is targeting that first schema rather than public.
    If search_path resolves directly to the expected schema, that rules out schema redirection and isolates the conflict to an existing object in that schema.
    Evidence to keep:
    Record the exact output of SHOW search_path and the active session role name.

    Run only read-only SHOW queries during diagnosis; do not alter role or database search_path settings on production without reviewing dependent queries.

    Evidence: PostgreSQL 18 schemas · PostgreSQL 18 CREATE TABLE

  3. Query information_schema.tables for the conflicting relation

    Tools required

    Who:
    Your developer or DBA queries the database catalog in read-only mode.
    How:
    Query information_schema.tables filtering by table_schema and table_name to inspect table_type (BASE TABLE, VIEW, FOREIGN, or LOCAL TEMPORARY), and check whether a sequence, index, or materialized view holds the name.
    Result that matters:
    The exact relation type, schema, and column structure of the existing object.
    If table_type is BASE TABLE and its columns and constraints match the migration specification, that confirms the table creation already ran earlier.
    If the existing object is a VIEW, FOREIGN table, index, sequence, or a table with different columns, that rules out a simple duplicate run and indicates a naming collision.
    Evidence to keep:
    Keep the query result showing table_schema, table_name, table_type, and existing column definitions.

    Never execute DROP TABLE or DROP SCHEMA CASCADE to clear a 42P07 error before verifying what data the existing relation holds.

    Evidence: PostgreSQL 18 schemas · PostgreSQL 18 information_schema tables view · PostgreSQL 18 CREATE TABLE

Still not solved

If the error persists, stop here. See what the technician must do and show you.

Virtual technician

Does 42P07 still appear after these checks?

This guided flow uses the exact code and your answers to choose a test, an expected result and the next branch.

Where this code applies

A code is meaningful only inside the right product and version context.

Primary scope

System
Software and digital services
Brand
PostgreSQL
Product type
Databases

Known code variants

  • duplicate_table
  • PostgreSQL 42P07
  • SQLSTATE 42P07

What it means

PostgreSQL Appendix A assigns 42P07 to duplicate_table under Class 42 Syntax Error or Access Rule Violation. According to the CREATE TABLE documentation, a new table's name must be distinct from the name of any other relation in the same schema, because tables, sequences, indexes, views, materialized views, and foreign tables share one schema namespace. When CREATE TABLE omits a schema qualification, PostgreSQL places the object in the current schema, which is the first existing schema in search_path. Code 42P07 alone does not prove a prior migration succeeded or that the existing relation has the expected structure.

Warnings and stop conditions

  • Mediumeditorial warning

    Never run DROP TABLE or DROP SCHEMA CASCADE in production just to make a failing CREATE statement pass; verify the existing object's contents and take a backup first.

    Evidence: PostgreSQL 18 schemas · PostgreSQL 18 CREATE TABLE

  • Lowsource verified

    Do not add CREATE TABLE IF NOT EXISTS blindly to silence 42P07, because PostgreSQL does not verify that the existing relation matches the columns, data types, or constraints your application expects.

    Evidence: PostgreSQL 18 CREATE TABLE

Symptoms and causes

Probable causes · in order

  • Repeated DDL script or migration creating a table that already existsCommon

    Re-running a non-idempotent CREATE TABLE script in the same database and schema fails with 42P07 when the base table was already created during an earlier deployment or manual run.

    Evidence: PostgreSQL 18 Appendix A: Error Codes · PostgreSQL 18 CREATE TABLE

  • Another relation type occupying the same name in the target schemaPossible

    Within a single PostgreSQL schema, tables, sequences, indexes, views, materialized views, and foreign tables share the same namespace, so an existing view, index, or sequence with that name blocks CREATE TABLE.

    Evidence: PostgreSQL 18 schemas · PostgreSQL 18 information_schema tables view · PostgreSQL 18 CREATE TABLE

  • Unqualified CREATE statement resolving to an unexpected current schemaPossible

    When a CREATE command does not specify schema.table, PostgreSQL targets the first schema in search_path (or the session temporary schema first for temporary tables), causing a collision in a schema the developer did not intend to use.

    Evidence: PostgreSQL 18 schemas · PostgreSQL 18 CREATE TABLE

Sources and technical references

  1. 1

    PostgreSQL 18 Appendix A: Error Codes

    PostgreSQL Global Development Group · 2026 · official_doc

  2. 2

    PostgreSQL error and notice fields

    PostgreSQL Global Development Group · 2026 · official_doc

Sources: PostgreSQL 18 Appendix A: Error Codes · PostgreSQL error and notice fieldsreviewed 2026-10-19 · verified

Was this entry useful?