Filtering out duplicates

window_funcs.sql

scratch=# create table pairs (name varchar(5), value integer);
CREATE TABLE
scratch=# insert into pairs values ('a', 1);
INSERT 0 1
scratch=# insert into pairs values ('a', 1);
INSERT 0 1
scratch=# insert into pairs values ('a', 1);
INSERT 0 1
scratch=# insert into pairs values ('a', 11);
INSERT 0 1
scratch=# insert into pairs values ('a', 11);
INSERT 0 1
scratch=# insert into pairs values ('b', 2);
INSERT 0 1
scratch=# insert into pairs values ('b', 2);
INSERT 0 1
scratch=# insert into pairs values ('c', 3);
INSERT 0 1
scratch=# insert into pairs values ('c', 33);
INSERT 0 1
scratch=# select * from pairs;
 name | value
------+-------
 a    |     1
 a    |     1
 a    |     1
 a    |    11
 a    |    11
 b    |     2
 b    |     2
 c    |     3
 c    |    33
(9 rows)

scratch=# select name, value, row_number() over (partition by name, value) as row_num from pairs;
 name | value | row_num
------+-------+---------
 a    |     1 |       1
 a    |     1 |       2
 a    |     1 |       3
 a    |    11 |       1
 a    |    11 |       2
 b    |     2 |       1
 b    |     2 |       2
 c    |     3 |       1
 c    |    33 |       1
(9 rows)

scratch=# create table pairs_unique as
scratch-#   (select name, value from
scratch(#     (select name, value, row_number() over (partition by name, value) as row_num from pairs)
scratch(#   ps where row_num = 1);
SELECT 5
scratch=# select * from pairs_unique;
 name | value
------+-------
 a    |     1
 a    |    11
 b    |     2
 c    |     3
 c    |    33
(5 rows)

scratch=# alter table pairs rename to pairs_original;
ALTER TABLE
scratch=# alter table pairs_unique rename to pairs;
ALTER TABLE
scratch=# select * from pairs;
 name | value
------+-------
 a    |     1
 a    |    11
 b    |     2
 c    |     3
 c    |    33
(5 rows)