Antonio Pitasi

A personal space for personal thoughts

SQL

Create an Index on Existing Table #

Sometimes it happens: you want to create a new UNIQUE constraint on a table that currently doesn’t respect it.

The most effective way I found to do it, without downtimes:


-- create a temp table
CREATE TABLE yourtable_temp (LIKE yourtable INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);

-- add your new index/constraint
CREATE UNIQUE INDEX yournewindex ON yourtable_temp(yourfield1, yourfield3);

-- populate the temp table with correct data
INSERT INTO yourtable_temp(yourfield1, yourfield2, yourfield3)
SELECT DISTINCT ON (yourfield1, yourfield3) yourfield1, yourfield2, yourfield3 FROM yourtable; 

-- substitute the old table with the temp one
DROP TABLE yourtable;
ALTER TABLE yourtable_temp RENAME TO yourtable;

Rename yourtable, yourfield*, yourindex accordingly.