Re: Duplicate data even with primary keys enabled - Mailing list pgsql-admin

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

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