PostgreSQLでの『ERROR: duplicate key value violates unique constraint』を解決する方法

ERROR: duplicate key value violates unique constraint "your_table_column_key"
DETAIL: Key (column_name)=(duplicate_value) already exists.

SELECT column_name, COUNT(*)
FROM your_table
GROUP BY column_name
HAVING COUNT(*) > 1;

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table';

SELECT MAX(id) FROM your_table;

SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table));

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM your_table WHERE column_name = 'some_value') THEN
        INSERT INTO your_table (column_name) VALUES ('some_value');
    END IF;
END
$$;

INSERT INTO your_table (id, name)
VALUES (1, 'Alice')
ON CONFLICT (id) DO NOTHING;

INSERT INTO your_table (id, name)
VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

ALTER TABLE your_table DROP CONSTRAINT your_table_column_key;
-- 作業後に再付与
ALTER TABLE your_table ADD CONSTRAINT your_table_column_key UNIQUE (column_name);

DELETE FROM your_table
WHERE id NOT IN (
  SELECT MIN(id)
  FROM your_table
  GROUP BY column_name
);

SELECT * FROM your_table
WHERE id IN (SELECT id FROM staging_table);

ALTER TABLE your_table ADD CONSTRAINT unique_multi_col UNIQUE (col1, col2);

SELECT col1, col2, COUNT(*)
FROM your_table
GROUP BY col1, col2
HAVING COUNT(*) > 1;