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@xxxxxxxxxxx>
*Sent:* Thursday, March 6, 2025 3:03 PM
*To:* mark bradley <markbradyju@xxxxxxxxxxx>
*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@xxxxxxxxxxx>
*Sent:* Thursday, March 6, 2025 1:22 PM
*To:* mark bradley <markbradyju@xxxxxxxxxxx>; Ron Johnson
<ronljohnsonjr@xxxxxxxxx>; pgsql-general <pgsql-general@xxxxxxxxxxxxxx>
*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@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
