How to fix pg_restore: error: could not create unique index in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 28, 2025· 146 views

The "could not create unique index" error during pg_restore occurs when restoring data contains duplicate values that violate unique constraints. Resolve by identifying duplicate data, fixing the source, or using safe restoration techniques.

Quick fix

Fixes pg_restore: error: could not create unique index

-- Find which table/index has the issue
SELECT * FROM pg_indexes WHERE schemaname = 'public' AND indexname LIKE '%index_name%';

-- Find duplicate values in the problematic column(s)
SELECT column_name, COUNT(*) as duplicate_count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
  1. Identify the problematic index and find duplicate values
  2. Fix the source database or create a clean target
  3. Reset primary key sequences if out of sync
  4. Use pg_restore with safe options
postgresql — error
-- Find which table/index has the issue
SELECT * FROM pg_indexes WHERE schemaname = 'public' AND indexname LIKE '%index_name%';
-- Find duplicate values in the problematic column(s)
SELECT column_name, COUNT(*) as duplicate_count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
pg_restore: error: could not create unique index

What this error means

This error happens when pg_restore attempts to recreate a unique index or constraint on a table that contains duplicate values in the indexed column(s). PostgreSQL cannot create a unique index on data with duplicates because that would violate the uniqueness guarantee. The error typically includes a DETAIL message showing which specific duplicate values caused the failure. This is a data integrity issue that prevents the restore operation from completing successfully.

Typical symptoms

  • pg_restore command fails mid-execution with "could not create unique index" error message
  • DETAIL line shows "Key (...) is duplicated" or "Table contains duplicated values"
  • Error appears partway through restore, leaving the database in an incomplete state
  • Error happens on specific table indexes but not on others

Common causes

  1. Source database contains duplicate values in columns that should be unique
  2. Primary key sequence became out of sync during data import or manipulation
  3. Large objects or system tables retain old data from previous operations
  4. Data was corrupted or inconsistent in the original database before dumping
  5. Restore order violated CHECK constraints or other constraint dependencies
  6. Application-level code inserted duplicates that should have been prevented by the database schema

How to fix "pg_restore: error: could not create unique index"

1Identify the problematic index and find duplicate values

First, determine which index is causing the error by examining the pg_restore error message. The DETAIL section typically shows which column(s) have duplicates. If restoring to an existing database (not recommended), you can query:

sql
-- Find which table/index has the issue
SELECT * FROM pg_indexes WHERE schemaname = 'public' AND indexname LIKE '%index_name%';

-- Find duplicate values in the problematic column(s)
SELECT column_name, COUNT(*) as duplicate_count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
2Fix the source database or create a clean target

The safest approach is to NOT restore over an existing database. Instead, create a fresh database and restore into it. If you must work with an existing target database, clean the problematic table:

sql
-- BACKUP first!
-- Remove duplicates, keeping the first occurrence
DELETE FROM table_name
WHERE ctid NOT IN (
  SELECT MIN(ctid)
  FROM table_name
  GROUP BY column_name
);

-- Alternatively, if you need to keep specific rows, manually identify and delete
DELETE FROM table_name WHERE id IN (
  SELECT id FROM table_name
  WHERE column_name IN (
    SELECT column_name FROM table_name
    GROUP BY column_name HAVING COUNT(*) > 1
  )
  AND id NOT IN (
    SELECT MIN(id) FROM table_name
    GROUP BY column_name
  )
);
3Reset primary key sequences if out of sync

If the error relates to primary key constraints, the sequence may be behind. Check and reset it:

sql
-- Check current sequence value
SELECT MAX(id) FROM table_name;
SELECT nextval("table_name_id_seq");

-- If MAX is higher than nextval, reset the sequence
SELECT setval("table_name_id_seq", (SELECT MAX(id) FROM table_name) + 1);

-- For all tables at once
SELECT setval(pg_get_serial_sequence(tablename, "id"), 
       (SELECT MAX(id) FROM (SELECT * FROM tablename) AS tmp) + 1)
FROM pg_tables WHERE schemaname = "public";
4Use pg_restore with safe options

When re-running pg_restore, use these options to provide more control:

bash
# Restore into a fresh, empty database (recommended)
creatdb new_db_name
pg_restore -d new_db_name --jobs=4 --no-password backup.dump

# If restoring to existing DB, use single-transaction mode for consistency
pg_restore -d existing_db -j 1 --single-transaction backup.dump

# If the error persists, restore without indexes first
pg_restore -d existing_db --no-indexes backup.dump
# Then create indexes manually after inspecting data
5Clean large objects if necessary

In rare cases, old large objects can cause unique constraint violations on system indexes:

sql
-- WARNING: Only run if you are certain there are orphaned large objects
DELETE FROM pg_largeobject WHERE loid IN (
  SELECT oid FROM pg_largeobject_metadata
  WHERE owner NOT IN (SELECT usesysid FROM pg_user)
);

-- Or TRUNCATE if starting fresh (destructive)
TRUNCATE pg_largeobject;

Advanced notes

For production systems, always restore to a fresh database instance first. Test your application against it before applying the data to production. If using managed PostgreSQL services (AWS RDS, Google Cloud SQL, Heroku), check their specific migration guides. Cloud SQL provides tools to find and fix inconsistent indexes. If duplicates exist in your source database, investigate why your application allowed them—you may need schema changes or application-level fixes to prevent future occurrences. Consider using REINDEX CONCURRENTLY on critical indexes rather than dropping and recreating them during restore, as it maintains availability.

Frequently asked questions

What does "pg_restore: error: could not create unique index" mean?
This error happens when pg_restore attempts to recreate a unique index or constraint on a table that contains duplicate values in the indexed column(s). PostgreSQL cannot create a unique index on data with duplicates because that would violate the uniqueness guarantee. The error typically includes a DETAIL message showing which specific duplicate values caused the failure.
How do I fix "pg_restore: error: could not create unique index"?
The "could not create unique index" error during pg_restore occurs when restoring data contains duplicate values that violate unique constraints. Resolve by identifying duplicate data, fixing the source, or using safe restoration techniques.
What causes "pg_restore: error: could not create unique index"?
Source database contains duplicate values in columns that should be unique Primary key sequence became out of sync during data import or manipulation Large objects or system tables retain old data from previous operations
Is the "pg_restore: error: could not create unique index" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading