Re: Duplicate data even with primary keys enabled - Mailing 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);

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.

I just copied your CREATE TABLE statement (with all peculiarities like quoted identifiers):
hj=# CREATE TABLE IF NOT EXISTS "TEST"
hj-# (
hj(#     "COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
hj(#     "COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
hj(#     "COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
hj(#     "COL4" numeric(10,0) NOT NULL,
hj(#     "COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
hj(#     "COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
hj(#     CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
hj(# )
hj-# ;

CREATE TABLE

Trying to insert the same row twice results in an error:
hj=# insert into "TEST" values (1,3456, 76542, 5, 'ABC', '1234');
INSERT 0 1
hj=# insert into "TEST" values (1,3456, 76542, 5, 'ABC', '1234');
FEHLER:  doppelter Schlüsselwert verletzt Unique-Constraint »test_pk«
DETAIL:  Schlüssel »("COL1", "COL2", "COL3")=(1, 3456, 76542)« existiert bereits.

So you'll have to check what you are doing differently.

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