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.
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.
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:
-- 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;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:
-- 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
)
);If the error relates to primary key constraints, the sequence may be behind. Check and reset it:
-- 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";When re-running pg_restore, use these options to provide more control:
# 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 dataIn rare cases, old large objects can cause unique constraint violations on system indexes:
-- 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;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.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL