Re: Duplicate data even with primary keys enabled

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Have you tried dropping the index and then recreating it ?   



Sent via the Samsung Galaxy S22+ 5G, an AT&T 5G smartphone


-------- Original message --------
From: Teju Jakkidi vlogs <teja.jakkidi05@xxxxxxxxx>
Date: 10/31/22 11:07 AM (GMT-06:00)
To: Samed YILDIRIM <samed@xxxxxxxxxx>
Cc: Holger Jakobs <holger@xxxxxxxxxx>, pgsql-admin <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Duplicate data even with primary keys enabled

Hello YILDIRIM,

Thanks for the reply.

The md5() value returns out to be the same for the duplicate values.
Also, postgres version is 14.4 and we did not use CONCURRENTLY keyword while creating indexes.

Regards,
Teja.


On Sat, Oct 29, 2022 at 8:06 AM Samed YILDIRIM <samed@xxxxxxxxxx> wrote:
Hi Teja,

Your columns are varchar. They may seem the same to you due to the tool you use. But, it doesn't mean they are identical. There can be leading or trailing spaces.

You can test the data on columns by md5 hashing function, example below. If their hashes are the same, it means there is a problem on the database side. Otherwise, the problem comes from the data itself or from the application writing the data into the table.

select md5(col1), md5(col2), md5(col3) from table.

Another possibility is that you hit a bug I mentioned earlier from 14.0 to 14.3.
- What version of PpstgreSQL do you use?
- Did you use CONCURRENTLY keyword while creating your index?

Best regards.
Samed YILDIRIM

On Fri, 28 Oct 2022, 23:16 Teju Jakkidi vlogs, <teja.jakkidi05@xxxxxxxxx> wrote:
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@xxxxxxxxxx> 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


[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux