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
