How to fix Duplicate table in PostgreSQL

PostgreSQLBEGINNERMEDIUMUpdated Dec 27, 2025· 223 views

The "duplicate table" error (SQLSTATE 42P07) occurs when you attempt to create a table that already exists in the database. Use CREATE TABLE IF NOT EXISTS to prevent this error.

Quick fix

Fixes 42P07

SELECT EXISTS (
  SELECT 1 FROM information_schema.tables 
  WHERE table_schema = 'public' 
  AND table_name = 'your_table_name'
);
  1. Check if the table already exists
  2. Use CREATE TABLE IF NOT EXISTS
  3. Drop and recreate the table (destructive)
  4. Fix migration scripts and idempotency
postgresql — error
SELECT EXISTS (
  SELECT 1 FROM information_schema.tables 
  WHERE table_schema = 'public' 
  AND table_name = 'your_table_name'
);
Duplicate table

What this error means

PostgreSQL raises the "duplicate table" error (error code 42P07) when a CREATE TABLE statement tries to create a relation (table) with a name that already exists in the current schema. This is PostgreSQL's way of enforcing that table names must be unique within a schema. The error message typically appears as "relation \"table_name\" already exists" and indicates a naming conflict in your database schema.

Typical symptoms

  • CREATE TABLE statement fails with relation already exists error
  • Migration scripts halt during table creation steps
  • Application startup fails when initializing database schema
  • ORM or migration tool reports duplicate table conflicts

Common causes

  1. Running a CREATE TABLE statement twice without dropping or checking for existence first
  2. Multiple migrations or initialization scripts creating the same table
  3. Rerunning database setup scripts without cleaning up previous tables
  4. Case sensitivity issues where "users" and "Users" are treated as the same table in PostgreSQL
  5. Table already exists but the application doesn't know about it

How to fix "Duplicate table"

1Check if the table already exists

Query the information schema to verify the table exists in your current schema:

sql
SELECT EXISTS (
  SELECT 1 FROM information_schema.tables 
  WHERE table_schema = 'public' 
  AND table_name = 'your_table_name'
);

If this returns true, the table exists. If false, the issue is elsewhere.

2Use CREATE TABLE IF NOT EXISTS

Modify your CREATE TABLE statement to check for existence before creating:

sql
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

This will silently skip table creation if it already exists, preventing the duplicate table error.

3Drop and recreate the table (destructive)

If you need to rebuild the table from scratch:

sql
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

WARNING: This destroys any data in the table. Only use this in development or if you've backed up your data.

4Fix migration scripts and idempotency

For migration tools (Flyway, Liquibase, etc.), ensure migrations are idempotent:

sql
-- Use transactional DDL for safety
BEGIN;
  DROP TABLE IF EXISTS users;
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
  );
COMMIT;

Alternatively, structure migrations to skip if already done:

sql
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'users') THEN
    CREATE TABLE users (id SERIAL PRIMARY KEY);
  END IF;
END $$;

Advanced notes

PostgreSQL treats table names as case-insensitive by default (they're lowercased), but quoted identifiers are case-sensitive. Ensure your schema naming conventions are consistent. In transactional DDL, a failed DROP/CREATE within a transaction rolls back cleanly. For concurrent applications, migrations should be locked at the database level to prevent multiple processes creating tables simultaneously. If you encounter this during a pg_restore, use the --clean flag: pg_restore --clean --create dump.sql

Frequently asked questions

What does "42P07" mean?
PostgreSQL raises the "duplicate table" error (error code 42P07) when a CREATE TABLE statement tries to create a relation (table) with a name that already exists in the current schema. This is PostgreSQL's way of enforcing that table names must be unique within a schema. The error message typically appears as "relation \"table_name\" already exists" and indicates a naming conflict in your database schema.
How do I fix "42P07"?
The "duplicate table" error (SQLSTATE 42P07) occurs when you attempt to create a table that already exists in the database. Use CREATE TABLE IF NOT EXISTS to prevent this error.
What causes "42P07"?
Running a CREATE TABLE statement twice without dropping or checking for existence first Multiple migrations or initialization scripts creating the same table Rerunning database setup scripts without cleaning up previous tables
Is the "42P07" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading