Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.1\))
Subject: Re: Duplicate data even with primary keys enabled
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CAKA2XvYoEw0qbMKmnk686gOKe+FrR22R14S6Ts1aY96+ExHtgA@mail.gmail.com>
Date: Fri, 28 Oct 2022 14:50:22 -0600
Cc: Holger Jakobs <holger@jakobs.com>,
 pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <CECDAF83-106B-4425-865C-176B170CA56D@elevated-dev.com>
References: 
 <CAAo1mbnEV6QxHwnV6r2EUMXjb_Fz98+8eO6ZRU7eux4FTuF0YA@mail.gmail.com>
 <59B1180A-4A4B-4A1A-B50A-C9D9B41AA67D@gmail.com>
 <74e777cb-c332-3db7-7c4a-784d408227d6@jakobs.com>
 <CAKA2XvaFawqTiRtnYKEz84x4A9rDcY2vDCY2SbkS3e2cNKxLng@mail.gmail.com>
 <032E52B8-00C2-431D-BBAF-DCFCCEA817D2@elevated-dev.com>
 <CAKA2XvYoEw0qbMKmnk686gOKe+FrR22R14S6Ts1aY96+ExHtgA@mail.gmail.com>
To: Teju Jakkidi vlogs <teja.jakkidi05@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/CECDAF83-106B-4425-865C-176B170CA56D%40elevated-dev.com>
Precedence: bulk

> On Oct 28, 2022, at 2:42 PM, Teju Jakkidi vlogs =
<teja.jakkidi05@gmail.com> wrote:
>=20
> I am wondering how the query matters here?

Because it's possible to write a query that returns the same row =
multiple times.

> Also, I am just running a simple select from "TEST" with COL1, COL2, =
COL3 in the WHERE condition.
> select * from 'TEST" WHERE COL1=3D'3456' AND  COL2=3D'76542' AND =
COL3=3D'5';
>=20
> The actual Issue is I am unable to figure out why the table is having =
duplicate data in the primary key columns. Or how postgres accepted 2 =
rows with the same combination of the columns in the primary key =
constraint. I would expect it to throw primary key constraint violation =
error.

A  corrupted index could allow that to happen by failing to catch the =
duplicate insert. I suppose it might also be able to cause the same row =
to be returned multiple times--although I have personally only ever seen =
corrupted indexes cause rows to not be found.

I'd suggest posting the result of \d "TEST", to make sure that the index =
hasn't been altered (or dropped), and trying REINDEX on the table as =
well.