From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Removing duplicate rows in table |
Date: | 2024-09-10 15:35:48 |
Message-ID: | 265771f-f2c8-3c98-aebf-6239da97f3b2@appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2024-09-10 15:45:21 | Re: Removing duplicate rows in table |
Previous Message | Rich Shepard | 2024-09-10 15:33:46 | Re: Removing duplicate rows in table |