I've been learning Postgres (coming from SQL Server), and this error really confuses me.
Here is the code with some sample data:
create table T ( ID serial primary key, A varchar(1), B varchar(1), C varchar(1) )
↑ Testing table.
insert into T (A, B, C) values('A', 'B', 'C'), ('A', 'B', 'C')
↑ Insert duplicates
delete from T where ID in ( select t.ID from ( select ID, row_number() over (partition by A,B,C order by A,B,C) as rn from T) as t where t.rn < (select max(t.rn) from t) )
↑ Delete duplicate keeping the last entry.
The problem is in the
(select max(t.rn) from t) I am assuming this is a noob mistake related to not knowing postgres syntax when it comes to referencing columns with aliases?