MIME-Version: 1.0
References: <e4688faf-de51-93ef-2db-f2acacbd658@appl-ecosys.com>
 <CA+bJJbzg2H-oLvKKt49bFEpkzXpnjcnwtB--28tE1xwin9mxnA@mail.gmail.com> <bf9be43-3723-b24-6624-a4986a2fbcba@appl-ecosys.com>
In-Reply-To: <bf9be43-3723-b24-6624-a4986a2fbcba@appl-ecosys.com>
From: Francisco Olarte <folarte@peoplecall.com>
Date: Tue, 10 Sep 2024 18:03:16 +0200
Message-ID: <CA+bJJbyxMUJFmZ4r22WZ018GJoZxFn+gr3Ad1y5XY5QkjvXZAQ@mail.gmail.com>
Subject: Re: Removing duplicate rows in table
To: Rich Shepard <rshepard@appl-ecosys.com>
Cc: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CA%2BbJJbyxMUJFmZ4r22WZ018GJoZxFn%2Bgr3Ad1y5XY5QkjvXZAQ%40mail.gmail.com>
Precedence: bulk

Rich:

On Tue, 10 Sept 2024 at 17:32, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> Only a duplicate problem because when I created this table I didn't make the
> proj_nbr column a PK.

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.


> > 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.

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.