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?

6

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) ;
  • O what fun. I am laughing about myself. Thank you very much! I tried to find the solution in the select-part. I never looking at the characters behind the "group by". – guettli Apr 4 '18 at 8:57
  • I'm not 100% confident about the explanation by the way. It might have to do with the addition of GROUP BY GROUPING SETS and CUBE in 9.5 as that involved changes in GROUP BY handling. – ypercubeᵀᴹ Apr 4 '18 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.