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
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
-
Use
IF NOT EXISTSwhere the command supports it.CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE TABLE IF NOT EXISTS t (id int);
-
Roles, constraints and types have no
IF NOT EXISTS; check first in aDOblock.DO $$ BEGIN IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'app') THEN CREATE ROLE app LOGIN; END IF; END; $$; -
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
42P07relation already exists42P04database already exists42P06schema already exists42704undefined object
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42846cannot coerce42803column must appear in the GROUP BY clause42830invalid foreign key42804datatype mismatch42P18could not determine data type of parameter42703column does not exist
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.
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.