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 1oohuW-0005GU-9e for pgsql-admin@arkaria.postgresql.org; Sat, 29 Oct 2022 09:13:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oohuU-0004cX-PF for pgsql-admin@arkaria.postgresql.org; Sat, 29 Oct 2022 09:13:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ooWJu-0006p1-H2 for pgsql-admin@lists.postgresql.org; Fri, 28 Oct 2022 20:51:14 +0000 Received: from relay3-d.mail.gandi.net ([217.70.183.195]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ooWJs-0001Fu-2Z for pgsql-admin@lists.postgresql.org; Fri, 28 Oct 2022 20:51:14 +0000 Received: (Authenticated sender: marco.mailbox@debooy.eu) by mail.gandi.net (Postfix) with ESMTPSA id A399A60002 for ; Fri, 28 Oct 2022 20:51:10 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=debooy.eu; s=gm1; t=1666990270; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=C2C6oM5ZBZRxeuzJFiG+YgLGhq2MmRyCKqqay/JQGm8=; b=CRE0P1DIRe2roY3R31PX7chkaAykA7v6F0HL7YVF1tAuE+Xbjzuc7GUHNmOOig8AG8/6/v yqqU6xUZrBkDrJplz5sRnX5pCbppjNwOZvP9eMUji8swwYqAh4Z7UpOSdMJaKuPP3oNWiY 7FONG4DpNeCVJIthjT+xBLCkM/vVZ6Au/Dqb1zjzYoGmYiWDuAiL3BTQ/L8/1IeTH/6MF9 IgAREupv+IeSMVqK5xRfcyPT+Ki10fyD7DBQScDjjEbg43lK05V09uXuAjePdm0X0e8qQh NEtZrdypPCus3r5sXxNPJO546Nilv0ogRidEa8YyHTmBQ9I6RhUCJBqtz7bhjA== Content-Type: multipart/alternative; boundary="------------LEmDsNHqyehXcTxwULuyMJQg" Message-ID: <91e84ed8-d669-45ef-61f2-e4af4d442afa@debooy.eu> Date: Fri, 28 Oct 2022 22:51:09 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Duplicate data even with primary keys enabled Content-Language: nl To: pgsql-admin@lists.postgresql.org References: <59B1180A-4A4B-4A1A-B50A-C9D9B41AA67D@gmail.com> <74e777cb-c332-3db7-7c4a-784d408227d6@jakobs.com> From: Marco DE BOOIJ In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------LEmDsNHqyehXcTxwULuyMJQg Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Your output looks strange. The character colums are right aligned. This is for numeric colums. Saying this, can you run the following select to see if there are no trailing or prepending spaces. This can be the reason for not finding the duplicates. select concat('|', "COL1", '|'), concat('|', "COL2", '|'), concat('|', "COL3", '|') from "TEST"; Regards, Marco Op 28/10/2022 om 22:15 schreef 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. > > > On Fri, Oct 28, 2022 at 11:41 AM Holger Jakobs wrote: > > > Am 28.10.22 um 20:27 schrieb Teja Jakkidi: > > Hi YILDIRIM, > > > > Thanks for your response. > > > > We have a composite primary key on 3columns. We are noticing > multiple > > rows with same values in the 3pk columns which should not happen as > > primary constraint is defined on them. > > Also, none of the columns are null. > > > > Regards, > > Teja. J. > > > Unless you don't post your CREATE TABLE command plus some INSERT > command > which lead to the described behavior, we won't be able to track > the problem. > > > -- > Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 > --------------LEmDsNHqyehXcTxwULuyMJQg Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Your output looks strange. The character colums are right aligned. This is for numeric colums. Saying this, can you run the following select to see if there are no trailing or prepending spaces. This can be the reason for not finding the duplicates.

select concat('|', "COL1", '|'), concat('|', "COL2", '|'), concat('|', "COL3", '|') from "TEST";

Regards,

Marco

Op 28/10/2022 om 22:15 schreef 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.


On Fri, Oct 28, 2022 at 11:41 AM Holger Jakobs <holger@jakobs.com> wrote:

Am 28.10.22 um 20:27 schrieb Teja Jakkidi:
> Hi YILDIRIM,
>
> Thanks for your response.
>
> We have a composite primary key on 3columns. We are noticing multiple
> rows with same values in the 3pk columns which should not happen as
> primary constraint is defined on them.
> Also, none of the columns are null.
>
> Regards,
> Teja. J.
>
Unless you don't post your CREATE TABLE command plus some INSERT command
which lead to the described behavior, we won't be able to track the problem.


--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

--------------LEmDsNHqyehXcTxwULuyMJQg--