// PostgreSQL error reference

PostgreSQL 42710: duplicate object

A CREATE statement used a name already taken by a role, constraint, extension, trigger, type or other non-table object.

SQLSTATE
42710
Name
duplicate_object
Category
Syntax Error or Access Rule Violation
Error message
role "app" already exists

What it means

42710 (duplicate_object) is the "already exists" error for objects other than tables and other relations (42P07), databases (42P04), schemas (42P06) and functions (42723). Typical messages are role "app" already exists, constraint "orders_pkey" for relation "orders" already exists, extension "pgcrypto" already exists and trigger "set_updated_at" for relation "orders" already exists.

It nearly always means a setup or migration script is running a second time.

Common causes

  • Re-running a setup script that creates roles, extensions or types.
  • A migration adding a constraint or trigger that an earlier migration or a manual change already added.
  • Restoring a dump into a database or cluster where the objects already exist.

How to fix it

  1. Use IF NOT EXISTS where the command supports it.

    CREATE EXTENSION IF NOT EXISTS pgcrypto;
    CREATE TABLE IF NOT EXISTS t (id int);
  2. Roles, constraints and types have no IF NOT EXISTS; check first in a DO block.

    DO $$
    BEGIN
        IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'app') THEN
            CREATE ROLE app LOGIN;
        END IF;
    END;
    $$;
  3. For triggers, replace them in place (PostgreSQL 14 and later).

    CREATE OR REPLACE TRIGGER set_updated_at
        BEFORE UPDATE ON orders
        FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

Reproduce it safely

CREATE EXTENSION plpgsql;  -- ERROR: extension "plpgsql" already exists

Related errors

More syntax error or access rule violation errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.