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
