How to fix Could not create unique index in PostgreSQL

-- Find duplicates in a single column:
SELECT column_name, COUNT(*) as count
FROM your_table
GROUP BY column_name
HAVING COUNT(*) > 1;

-- For composite indexes (multiple columns):
SELECT column1, column2, COUNT(*) as count
FROM your_table
GROUP BY column1, column2
HAVING COUNT(*) > 1;

-- To see the actual rows that are duplicated:
SELECT * FROM your_table
WHERE column_name IN (
  SELECT column_name FROM your_table
  GROUP BY column_name
  HAVING COUNT(*) > 1
)
ORDER BY column_name;

-- This keeps the first row inserted, deletes later duplicates:
DELETE FROM your_table a
USING your_table b
WHERE a.ctid > b.ctid
  AND a.column_name = b.column_name;

-- Assumes you have an updated_at or created_at timestamp:
DELETE FROM your_table a
USING your_table b
WHERE a.column_name = b.column_name
  AND a.id < b.id
  AND a.updated_at < b.updated_at;

-- Then keep only the most recent:
DELETE FROM your_table a
USING your_table b
WHERE a.column_name = b.column_name
  AND a.updated_at < b.updated_at;

-- See all duplicates for a composite key:
SELECT *
FROM your_table
WHERE (column1, column2) IN (
  SELECT column1, column2
  FROM your_table
  GROUP BY column1, column2
  HAVING COUNT(*) > 1
)
ORDER BY column1, column2, id;

-- SAFE: Preview which rows will be deleted:
WITH to_delete AS (
  SELECT ctid FROM your_table a
  USING your_table b
  WHERE a.ctid > b.ctid
    AND a.column_name = b.column_name
)
SELECT COUNT(*) FROM to_delete;

-- If the count looks correct, execute the DELETE:
DELETE FROM your_table a
USING your_table b
WHERE a.ctid > b.ctid
  AND a.column_name = b.column_name;

-- Verify duplicates are gone:
SELECT column_name, COUNT(*) FROM your_table
GROUP BY column_name
HAVING COUNT(*) > 1;
-- Should return no rows

-- Find the sequence name:
SELECT pg_get_serial_sequence('your_table', 'id');
-- Result example: public.your_table_id_seq

-- Reset the sequence to the next available value:
SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table) + 1);

-- Verify the sequence is correct:
SELECT nextval('your_table_id_seq');
-- Should return MAX(id) + 2

-- Single column unique index:
CREATE UNIQUE INDEX index_name ON your_table (column_name);

-- Composite unique index (multiple columns):
CREATE UNIQUE INDEX index_name ON your_table (column1, column2);

-- With optional CONCURRENTLY clause (for production tables, does not block reads/writes):
CREATE UNIQUE INDEX CONCURRENTLY index_name ON your_table (column_name);

-- Add a unique constraint (creates an underlying unique index):
ALTER TABLE your_table
ADD CONSTRAINT constraint_name UNIQUE (column_name);

-- Or create a constraint without a separate index (uses existing unique index if available):
ALTER TABLE your_table
ADD CONSTRAINT constraint_name UNIQUE (column_name) USING INDEX index_name;