Re: Removing duplicate rows in table

On Tue, 10 Sep 2024, Andreas Kretschmer wrote:

> you can use the hidden ctid-column:
>
> postgres=# create table demo (id int, val text);
> CREATE TABLE
> postgres=# insert into demo values (1, 'test1');
> INSERT 0 1
> postgres=# insert into demo values (1, 'test1');
> INSERT 0 1
> postgres=# insert into demo values (1, 'test1');
> INSERT 0 1
> postgres=# insert into demo values (1, 'test1');
> INSERT 0 1
> postgres=# select ctid, * from demo;
>  ctid  | id |  val
> -------+----+-------
>  (0,1) |  1 | test1
>  (0,2) |  1 | test1
>  (0,3) |  1 | test1
>  (0,4) |  1 | test1
> (4 rows)
>
> postgres=# with my_ctid as (select min(ctid) from demo where id = 1 and val = 
> 'test1') delete from demo using my_ctid where id=1 and val='test1' and ctid 
> != my_ctid.min;
> DELETE 3
> postgres=# select ctid, * from demo;
>  ctid  | id |  val
> -------+----+-------
>  (0,1) |  1 | test1
> (1 row)

Thanks, Andreas.

Rich