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

-- 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;

-- 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
  )
);

-- 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";

# 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

-- 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;