PostgreSQL raises ERROR: [object type] "[name]" already exists with SQLSTATE 42710 (condition name duplicate_object) when a CREATE statement attempts to create a named database object — such as an index, constraint, role, schema, extension, or operator — and an object with that name already exists in the same namespace. For example: ERROR: index "users_email_idx" already exists or ERROR: role "app_user" already exists.

What This Error Means

SQLSTATE 42710 belongs to PostgreSQL error class 42 — "Syntax Error or Access Rule Violation." Despite the class name, duplicate_object is a semantic error: the SQL syntax is valid, but the requested object cannot be created because the name is already taken in the target namespace.

PostgreSQL enforces unique names for most schema-level objects within a given namespace. Indexes must be unique within a schema, constraints must be unique within a table, roles are unique across the entire cluster (roles are not schema-scoped), and extensions are unique within a database. When any of these uniqueness requirements are violated, PostgreSQL raises 42710 immediately, before making any changes.

When 42710 is raised inside a transaction block, the transaction is marked as aborted. No changes from the failed statement are applied, but all prior statements in the same transaction are also rolled back unless you are using savepoints. This is the standard PostgreSQL behavior for any error raised mid-transaction.

Common Causes

Running a migration more than once. A migration script that creates indexes or constraints is applied to a database that already has those objects from a prior run. This often happens in CI/CD pipelines or when a migration tool's state tracking gets out of sync.
Creating an index without IF NOT EXISTS. Before PostgreSQL 9.5, CREATE INDEX IF NOT EXISTS did not exist. Code written for older versions (or ported from it) uses plain CREATE INDEX, which fails if the index already exists.
Creating a role or extension that already exists. CREATE ROLE app_user and CREATE EXTENSION pgcrypto fail if the role or extension is already present. This commonly occurs in provisioning scripts that are not idempotent.
Constraint names that collide across tables. In PostgreSQL, check constraint and foreign key constraint names must be unique within a table, but index names (which back unique and primary key constraints) must be unique across the entire schema. A constraint whose backing index collides with an existing index triggers this error.
Concurrent DDL execution. Two processes or migration workers running the same CREATE INDEX or CREATE TABLE ... CONSTRAINT statement at nearly the same time. One succeeds; the other gets 42710.

How to Fix `duplicate_object`

Use IF NOT EXISTS where supported. Modern PostgreSQL versions support this clause for most CREATE statements:

-- Indexes (PostgreSQL 9.5+)
CREATE INDEX IF NOT EXISTS users_email_idx ON users (email);

-- Roles (PostgreSQL 8.x+)
CREATE ROLE IF NOT EXISTS app_user LOGIN;

-- Extensions (PostgreSQL 9.1+)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Schemas (PostgreSQL 9.3+)
CREATE SCHEMA IF NOT EXISTS analytics;

IF NOT EXISTS silently skips the creation and returns a notice instead of an error, making scripts idempotent.

Drop before creating (when safe). If you want to replace an object with a new definition, drop it first:
```
DROP INDEX IF EXISTS users_email_idx;
CREATE INDEX users_email_idx ON users (email);
```
Use this pattern with caution in production — dropping an index removes the query optimization it provides until it is rebuilt.

Check existence before creating. For constraints, which do not support IF NOT EXISTS syntax on ALTER TABLE ... ADD CONSTRAINT, query the catalog first:

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'orders_user_id_fkey'
      AND conrelid = 'orders'::regclass
  ) THEN
    ALTER TABLE orders
      ADD CONSTRAINT orders_user_id_fkey
      FOREIGN KEY (user_id) REFERENCES users (id);
  END IF;
END;
$$;

Fix your migration tool's state tracking. If a migration runner (Flyway, Liquibase, golang-migrate, etc.) re-runs migrations that were already applied, investigate why the applied-migrations table is missing entries or was reset. Do not simply add IF NOT EXISTS to mask a broken migration history.
Use CREATE OR REPLACE for supported object types. Functions, procedures, views, and rules support CREATE OR REPLACE syntax. For indexes and constraints, this syntax is not available — use the drop-and-recreate approach above.

Additional Information

CREATE INDEX IF NOT EXISTS was introduced in PostgreSQL 9.5. On older versions you must check pg_indexes or use a PL/pgSQL block.
CREATE ROLE IF NOT EXISTS and CREATE USER IF NOT EXISTS have been available since PostgreSQL 8.x (released alongside the role system).
Related SQLSTATE codes in class 42: 42P07 (duplicate_table) is raised specifically when CREATE TABLE targets a name that already exists; 42P16 (invalid_table_definition) covers malformed table definitions; 23505 (unique_violation) is a different class entirely — it fires on DML when a unique index constraint is violated, not on DDL.
Most PostgreSQL drivers (libpq, psycopg2, asyncpg, JDBC) surface this as a DuplicateObject exception subclass. In psycopg2, catch it as psycopg2.errors.DuplicateObject (or its base psycopg2.errors.ProgrammingError). In JDBC, inspect SQLState for 42710.
42710 can also be raised by CREATE OPERATOR, CREATE TYPE, CREATE AGGREGATE, and CREATE TEXT SEARCH CONFIGURATION — any DDL that creates a named schema object.

Frequently Asked Questions

Why does ALTER TABLE ... ADD CONSTRAINT raise 42710 instead of 23505? 23505 (unique_violation) is a DML-time error — it fires when an INSERT or UPDATE violates an existing unique index. 42710 is a DDL-time error — it fires when ADD CONSTRAINT tries to create a constraint (and its backing index) whose name is already in use. These are two different operations on different code paths.

Can I use IF NOT EXISTS with ALTER TABLE ... ADD CONSTRAINT? No. As of PostgreSQL 17, ALTER TABLE ... ADD CONSTRAINT IF NOT EXISTS is not supported. You must either query pg_constraint to check existence first (see the PL/pgSQL example above) or use a DO $$ BEGIN ... EXCEPTION WHEN duplicate_object THEN NULL; END $$; block to swallow the error intentionally.

My migration ran once fine. Why does it fail the second time? Your migration is not idempotent. Migration frameworks track which scripts have been applied; if that tracking data is missing or was reset, the framework will attempt to re-run migrations that already succeeded. Fix the state tracking rather than making every statement defensive — masking re-runs can hide real double-apply bugs.

How do I list existing indexes to find name conflicts? Query pg_indexes for index names within a schema:

SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY indexname;

For constraints, query pg_constraint joined to pg_class:

SELECT c.conname, c.contype, t.relname AS table_name
FROM pg_constraint c
JOIN pg_class t ON t.oid = c.conrelid
WHERE t.relnamespace = 'public'::regnamespace
ORDER BY c.conname;

PostgreSQL Duplicate Object (SQLSTATE 42710)

What This Error Means

Common Causes

How to Fix duplicate_object

Additional Information

Frequently Asked Questions

How to Fix `duplicate_object`