PostgreSQL – Column Must Appear in GROUP BY Clause or Be Used in Aggregate Function

postgresqlpostgresql-10postgresql-9.3

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?

Best Answer

You just need to remove the parentheses around the columns in group by (col1, col2). This works in version 9.4 and previous as well:

delete from mytable  
where (col1, col2) in  (
    select col1, col2 from mytable  
    group by col1, col2                   -- <-- changed
    having  count(distinct col3) >1) ;

The reason that it fails (I think) is that while (col1, col2) is equivalent to row(col1, col2), there was some inconsistency in how it was handled in the various clauses which was fixed in 9.5. In previous versions, you could use a more complex construction in WHERE: WHERE (SELECT (col1, col2)) IN .... So this should work in 9.3 as well:

delete from mytable
where (select (col1, col2)) in  (
    select (col1, col2) from mytable  
    group by (col1, col2) having  count(distinct col3) >1) ;
Related Question