I have a simple table with columns col1, col2, col3. All not nullable.
I want to delete all rows where the tuple (col1, col2) has several entries. Background: a unique constraint for (col1, col2) should be added.
drop table mytable; create table mytable ( col1 integer not null, col2 integer not null, col3 integer not null); -- rows to delete insert into mytable values (1, 1, 1); insert into mytable values (1, 1, 2); -- rows to keep insert into mytable values (2, 2, 1); insert into mytable values (2, 3, 2); delete from mytable where (col1, col2) in ( select col1, col2 from mytable group by (col1, col2) having count(distinct col3) >1) ; select * from mytable;
Above works on PostgreSQL 10 but fails on older versions.
Older versions tell me this error message:
ERROR: column "mytable.col1" must appear in the GROUP BY clause or be used in an aggregate function
How to get this working on PG 9.3?