From: Marco DE BOOIJ <marco.developer@debooy.eu>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Duplicate data even with primary keys enabled
Date: Fri, 28 Oct 2022 22:51:09 +0200
Message-ID: <91e84ed8-d669-45ef-61f2-e4af4d442afa@debooy.eu> (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>
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
>
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: marco.developer@debooy.eu, pgsql-admin@lists.postgresql.org
Subject: Re: Duplicate data even with primary keys enabled
In-Reply-To: <91e84ed8-d669-45ef-61f2-e4af4d442afa@debooy.eu>
* 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