Erroneous behavior of primary key

From: Daniel J Peacock <bluedanunit(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Erroneous behavior of primary key
Date: 2018-08-27 19:50:51
Message-ID: CAPQSkmPR82gk9RRH8TZjCYzXpNZUyeP4_=nbPdi5qStySoQGJg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good afternoon, all.
I've got an odd situation with a table that has a varchar(255) as the
primary key that is getting key values from an Elasticsearch engine. What
I'm finding is that even though there is a primary key on the table, I am
getting duplicated keys in the data and the constraint is not blocking
these. When I do a "select <pk_field>,count(*) from <table> group by
<pk_field> having count(*) > 1" I get no results. Yet, when I search the
table for a value that is like a key I know to be duplicated, I get
multiple results. When I select from the table where field is equal to the
duplicated field I get one result. I verified that they are distinct row
with ctid. I also created a clone of the table with CTAS and then tried to
create a unique index on the id varchar field but that failed with
"duplicate keys found". I'm stumped as to what could be the problem.
The only thing that I can think of is that the primary key is somehow
corrupt. I've noticed this behavior on other tables on this database.
What could be causing this sort of problem?

Thanks in advance for any insights.

Dan Peacock
Auto-wares, Inc.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Paul Suworow 2018-08-27 20:19:30 Connection to PostgreSQL from Excel
Previous Message Naveen Dabas 2018-08-27 15:33:47 Re: pg_sample