Re: Duplicate data even with primary keys enabled

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(at)jakobs(dot)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
>
>