PostgreSQL: how to remove even rows

Question

Structure of rows in my table is following:

col1   col2   col3
  a      b      c
  a      b      c
  a      b      c
  d      e      f
  d      e      f

I've enumerated them using row_number

col1   col2   col3   rn
  a      b      c     1
  a      b      c     2
  a      b      c     3
  d      e      f     1
  d      e      f     2

and I would like to remove every row which has even rn value.

How could I do this? My idea was with using row_number but maybe there is another solution.

Answer 1

This is painful because you do not have a unique value to identify each row. You can use ctid on the fly to do this:

delete from t
   using (select t2.*, ctid as orig_ctid,
                row_number() over (partition by col1, col2, col3 order by ?) as seqnum
         from t t2
        ) t2
   where t2.orig_ctid = t.ctid and
         mod(seqnum, 2) = 0;

Having a serial column is a much better idea, though, than relying on ctid.