Feedback-ID: i76984098:Fastmail
Message-ID: <75b33741-ee99-4524-b63a-edad21c1266d@aklaver.com>
Date: Thu, 6 Mar 2025 12:34:59 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Duplicate Key Values
To: mark bradley <markbradyju@outlook.com>
References: <CH3PR22MB4312C23476C0E67F9B4C2E10BACB2@CH3PR22MB4312.namprd22.prod.outlook.com>
 <CANzqJaDWzZ4O10QZi6w__N5q7gzwcZANfu0DxA7UOpFwqAqTyQ@mail.gmail.com>
 <SJ2PR22MB43284AE4E32208EB035F68F5BACB2@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <CANzqJaCZefaoZqJzPSFqEaRA1W-Ps5E6yNuL_PT-WxUw9x38Nw@mail.gmail.com>
 <36356aa1-1a5c-44af-a1c0-f190249812ca@aklaver.com>
 <SJ2PR22MB4328C53DB4397EB9759419DDBACA2@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <6aee94f9-d421-4622-bccc-bdac37969be0@aklaver.com>
 <SJ2PR22MB4328C88FCBA2B447CC72DC90BACA2@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <b21040db-9b7f-414c-9d11-da08685e1a81@aklaver.com>
 <SJ2PR22MB4328931B7CB951A48F1434ABBACA2@SJ2PR22MB4328.namprd22.prod.outlook.com>
Content-Language: en-US
Cc: pgsql-general <pgsql-general@postgresql.org>
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <SJ2PR22MB4328931B7CB951A48F1434ABBACA2@SJ2PR22MB4328.namprd22.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/75b33741-ee99-4524-b63a-edad21c1266d%40aklaver.com>
Precedence: bulk

On 3/6/25 12:06, mark bradley wrote:

My mistake I forgot to Cc list on my previous post, which was:

That would be an issue and also would mean it is not a PK.

In psql  do:

\d dataset

and show the results as text in your reply.

Ccing list


The below shows there is an index("dataset_pkey") on node_id.

Note, reindexing will take a lock on the table that prevents changing 
data while the operation is running. See the below for more information:

https://www.postgresql.org/docs/current/sql-reindex.html

If the table is not to big and you can interrupt access to it then the 
simplest command to run would be:

REINDEX  TABLE dataset;

> Universal Metadata Schema=# \d dataset
>                                   Table "public.dataset"
>            Column           |           Type            | Collation | 
> Nullable | Defau
> lt
> ---------------------------+---------------------------+-----------+----------+------
> ---
>   node_id                   | integer                   |           | 
> not null |
>   dataset_name              | character varying(25)     |           | 
> not null |
>   notes                     | text                      |           |   
>         |
>   dataset_type              | database_type             |           | 
> not null |
>   dataset_maturity          | database_maturity_type    |           | 
> not null |
>   disposition               | disposition_type          |           | 
> not null |
>   start_date                | date                      |           |   
>         |
>   end_date                  | date                      |           |   
>         |
>   most_recent_update        | date                      |           |   
>         |
>   update_periodicity        | interval                  |           |   
>         |
>   system_of_record          | text                      |           |   
>         |
>   point_of_contact          | integer                   |           | 
> not null |
>   dataset_url               | text                      |           |   
>         |
>   classification_level      | classification_level_type |           | 
> not null |
>   physical_location         | text                      |           |   
>         |
>   quality_control           | yes_no_type               |           | 
> not null |
>   dataset_documentation_url | text                      |           | 
> not null |
>   description               | text                      |           |   
>         |
>   node_type                 | node_type                 |           |   
>         |
>   dummy                     | integer                   |           |   
>         |
> Indexes:
>      "dataset_pkey" PRIMARY KEY, btree (node_id)
> Foreign-key constraints:
>      "node_id" FOREIGN KEY (node_id) REFERENCES node(node_id) NOT VALID
>      "poc" FOREIGN KEY (point_of_contact) REFERENCES poc(poc_id) NOT VALID
> Referenced by:
>      TABLE "dataset_table" CONSTRAINT "dataset" FOREIGN KEY (node_id) 
> REFERENCES datas
> et(node_id) NOT VALID
>      TABLE "dataset_subject" CONSTRAINT "dataset_subject_node_id_fkey" 
> FOREIGN KEY (no
> de_id) REFERENCES dataset(node_id)
>      TABLE "system_dataset" CONSTRAINT "system_dataset_node_id_fkey" 
> FOREIGN KEY (node
> _id) REFERENCES dataset(node_id) NOT VALID
> Inherits: node
> 
> 
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Thursday, March 6, 2025 3:03 PM
> *To:* mark bradley <markbradyju@outlook.com>
> *Subject:* Re: Duplicate Key Values
> On 3/6/25 10:51, mark bradley wrote:
> Reply to list alos.
> Ccing list.
> 
>> Looks like there is no index on node_id at the moment
> 
> That would be an issue and also would mean it is not a PK.
> 
> In psql  do:
> 
> \d dataset
> 
> and show the results as text in your reply.
> 
>> 
>> 
>> Mark Brady, Ph.D.
>> Deputy Chief Data Officer, TRMC
>> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady 
> <https://amazon.com/author/markjbrady>>_
>> ------------------------------------------------------------------------
>> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
>> *Sent:* Thursday, March 6, 2025 1:22 PM
>> *To:* mark bradley <markbradyju@outlook.com>; Ron Johnson 
>> <ronljohnsonjr@gmail.com>; pgsql-general <pgsql-general@postgresql.org>
>> *Subject:* Re: Duplicate Key Values
>> On 3/6/25 10:11, mark bradley wrote:
>>> Here is the table definition:
>>> 
>>> 
>>> And here is the error message I get when I try to delete a duplicate:
>> 
>> Please answer the following:
>> 
>> 1) Did you not see duplicates with the old version of pgAdmin4?
>> 
>> 2) What do you see if you use psql?
>> 
>> 3) Did you upgrade/move the Postgres server or the underlying OS?
>> 
>> 4) Have you tried reindexing the node_id field?
>> 
>>> 
>>> 
>>> Mark Brady,
>>> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady
>> <https://amazon.com/author/markjbrady 
> <https://amazon.com/author/markjbrady>>>_
>>> ------------------------------------------------------------------------
>> 
>> -- 
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com