Re: Erroneous behavior of primary key - Mailing list pgsql-general

From Rob Sargent
Subject Re: Erroneous behavior of primary key
Date
Msg-id 1B74B41D-936B-464D-9D53-A7374C27F3A2@gmail.com
Whole thread Raw
In response to Erroneous behavior of primary key  (Daniel J Peacock <bluedanunit@gmail.com>)
List pgsql-general

> On Aug 27, 2018, at 1:50 PM, Daniel J Peacock <bluedanunit@gmail.com> wrote:
>
> 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
Elasticsearchengine.  What I'm finding is that even though there is a primary key on the table, I am getting duplicated
keysin 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
tobe duplicated, I get multiple results.  When I select from the table where field is equal to the duplicated field I
getone result.  I verified that they are distinct row with ctid.  I also created a clone of the table with CTAS and
thentried to create a unique index on the id varchar field but that failed with "duplicate keys found".  I'm stumped as
towhat 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
tableson this database.   
> What could be causing this sort of problem?
>
> Thanks in advance for any insights.
>
> Dan Peacock
> Auto-wares, Inc.
Check for trailing white space

pgsql-general by date:

Previous
From: Daniel J Peacock
Date:
Subject: Erroneous behavior of primary key
Next
From: Ken Tanzer
Date:
Subject: Re: Will there ever be support for Row Level Security on Materialized Views?