From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Duplicate data even with primary keys enabled
Date: Fri, 28 Oct 2022 22:54:24 +0200
Message-ID: <782c8ac1-0551-d383-b30f-0bdc956e2d90@jakobs.com> (raw)
In-Reply-To: <CAKA2XvaFawqTiRtnYKEz84x4A9rDcY2vDCY2SbkS3e2cNKxLng@mail.gmail.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>
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.
>
> CREATE TABLE IF NOT EXISTS "TEST"
> (
> "COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
> "COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
> "COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
> "COL4" numeric(10,0) NOT NULL,
> "COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
> "COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
> CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
> )
>
> CREATE UNIQUE INDEX IF NOT EXISTS "TEST_UNIQUE"
> ON "TEST" USING btree
> ("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL2"
> COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE
> pg_catalog."default" ASC NULLS LAST);
>
> CREATE INDEX IF NOT EXISTS "TEST_INDEX"
> ON "TEST" USING btree
> ("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "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=# CREATE TABLE IF NOT EXISTS "TEST"
hj-# (
hj(# "COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
hj(# "COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
hj(# "COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
hj(# "COL4" numeric(10,0) NOT NULL,
hj(# "COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
hj(# "COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
hj(# CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
hj(# )
hj-# ;
CREATE TABLE
Trying to insert the same row twice results in an error:
hj=# insert into "TEST" values (1,3456, 76542, 5, 'ABC', '1234');
INSERT 0 1
hj=# insert into "TEST" values (1,3456, 76542, 5, 'ABC', '1234');
FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »test_pk«
DETAIL: Schlüssel »("COL1", "COL2", "COL3")=(1, 3456, 76542)« existiert
bereits.
So you'll have to check what you are doing differently.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../782c8ac1-0551-d383-b30f-0bdc956e2d90@jakobs.com/3-OpenPGP_signature)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Duplicate data even with primary keys enabled
In-Reply-To: <782c8ac1-0551-d383-b30f-0bdc956e2d90@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox