public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: mark bradley <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
Date: Thu, 6 Mar 2025 12:34:59 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <SJ2PR22MB4328931B7CB951A48F1434ABBACA2@SJ2PR22MB4328.namprd22.prod.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>
<[email protected]>
<SJ2PR22MB4328C53DB4397EB9759419DDBACA2@SJ2PR22MB4328.namprd22.prod.outlook.com>
<[email protected]>
<SJ2PR22MB4328C88FCBA2B447CC72DC90BACA2@SJ2PR22MB4328.namprd22.prod.outlook.com>
<[email protected]>
<SJ2PR22MB4328931B7CB951A48F1434ABBACA2@SJ2PR22MB4328.namprd22.prod.outlook.com>
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 <[email protected]>
> *Sent:* Thursday, March 6, 2025 3:03 PM
> *To:* mark bradley <[email protected]>
> *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 <[email protected]>
>> *Sent:* Thursday, March 6, 2025 1:22 PM
>> *To:* mark bradley <[email protected]>; Ron Johnson
>> <[email protected]>; pgsql-general <[email protected]>
>> *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
>> [email protected]
>>
>
> --
> Adrian Klaver
> [email protected]
>
--
Adrian Klaver
[email protected]
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Duplicate Key Values
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox