Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ooWN8-0000Xl-2P for pgsql-admin@arkaria.postgresql.org; Fri, 28 Oct 2022 20:54:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ooWN6-0007Mk-Gh for pgsql-admin@arkaria.postgresql.org; Fri, 28 Oct 2022 20:54:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ooWN5-0007MW-TF for pgsql-admin@lists.postgresql.org; Fri, 28 Oct 2022 20:54:32 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ooWN2-0000Bx-RA for pgsql-admin@lists.postgresql.org; Fri, 28 Oct 2022 20:54:30 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 00A2E380F4E for ; Fri, 28 Oct 2022 22:54:26 +0200 (CEST) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id gLrq_cc3abie for ; Fri, 28 Oct 2022 22:54:25 +0200 (CEST) Received: from [192.168.0.178] (ip-109-40-240-24.web.vodafone.de [109.40.240.24]) by rs.plausibolo.de (Postfix) with ESMTPSA id 90AD1380273 for ; Fri, 28 Oct 2022 22:54:25 +0200 (CEST) Message-ID: <782c8ac1-0551-d383-b30f-0bdc956e2d90@jakobs.com> Date: Fri, 28 Oct 2022 22:54:24 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.0 To: "pgsql-admin@lists.postgresql.org" References: <59B1180A-4A4B-4A1A-B50A-C9D9B41AA67D@gmail.com> <74e777cb-c332-3db7-7c4a-784d408227d6@jakobs.com> From: Holger Jakobs Subject: Re: Duplicate data even with primary keys enabled In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------HPTATi2eNbHveDk0Wai7Taq8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------HPTATi2eNbHveDk0Wai7Taq8 Content-Type: multipart/mixed; boundary="------------Ut14SNrrttCoLC454DqxWar3"; protected-headers="v1" From: Holger Jakobs To: "pgsql-admin@lists.postgresql.org" Message-ID: <782c8ac1-0551-d383-b30f-0bdc956e2d90@jakobs.com> Subject: Re: Duplicate data even with primary keys enabled References: <59B1180A-4A4B-4A1A-B50A-C9D9B41AA67D@gmail.com> <74e777cb-c332-3db7-7c4a-784d408227d6@jakobs.com> In-Reply-To: --------------Ut14SNrrttCoLC454DqxWar3 Content-Type: multipart/alternative; boundary="------------B4FR1IrUER5ByDm0Ci76o4i7" --------------B4FR1IrUER5ByDm0Ci76o4i7 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 QW0gMjguMTAuMjIgdW0gMjI6MTUgc2NocmllYiBUZWp1IEpha2tpZGkgdmxvZ3M6DQo+IEhl bGxvIEpha29icywNCj4NCj4gSSBoYXZlIHByb3ZpZGVkIHRoZSBzYW1wbGUgY3JlYXRlIHRh YmxlIGFuZCBpbnNlcnRzIHRoYXQgd2UgYXJlIA0KPiBvYnNlcnZpbmcuDQo+DQo+IENSRUFU RSBUQUJMRSBJRiBOT1QgRVhJU1RTICJURVNUIg0KPiAoDQo+IMKgIMKgICJDT0wxIiBjaGFy YWN0ZXIgdmFyeWluZyg5KSBDT0xMQVRFIHBnX2NhdGFsb2cuImRlZmF1bHQiIE5PVCBOVUxM LA0KPiDCoCDCoCAiQ09MMiIgY2hhcmFjdGVyIHZhcnlpbmcoMzApIENPTExBVEUgcGdfY2F0 YWxvZy4iZGVmYXVsdCIgTk9UIE5VTEwsDQo+IMKgIMKgICJDT0wzIiBjaGFyYWN0ZXIgdmFy eWluZygzMCkgQ09MTEFURSBwZ19jYXRhbG9nLiJkZWZhdWx0IiBOT1QgTlVMTCwNCj4gwqAg wqAgIkNPTDQiIG51bWVyaWMoMTAsMCkgTk9UIE5VTEwsDQo+IMKgIMKgICJDT0w1IiBjaGFy YWN0ZXIgdmFyeWluZygxMikgQ09MTEFURSBwZ19jYXRhbG9nLiJkZWZhdWx0IiBOT1QgTlVM TCwNCj4gwqAgwqAgIkNPTDYiIGNoYXJhY3RlciB2YXJ5aW5nKDEyKSBDT0xMQVRFIHBnX2Nh dGFsb2cuImRlZmF1bHQiIE5PVCBOVUxMLA0KPiDCoCDCoCBDT05TVFJBSU5UIHRlc3RfcGsg UFJJTUFSWSBLRVkgKCJDT0wxIiwgIkNPTDIiLCAiQ09MMyIpDQo+ICkNCj4NCj4gQ1JFQVRF IFVOSVFVRSBJTkRFWCBJRiBOT1QgRVhJU1RTICJURVNUX1VOSVFVRSINCj4gwqAgwqAgT04g IlRFU1QiIFVTSU5HIGJ0cmVlDQo+IMKgIMKgICgiQ09MMSIgQ09MTEFURSBwZ19jYXRhbG9n LiJkZWZhdWx0IiBBU0MgTlVMTFMgTEFTVCwgIkNPTDIiIA0KPiBDT0xMQVRFIHBnX2NhdGFs b2cuImRlZmF1bHQiIEFTQyBOVUxMUyBMQVNULCAiQ09MMyIgQ09MTEFURSANCj4gcGdfY2F0 YWxvZy4iZGVmYXVsdCIgQVNDIE5VTExTIExBU1QpOw0KPg0KPiBDUkVBVEUgSU5ERVggSUYg Tk9UIEVYSVNUUyAiVEVTVF9JTkRFWCINCj4gwqAgwqAgT04gIlRFU1QiIFVTSU5HIGJ0cmVl DQo+IMKgIMKgICgiQ09MMSIgQ09MTEFURSBwZ19jYXRhbG9nLiJkZWZhdWx0IiBBU0MgTlVM TFMgTEFTVCwgIkNPTDMiIA0KPiBDT0xMQVRFIHBnX2NhdGFsb2cuImRlZmF1bHQiIEFTQyBO VUxMUyBMQVNUKTsNCj4NCj4gKl9BbHNvLCB0aGUgdmFsdWVzIHRoYXQgd2UgYXJlIHNlZWlu ZyBpcyBhcyBiZWxvdzpfKg0KPg0KPiBDT0wxIAlDT0wyIAlDT0wzIAlDT0w0IAlDT0w1IAlD T0w2DQo+IDEgCTM0NTYgCTc2NTQyIAk1IAlBQkMgCTEyMzQNCj4gMSAJMzQ1NiAJNzY1NDIg CTUgCUFCQyAJMTIzNA0KPiAyIAk5ODcyIAk4OTc2NSAJMCAJRkdUIAkxMjM0DQo+IDMgCTY1 NDcgCTc4NjU5IAk3IAlKSEwgCTg3OTANCj4NCj4NCj4gV2UgYWxyZWFkeSBkZWZpbmVkIENP TDEsIENPTDIsIENPTDMgYXMgcHJpbWFyeSBrZXlzLCBidXQgc3RpbGwgYXMgeW91IA0KPiBz ZWUgYWJvdmUgaW4gdGhlIHRhYmxlIG91dHB1dCwgdGhlIGZpcnN0IDIgcm93cyBoYXMgZXhh Y3RseSBzYW1lIA0KPiBjb21iaW5hdGlvbiBmb3IgdGhvc2UgMyByb3dzLg0KPg0KPiBUaGFu a3MsDQo+IFRlamEuDQo+DQo+DQpJIGp1c3QgY29waWVkIHlvdXIgQ1JFQVRFIFRBQkxFIHN0 YXRlbWVudCAod2l0aCBhbGwgcGVjdWxpYXJpdGllcyBsaWtlIA0KcXVvdGVkIGlkZW50aWZp ZXJzKToNCg0KaGo9IyBDUkVBVEUgVEFCTEUgSUYgTk9UIEVYSVNUUyAiVEVTVCINCmhqLSMg KA0KaGooI8KgwqDCoMKgICJDT0wxIiBjaGFyYWN0ZXIgdmFyeWluZyg5KSBDT0xMQVRFIHBn X2NhdGFsb2cuImRlZmF1bHQiIE5PVCBOVUxMLA0KaGooI8KgwqDCoMKgICJDT0wyIiBjaGFy YWN0ZXIgdmFyeWluZygzMCkgQ09MTEFURSBwZ19jYXRhbG9nLiJkZWZhdWx0IiBOT1QgTlVM TCwNCmhqKCPCoMKgwqDCoCAiQ09MMyIgY2hhcmFjdGVyIHZhcnlpbmcoMzApIENPTExBVEUg cGdfY2F0YWxvZy4iZGVmYXVsdCIgTk9UIE5VTEwsDQpoaigjwqDCoMKgwqAgIkNPTDQiIG51 bWVyaWMoMTAsMCkgTk9UIE5VTEwsDQpoaigjwqDCoMKgwqAgIkNPTDUiIGNoYXJhY3RlciB2 YXJ5aW5nKDEyKSBDT0xMQVRFIHBnX2NhdGFsb2cuImRlZmF1bHQiIE5PVCBOVUxMLA0KaGoo I8KgwqDCoMKgICJDT0w2IiBjaGFyYWN0ZXIgdmFyeWluZygxMikgQ09MTEFURSBwZ19jYXRh bG9nLiJkZWZhdWx0IiBOT1QgTlVMTCwNCmhqKCPCoMKgwqDCoCBDT05TVFJBSU5UIHRlc3Rf cGsgUFJJTUFSWSBLRVkgKCJDT0wxIiwgIkNPTDIiLCAiQ09MMyIpDQpoaigjICkNCmhqLSMg Ow0KDQpDUkVBVEUgVEFCTEUNCg0KVHJ5aW5nIHRvIGluc2VydCB0aGUgc2FtZSByb3cgdHdp Y2UgcmVzdWx0cyBpbiBhbiBlcnJvcjoNCg0KaGo9IyBpbnNlcnQgaW50byAiVEVTVCIgdmFs dWVzICgxLDM0NTYsIDc2NTQyLCA1LCAnQUJDJywgJzEyMzQnKTsNCklOU0VSVCAwIDENCmhq PSMgaW5zZXJ0IGludG8gIlRFU1QiIHZhbHVlcyAoMSwzNDU2LCA3NjU0MiwgNSwgJ0FCQycs ICcxMjM0Jyk7DQpGRUhMRVI6wqAgZG9wcGVsdGVyIFNjaGzDvHNzZWx3ZXJ0IHZlcmxldHp0 IFVuaXF1ZS1Db25zdHJhaW50IMK7dGVzdF9wa8KrDQpERVRBSUw6wqAgU2NobMO8c3NlbCDC uygiQ09MMSIsICJDT0wyIiwgIkNPTDMiKT0oMSwgMzQ1NiwgNzY1NDIpwqsgZXhpc3RpZXJ0 IA0KYmVyZWl0cy4NCg0KU28geW91J2xsIGhhdmUgdG8gY2hlY2sgd2hhdCB5b3UgYXJlIGRv aW5nIGRpZmZlcmVudGx5Lg0KDQoNCi0tIA0KSG9sZ2VyIEpha29icywgQmVyZ2lzY2ggR2xh ZGJhY2gsIFRlbC4gKzQ5LTE3OC05NzU5MDEyDQoNCg== --------------B4FR1IrUER5ByDm0Ci76o4i7 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 28.10.22 um 22:15 schrieb Teju Jakkidi vlogs:
Hello Jakobs,

I have provided the sample create table and inserts that we are observing.=C2=A0

CREATE TABLE IF NOT EXISTS "TEST"
(
=C2=A0 =C2=A0 "COL1" character varying(9) COLLATE pg_catalog."d= efault" NOT NULL,
=C2=A0 =C2=A0 "COL2" character varying(30) COLLATE pg_catalog."= default" NOT NULL,
=C2=A0 =C2=A0 "COL3" character varying(30) COLLATE pg_catalog."= default" NOT NULL,
=C2=A0 =C2=A0 "COL4" numeric(10,0) NOT NULL,
=C2=A0 =C2=A0 "COL5" character varying(12) COLLATE pg_catalog."= default" NOT NULL,
=C2=A0 =C2=A0 "COL6" character varying(12) COLLATE pg_catalog."= default" NOT NULL,
=C2=A0 =C2=A0 CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "= COL3")
)

CREATE UNIQUE INDEX IF NOT EXISTS "TEST_UNIQUE"
=C2=A0 =C2=A0 ON "TEST" USING btree
=C2=A0 =C2=A0 ("COL1" COLLATE pg_catalog."default" ASC NULLS LA= ST, "COL2" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS "TEST_INDEX"
=C2=A0 =C2=A0 ON "TEST" USING btree
=C2=A0 =C2=A0 ("COL1" COLLATE pg_catalog."default" ASC NULLS LA= ST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

Also, the values that we are seeing is as below:

=
COL1 COL2 COL3 COL4 COL5 COL6
1 3456 76542 5 ABC 1234
1 3456 76542 5 ABC 1234
2 9872 89765 0 FGT 1234
3 6547 78659 7 JHL 8790

We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows has exactly same combination for those 3 rows.

Thanks,
Teja.


I just copied your CREATE TABLE statement (with all peculiarities like quoted identifiers):

hj=3D# CREATE TABLE IF NOT EXISTS "TEST"
hj-# (
hj(#=C2=A0=C2=A0=C2=A0=C2=A0 "COL1" character varying(9) COLLATE pg_c= atalog."default" NOT NULL,
hj(#=C2=A0=C2=A0=C2=A0=C2=A0 "COL2" character varying(30) COLLATE pg_= catalog."default" NOT NULL,
hj(#=C2=A0=C2=A0=C2=A0=C2=A0 "COL3" character varying(30) COLLATE pg_= catalog."default" NOT NULL,
hj(#=C2=A0=C2=A0=C2=A0=C2=A0 "COL4" numeric(10,0) NOT NULL,
hj(#=C2=A0=C2=A0=C2=A0=C2=A0 "COL5" character varying(12) COLLATE pg_= catalog."default" NOT NULL,
hj(#=C2=A0=C2=A0=C2=A0=C2=A0 "COL6" character varying(12) COLLATE pg_= catalog."default" NOT NULL,
hj(#=C2=A0=C2=A0=C2=A0=C2=A0 CONSTRAINT test_pk PRIMARY KEY ("COL1", = "COL2", "COL3")
hj(# )
hj-# ;

CREATE TABLE

Trying to insert the same row twice results in an error:

hj=3D# insert into "TEST" values (1,3456, 76542, 5, 'ABC', '1234'); INSERT 0 1
hj=3D# insert into "TEST" values (1,3456, 76542, 5, 'ABC', '1234'); FEHLER:=C2=A0 doppelter Schl=C3=BCsselwert verletzt Unique-Constraint= =C2=BBtest_pk=C2=AB
DETAIL:=C2=A0 Schl=C3=BCssel =C2=BB("COL1", "COL2", "COL3")=3D(1, 345= 6, 76542)=C2=AB existiert bereits.

So you'll have to check what you are doing differently.


--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------B4FR1IrUER5ByDm0Ci76o4i7-- --------------Ut14SNrrttCoLC454DqxWar3-- --------------HPTATi2eNbHveDk0Wai7Taq8 Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCY1xBgAUDAAAAAAAKCRCKV5yTsxlhumVq AJ9PMPPqAgrlD6UmuZSZhn64LAYGiQCg1UMACEE/1EBXZCnZ6QEXPno6tZg= =4GPC -----END PGP SIGNATURE----- --------------HPTATi2eNbHveDk0Wai7Taq8--