📋

Description

This error indicates an attempt to create a database object (like a table, index, function, or view) that already exists with the same name within the current schema. It's a syntax or rule violation preventing the creation of non-unique identifiers in PostgreSQL.

💬

Error Message

duplicate object

🔍

Known Causes

3 known causes

⚠️

Attempting to Recreate an Existing Object

You executed a CREATE statement for a database object (e.g., table, index, function) that already exists in the specified schema.

⚠️

Running Non-Idempotent Scripts

Database migration or setup scripts were executed multiple times without including checks to prevent the recreation of existing objects.

⚠️

Naming Conflict in Schema

A naming conflict exists with an object of the same name, potentially due to case sensitivity differences or an oversight in schema management.

🛠️

Solutions

3 solutions available

1. Use IF NOT EXISTS easy

Create only if object doesn't exist

1

For indexes

CREATE INDEX IF NOT EXISTS idx_email ON users (email);

2

For types/enums

DO $$ BEGIN
  CREATE TYPE status AS ENUM ('active', 'inactive');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

3

For roles

DO $$ BEGIN
  CREATE ROLE myuser;
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

2. Drop and Recreate easy

Remove existing object first

1

Drop existing object

DROP INDEX IF EXISTS idx_email;
DROP TYPE IF EXISTS status;
DROP ROLE IF EXISTS myuser;

2

Then create new

CREATE INDEX idx_email ON users (email);

3. Find and Rename Existing easy

Keep existing with different name

1

Check existing objects

-- Find indexes:
SELECT indexname FROM pg_indexes WHERE indexname = 'idx_email';

-- Find types:
SELECT typname FROM pg_type WHERE typname = 'status';

2

Use different name

CREATE INDEX idx_users_email ON users (email);

3

Or rename existing

ALTER INDEX idx_email RENAME TO idx_email_old;

🔗

Related Errors

5 related errors

PostgreSQL Error 42710: Duplicate Object Definition