gNBVPupH

1/*
2We have:
3*/
4CREATE TABLE data (id BIGINT, name VARCHAR);
5CREATE UNIQUE INDEX ON data (name);
6INSERT INTO data (id, name) VALUES (1, 'Alex'), (2, 'Bob'), (3, 'alex'), (4, 'AleX');
7
8/*
9If we put (3, 'Alex') then we get error:
10*/
11INSERT INTO data (id, name) VALUES (5, 'Alex');
12--- ERROR:  duplicate key value violates unique constraint "data_name_idx"
13--- DETAIL:  Key (name)=(Alex) already exists.
14
15/*
16But we decide that our unique constraint is not so strict and want to migrate and merge entries for new unique constaint
17Just that is not working
18*/
19CREATE UNIQUE INDEX ON data ((lower(name)));
20--- ERROR:  could not create unique index "data_lower_idx"
21--- DETAIL:  Key (lower(name::text))=(alex) is duplicated.
22
23/*
24Decision is to move duplicates to temp table and then merge it
25Duplicates we can select using Window Functions
26*/
27WITH duplicates AS (SELECT id, name, row_number() over (PARTITION BY lower(name) ORDER BY id ASC) FROM data)
28SELECT * FROM duplicates WHERE row_number <> 1;
29---  id | name | row_number 
30--- ----+------+------------
31---   3 | alex |          2
32---   4 | AleX |          3
33--- (2 rows)
34
35
36...