Subject: Removing duplicate rows in table

From: Rich Shepard <[email-masked]>
Date: Tue, 10 Sep 2024 08:07:04 -0700 (PDT)

I've no idea how I entered multiple, identical rows in a table but I want to
delete all but one of these rows.

Here's an example:

bustrac=# select * from projects where proj_nbr = '4242.01';
  proj_nbr |   proj_name    | start_date |  end_date  |  description  | notes 
----------+----------------+------------+------------+---------------+-------
  4242.01  | Expert witness | 2008-10-15 | 2008-10-28 | Consol Energy |
  4242.01  | Expert witness | 2008-10-15 | 2008-10-28 | Consol Energy |
  4242.01  | Expert witness | 2008-10-15 | 2008-10-28 | Consol Energy |
  4242.01  | Expert witness | 2008-10-15 | 2008-10-28 | Consol Energy | 
(4 rows)

How do I clean this up so there's only a single row for this project number?

TIA,

Rich

---

From: Christophe Pettus <[email-masked]>
Date: Tue, 10 Sep 2024 08:10:26 -0700

If you don't mind taking the time to swap tables, you can always do an INSERT ... SELECT DISTINCT <fields> into a new table, and then swap it with the existing table.

> On Sep 10, 2024, at 08:07, Rich Shepard <[email-masked]> wrote:
> 
> proj_nbr |   proj_name    | start_date |  end_date  |  description  | notes ----------+----------------+------------+------------+---------------+-------
> 4242.01  | Expert witness | 2008-10-15 | 2008-10-28 | Consol Energy | (4 rows)
>

---

From: Ron Johnson <[email-masked]>
Date: Tue, 10 Sep 2024 11:16:15 -0400

>   proj_nbr |   proj_name    | start_date |  end_date  |  description  |

https://www.postgresqltutorial.com/postgresql-window-function/postgresql-row_number/

I'd use row_number to delete records where row_number > 1.

-- 
Death to America, and butter sauce.
Iraq lobster!

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 10 Sep 2024 08:22:36 -0700

> delete all but one of these rows.

Is there a Primary Key or Unique index on this table?

-- 
Adrian Klaver
[email-masked]

---

From: Andreas Kretschmer <[email-masked]>
Date: Tue, 10 Sep 2024 17:28:15 +0200

>  proj_nbr |   proj_name    | start_date |  end_date  | description  | 

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)

postgres=#

-- 
Andreas Kretschmer
CYBERTEC PostgreSQL Services and Support

---

From: Rich Shepard <[email-masked]>
Date: Tue, 10 Sep 2024 08:29:44 -0700 (PDT)

> Is there a Primary Key or Unique index on this table?

Adrian,

No. It didn't occur to me to make the project number a PK as this table is
not related to others in the database.

But, yesterday it occurred to me to make the proj_nbr a PK to eliminate
future issues.

Thanks,

Rich

---

From: Rich Shepard <[email-masked]>
Date: Tue, 10 Sep 2024 08:32:34 -0700 (PDT)

On Tue, 10 Sep 2024, Francisco Olarte wrote:

> Do you have any kid of corruption (i.e, unique index violation) or is
> it just a duplicate problem?

Francisco,

Only a duplicate problem because when I created this table I didn't make the
proj_nbr column a PK.

> Also, if you do not have any uniqueness criteria consider adding an
> "id identity" column, it is useful when shit hits the fan.

Yep. that's what I will do.

Thanks,

Rich

---

From: Rich Shepard <[email-masked]>
Date: Tue, 10 Sep 2024 08:33:46 -0700 (PDT)

On Tue, 10 Sep 2024, Christophe Pettus wrote:

> If you don't mind taking the time to swap tables, you can always do an
> INSERT ... SELECT DISTINCT <fields> into a new table, and then swap it
> with the existing table.

Christophe,

I'll make the proj_nbr table the PK then do as you recommend.

Thank you,

Rich

---

From: Rich Shepard <[email-masked]>
Date: Tue, 10 Sep 2024 08:35:48 -0700 (PDT)

On Tue, 10 Sep 2024, Andreas Kretschmer wrote:

> 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

Thanks, Andreas.

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 10 Sep 2024 08:45:21 -0700

> But, yesterday it occurred to me to make the proj_nbr a PK to eliminate

You might want to do something like:

select proj_nbr, count(*) as ct from projects group by proj_nbr;

to see how big a problem it is. If it is only a few projects it could 
just a matter of manually deleting the extras.

Whatever you do:

1) Make sure you have a backup of at least that table.

2) Do the data changes as BEGIN; <changes> COMMIT; or ROLLBACK;

---

From: Francisco Olarte <[email-masked]>
Date: Tue, 10 Sep 2024 18:03:16 +0200

Rich:

> Only a duplicate problem because when I created this table I didn't make the

Always report this is if you have future problems, so people know if
it is a case of pilot error or corruption, solutions differ and the
good for ones may harm others.

> Yep. that's what I will do.

Bear in mind the relational model on which relational databases are
modeled needs unique rows ( i.e., no two full rows should be equal on
all fields ). It is not enforced in SQL, but now having it normally
leads to problems. When it cannot be done in any other way, normally
adding an identity column is a cheap way to make them unique. I had
that problem with CDR tables ( it means call detail record, and until
I managed to add circuit identification I had the problem that you can
have two calls from and two the same two numbers with equal start and
end times ) and solved it using an identity column ( added just for
this purpose, after doing it a couple times using ctid in testing, it
is slightly more expensive, but a lot more civilized ).

Francisco Olarte.

---

From: Rich Shepard <[email-masked]>
Date: Tue, 10 Sep 2024 09:38:57 -0700 (PDT)

On Tue, 10 Sep 2024, Adrian Klaver wrote:

> to see how big a problem it is. If it is only a few projects it could just a 
> matter of manually deleting the extras.

Adrian,

It's a small table, not updated in a while. Looking at the example I sent
how do I delete the extras while keeping one when each row has the same
content? Not knowing how to do that is why I wrote.

> 2) Do the data changes as BEGIN; <changes> COMMIT; or ROLLBACK;

Yep. Learned that lesson.

Thanks,

Rich

P.S. Please reply to the mail list so I receive only one copy of your
message, not two.

---

From: Rich Shepard <[email-masked]>
Date: Tue, 10 Sep 2024 09:46:39 -0700 (PDT)

On Tue, 10 Sep 2024, Rich Shepard wrote:

>> to see how big a problem it is. If it is only a few projects it could just 
>> a matter of manually deleting the extras.

> Not knowing how to do that is why I wrote.

A web search (which I should have done before posting this thread) shows me
how to do this:
<https://www.postgresqltutorial.com/postgresql-tutorial/how-to-delete-duplicate-rows-in-postgresql/>

Rich

---

From: Erik Wienhold <[email-masked]>
Date: Wed, 11 Sep 2024 01:34:21 +0200

> P.S. Please reply to the mail list so I receive only one copy of your

You can configure your list subscription to not receive an extra copy.
That setting is under "Global configuration" on
https://lists.postgresql.org/manage/.

-- 
Erik

---

From: Muhammad Usman Khan <[email-masked]>
Date: Thu, 12 Sep 2024 09:06:00 +0500

Hi,
You can try the following CTE which removes all the identical rows and only
leave single row

WITH CTE AS (
  SELECT ctid, ROW_NUMBER() OVER (PARTITION BY proj_nbr, proj_name,
start_date, end_date, description, notes ORDER BY proj_nbr) AS rn
  FROM projects
  WHERE proj_nbr = '4242.02'
)
DELETE FROM projects
WHERE ctid IN (
  SELECT ctid FROM CTE WHERE rn > 1
);

---

From: Rich Shepard <[email-masked]>
Date: Thu, 12 Sep 2024 05:41:32 -0700 (PDT)

> You can try the following CTE which removes all the identical rows and only

Thank you, Muhammed.

Rich