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.
Fixes 42P07
SELECT EXISTS (
SELECT 1 FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = 'your_table_name'
);SELECT EXISTS (SELECT 1 FROM information_schema.tablesWHERE table_schema = 'public'AND table_name = 'your_table_name');Duplicate table
On this page
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.
Query the information schema to verify the table exists in your current schema:
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.
Modify your CREATE TABLE statement to check for existence before creating:
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.
If you need to rebuild the table from scratch:
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.
For migration tools (Flyway, Liquibase, etc.), ensure migrations are idempotent:
-- 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:
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 $$;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