Re: Duplicate Key Values

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(dot)klaver(at)aklaver(dot)com>
> *Sent:* Thursday, March 6, 2025 3:03 PM
> *To:* mark bradley <markbradyju(at)outlook(dot)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(dot)klaver(at)aklaver(dot)com>
>> *Sent:* Thursday, March 6, 2025 1:22 PM
>> *To:* mark bradley <markbradyju(at)outlook(dot)com>; Ron Johnson
>> <ronljohnsonjr(at)gmail(dot)com>; pgsql-general <pgsql-general(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com