Re: Duplicate data even with primary keys enabled - Mailing list pgsql-admin
| From | Teju Jakkidi vlogs |
|---|---|
| Subject | Re: Duplicate data even with primary keys enabled |
| Date | |
| Msg-id | CAKA2XvaFawqTiRtnYKEz84x4A9rDcY2vDCY2SbkS3e2cNKxLng@mail.gmail.com Whole thread Raw |
| In response to | Re: Duplicate data even with primary keys enabled (Holger Jakobs <holger@jakobs.com>) |
| Responses |
Re: Duplicate data even with primary keys enabled
(Scott Ribe <scott_ribe@elevated-dev.com>)
Re: Duplicate data even with primary keys enabled (Holger Jakobs <holger@jakobs.com>) Re: Duplicate data even with primary keys enabled (Marco DE BOOIJ <marco.maillist@debooy.eu>) Re: Duplicate data even with primary keys enabled (Marco DE BOOIJ <marco.developer@debooy.eu>) Re: Duplicate data even with primary keys enabled (Samed YILDIRIM <samed@reddoc.net>) |
| List | pgsql-admin |
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);
"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
pgsql-admin by date: