Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tqZ6i-006w8I-PW for pgsql-general@arkaria.postgresql.org; Fri, 07 Mar 2025 14:55:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tqZ6h-007IDa-Io for pgsql-general@arkaria.postgresql.org; Fri, 07 Mar 2025 14:55:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tqZ6g-007IDJ-PV for pgsql-general@lists.postgresql.org; Fri, 07 Mar 2025 14:55:23 +0000 Received: from mail-dm6nam10olkn2079.outbound.protection.outlook.com ([40.92.41.79] helo=NAM10-DM6-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tqZ6e-001Vce-0f for pgsql-general@postgresql.org; Fri, 07 Mar 2025 14:55:21 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=otrJVQFR+z57/BB9thsQw0lebellVs2zMFQMMGDcCa10o5VCCh7Kzy6IiKfS1MhksF1qLW+RAFQOVVeajnEtrDJ8GGNpxYH+6iaDwL/iu7ZuP5SxF1FYThZoqufKKQCkQe2PGLZvsQmV8gCwws84vURGqKlBu7m2Twc6y2cwAfjYMMoq7R0upQXwFr3V2xYkr95uL73wP5/jJzXpzzBzy9HVN/UIeevWlDm3pHZvBewkHa3hKja6KZMDvr0JXWqDBlLAu1LPonisXyxaSe/l96vo1+bgBMyTk+Kh45k2YfwEsO9ZG+4VRK88gaiubM2rJnfZQnpxXeAjQUUpz8onrw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=1zG/pyPJmKd5PYN5sGfZiYnbA7MoAZQaOZbh7GDduko=; b=klMvNAdAaHE3zJPKtgWZfu6uuCXTpNlWViLXRR9dBxmBS4WFb1zCM1wqaDIjZHvu0fMqJKpXIQ6bvAwIHiZ7SM6tAw1PyNdKlrVpLaNUQH+2U1ZIT6T8L2E3oiIPy/pnPlssoZ1xBnSNN8YuKNbd84nMqmIKQKvSQt0JbkgscAoLO8Dxr9JcFw5ER35rr9O41AT+vmu6mqc8Y54Y+az4gABYxhH3jW57iDYmDyC9T2fzGFJBv6OCmGqewLPdabEMLkZYMN9nbv81k3rEpKCdrmCzhUQ09iZQ7X/DdlkGSvLLouq+Uc8+3ZxoguY+v2mc4qSuoWTg1j8MYDv3IvfJcA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outlook.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=1zG/pyPJmKd5PYN5sGfZiYnbA7MoAZQaOZbh7GDduko=; b=GUd23Ux9wBHMM/aytNGUtMIWHDjcl/8fVX2cqpDTgbxL5V660g7a1rxcpALM1bBMl0ZRWy0uQEmXdY12tCP/TNXxHUk8RijJZ3Ar5uJi0eSsp0mTQa8Ydq/2xruKlCc+TxCBoexJDjAhUdZY6RM7yTsVFlB/dYLxmIpmI1uVFoJ01qp+cMu2S6zZebdGh1pi+OlweHkRv/hxi+JvfqquZuukBrCN6dUC4jxD+blN1w/1fAwMh3cIDmbVMi7l7/gi+PdU0Y0FYOQkMZsapeEsuO5zAxQFqkwdTU4X+IRDQgsUZjg12T0CedA+s6VurdpbozLHYRMY1+h8f+s6U8HxBA== Received: from SJ2PR22MB4328.namprd22.prod.outlook.com (2603:10b6:a03:547::6) by DS4PPFD88040915.namprd22.prod.outlook.com (2603:10b6:f:fc00::b4e) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8511.19; Fri, 7 Mar 2025 14:55:18 +0000 Received: from SJ2PR22MB4328.namprd22.prod.outlook.com ([fe80::2440:4b5e:d794:94e]) by SJ2PR22MB4328.namprd22.prod.outlook.com ([fe80::2440:4b5e:d794:94e%6]) with mapi id 15.20.8511.015; Fri, 7 Mar 2025 14:55:18 +0000 From: mark bradley To: Adrian Klaver CC: pgsql-general Subject: Re: Duplicate Key Values Thread-Topic: Duplicate Key Values Thread-Index: AQHbjfSQTi8IT+qnIE+vrsDEftWknrNk0i6AgABZWESAAAeigIAAA0qAgAEyu6OAAAQ9gIAACBsIgAAUTYCAAACl9oAACBWAgAEp+imAAAkwhg== Date: Fri, 7 Mar 2025 14:55:18 +0000 Message-ID: References: <36356aa1-1a5c-44af-a1c0-f190249812ca@aklaver.com> <6aee94f9-d421-4622-bccc-bdac37969be0@aklaver.com> <75b33741-ee99-4524-b63a-edad21c1266d@aklaver.com> In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-publictraffictype: Email x-ms-traffictypediagnostic: SJ2PR22MB4328:EE_|DS4PPFD88040915:EE_ x-ms-office365-filtering-correlation-id: 6480700f-2f56-4c7b-764a-08dd5d88137f x-microsoft-antispam: BCL:0;ARA:14566002|9400799030|8062599003|461199028|15030799003|19110799003|13031999003|8060799006|7092599003|12050799009|15080799006|440099028|10035399004|3412199025|4302099013|41001999003|102099032|1602099012; x-microsoft-antispam-message-info: =?us-ascii?Q?XPnveplFO/MqzeOfeiQZh0sHKaomXU6/kTq0YSwtlbhIGbofNfG2MUxa3+wV?= =?us-ascii?Q?nA5l+08CWHSDq/P+o0Aq4ROgmBfbdghUBK3aQ1r2zdvNUwXyXX3FrW2oJRpq?= =?us-ascii?Q?cDo0jAIkDykWsbAudN+SCIUfdKXNgHzOrtV6ZRrUkyzqEtNIOCQ0ln0ePVXR?= =?us-ascii?Q?HARA72CkmcFr47hPDmmUGiYy8BZzx4gv/RJSSuRfGSEAUQ6kGgtKDPOhZo9e?= =?us-ascii?Q?0sRCcjR+C8pnoNihjlIpELGekalk/GFTB1T7NAERplx03IjS1ic9Boga2D0V?= =?us-ascii?Q?/MxcCId9F4Bk1YS5djwByVx6WUbgU3y+0K1Jt0eH6QtrQg3TA0PbEjPS1ofC?= =?us-ascii?Q?73l3ZYdTyCuPRtYU2OV/BgGfKJ+L3GdBAPX/OSXaIW531FGSjeLscp7ep4/P?= =?us-ascii?Q?K5ksshYtpRO2F7FHvhAsYvbV6f4mRj47OTgTnhjXLUr6TDR4d4fdsOjk89uw?= =?us-ascii?Q?qbBOZKuYCNEQUODzpZfRzZ8fRWcw8pQSpWPKe6BayLF0YahptaA9ZAq/9gKO?= =?us-ascii?Q?GEI0XfbKvMtmbYCqLaV1vrMefUIMcc5m8jrG4xlTOGIKv9fdHXnajPJ/uQt4?= =?us-ascii?Q?7DCi5X21e7poR7ibhmDXa29Abxo2VdGReMH507sr4qqPA4bQStqi0e9yOM+W?= =?us-ascii?Q?M/HkTbkUkRXgPq3efn+0M1ppkYA7w4PBbQ+51d4sDS6qgm/t7L5VfiCBNZhI?= =?us-ascii?Q?Py0mN/2x4rEshxNKSqAcMiAV7sY6yBItYrSiNC060uOa8Lzn2LZNhlGsNFCW?= =?us-ascii?Q?G2Z8s5FLnEwh7W5GJFhlLjwdlbegP4hQCwl4W2d6akZJfx8Nzs5rHBzs9sX+?= =?us-ascii?Q?pKdmLIslD8Yw+mZ23anOac4xWQTzzOaHrIQTOHWGYDUAca4sddnAHTpiW2sH?= =?us-ascii?Q?SYI0Yg9qqX5IoQynSNwUyKqIF93tWC3o0QN+u8p4s1NnScqglZRHXs9aWNfk?= =?us-ascii?Q?KnAr6hhNaaV/eCd+YyZdeUM6epSyDaNjaaXUfT/5s7318GHqyLamE0XE9dO4?= =?us-ascii?Q?rvnudaVa1sLqN0cSBRkdijPelzKAHI71rebj1Fz1omZ+epoX7dEAupuDwNGX?= =?us-ascii?Q?8A4Ylg4VfHbWcditF3CvKcv9qztyvWH8dJSErAvR14Y3Q/AHZfD+ZafPO25Y?= =?us-ascii?Q?sN7Tf9lBwGzJzGc0/Rizf2KWYsXDJYBk3wmyya6pZRjtUcF3iaddTKtig5xH?= =?us-ascii?Q?D4CDyEvGp53J7LLP9iVmg4Qrz85fqIWIgiCTpebxq1787ondpAD5UQUilnzC?= =?us-ascii?Q?CYHlBOrmeE6JmVSpIxHR3PAoEqlqChFSqv8Dxy8XuNdrxMQ3N8SCZPdKiFZA?= =?us-ascii?Q?Wt0vE2xn7CQeWRcusrK3gVjHJ61oCO/oXIxm94fkbDRV81tUP4Z9lnhqo3y3?= =?us-ascii?Q?QrsoxNM=3D?= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?JastvXsI1VkR7apMXYQn4TFX9OLl16b1IhSylS7BzOB/qXWlP/sXY7M3RceP?= =?us-ascii?Q?owtLY9aEwsfQLMpRAl8xp+4xvzS30Kcl7aPV9Vex/DRtvV6OplKhU8/TBQuc?= =?us-ascii?Q?si7Tzu5ZW0KatQo7Yx1ym8QdYTGSXO4GBLHqOgBLr9h7LxR1p2Lh8xXJO01o?= =?us-ascii?Q?AnqOTOJ1wpEYayLfFCDvrcDpRua6hSE+y0698mKyWmVyDU9VD5wXReTjm+qK?= =?us-ascii?Q?sc0iiyzslE4/jFARdAeiQMbsr+vvq4EINY68heuAi0r3YAowbEJsDsl57rQK?= =?us-ascii?Q?/DOTq2HChKUnH7RzfVHLOa+U1dZ/zypbGvpBgTTFT6fZrcCezZCsKRi305el?= =?us-ascii?Q?d38fGtMaeSr1TgFMpBHam61E4Ch1rOeIJkfxtUSXcmGl4O3eEtMpNiKCXHia?= =?us-ascii?Q?C9xWcsta0X8e9HAlqRZDIOyV49VxTOWKZS3ZJMCW8Rka/fJHE6DMLv9jYihh?= =?us-ascii?Q?ZrbQzxbVkgwnXTy1KhxQuHdZ1rhUXFBjKE9MYdy5r5+aJkfc6MCebB9iiL6G?= =?us-ascii?Q?1HV/obxlXmpfFRMoJt9r4nnLsByCvIV6iKyEVO6raoDNvFqo+GIO2GT7IJuw?= =?us-ascii?Q?Q+6H22GtzvbqD5YLbTbzsr2nyIqhJOtU077dWLcdS2pYGWAtr+jbsg5/Skaa?= =?us-ascii?Q?8atKTmeiQeXrkERzhbjsi0qUMpMitxhhy4QM1P+vEv91QyQL0P6ckCDESmWq?= =?us-ascii?Q?EPGoEzEZDIrNR7kNOiJmRW8/Mjl7quvK7agHsWKIeQ/CSZA8Yqx4AZ65DlX2?= =?us-ascii?Q?g3pyf/RJ4Aw6JydRucnTZCkavQVEuft4IzHgGFT6s+LENuZ8hRUlvAFlL5/D?= =?us-ascii?Q?+GgZZtvbiseeAJGIuVHmKmPdiOmlQVe08PUCoZE/XNoVvxlP36uiPG6Dxmwr?= =?us-ascii?Q?zLeKW02KviSXf71HO/na22HP5sclPI+/cJtfVG0eqOsoK6aZFBRPgOqa9jiZ?= =?us-ascii?Q?TYdeiwJfXx51vixzk2FSD19BlmjYsIGFzxn/DV8u2AY4CFZjIU++ZUfpUosA?= =?us-ascii?Q?FW1CO4Odj86iguwUGWkT1j7/qE/lYvyP6yuebyXqmr4JCUvlFfhUImV07WWO?= =?us-ascii?Q?T9Zg3Hfea7qf46t4HEHMSW7FjiyLehaGUyh/e43rmUZmSAf8s04feZ8aXeTN?= =?us-ascii?Q?/LTFoPs75AePbYc3RQXld6jKBodldxlOLFPK5iADN/f/B+4/Y3t/teHY2fuN?= =?us-ascii?Q?lHCJPW+e1A52D8eH/Py0pfoYSLzvg4pXtmEEA+CgrGmXdqk18j5OF0p2hY0?= =?us-ascii?Q?=3D?= Content-Type: multipart/alternative; boundary="_000_SJ2PR22MB4328EAEBDB0C930CC0655149BAD52SJ2PR22MB4328namp_" MIME-Version: 1.0 X-OriginatorOrg: outlook.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: SJ2PR22MB4328.namprd22.prod.outlook.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: 6480700f-2f56-4c7b-764a-08dd5d88137f X-MS-Exchange-CrossTenant-originalarrivaltime: 07 Mar 2025 14:55:18.0310 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: DS4PPFD88040915 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SJ2PR22MB4328EAEBDB0C930CC0655149BAD52SJ2PR22MB4328namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Here are some of the references from Copilot https://dba.stackexchange.com/questions/62675/why-does-my-table-hold-duplic= ate-primary-keys https://stackoverflow.com/questions/55551461/having-duplicate-rows-on-a-pri= mary-key-and-unique-constraints-in-postgres Best regards, Mark Brady amazon.com/author/markjbrady ________________________________ From: mark bradley Sent: Friday, March 7, 2025 9:34 AM To: Adrian Klaver Cc: pgsql-general Subject: Re: Duplicate Key Values This is what MS Copilot has to say about this apparent bug where Postgres i= nserts extra rows violating a primary keys uniqueness constraint: Yes, this issue has been encountered by others. There are a few potential r= easons why this might happen: 1. Sequence Out of Sync: Sometimes, the sequence that generates unique values = for the primary key can become out of sync, especially after a bulk import = or a database restore. You can check if the sequence is out of sync and res= et it if necessary. 2. Index Corruption: Index corruption can occur due to various reasons, such a= s hardware failures or bugs in earlier versions of PostgreSQL. This can lea= d to duplicate primary keys being inserted. 3. Table Inheritance: If you are using table inheritance, primary keys are not= enforced among inherited tables. This can lead to duplicates if not handle= d correctly. 4. Application Logic: Sometimes, the application logic might inadvertently ins= ert duplicate records. Reviewing the application code and insert statements= can help identify and resolve such issues. To resolve the issue, you can: * Check and reset the sequence if it's out of sync. * Rebuild the index if it's corrupted. Any of the first 3 could be involved. There isn't an application involved = other than pgAdmin. 1. Originally, the key in the node table was a sequence, but I changed it to a= non-sequence. 2. There is no index on the primary key node_id, and I understand there should= be one. 3. I didn't explicitly use Postgres inheritance but there are two tables that = are subclasses of node. There are dataset nodes and processing_node [s] ta= bles. Each is a type of node and have primary keys that are foreign keys f= rom the node table. This key is node_id. What to do? I hesitate to just delete my tables and start over because thi= s error will reoccur. Best regards, Mark Brady amazon.com/author/markjbrady ________________________________ From: Adrian Klaver Sent: Thursday, March 6, 2025 3:34 PM To: mark bradley Cc: pgsql-general Subject: 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=3D# \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 VALI= D > 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 _ > ------------------------------------------------------------------------ > *From:* Adrian Klaver > *Sent:* Thursday, March 6, 2025 3:03 PM > *To:* mark bradley > *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 >_ >> ------------------------------------------------------------------------ >> *From:* Adrian Klaver >> *Sent:* Thursday, March 6, 2025 1:22 PM >> *To:* mark bradley ; Ron Johnson >> ; pgsql-general >> *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 > >>_ >>> -----------------------------------------------------------------------= - >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com >> > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com --_000_SJ2PR22MB4328EAEBDB0C930CC0655149BAD52SJ2PR22MB4328namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Here are some of the references from Copilot

https://dba.stackexchange.c= om/questions/62675/why-does-my-table-hold-duplicate-primary-keys

= https://stackoverflow.com/questions/55551461/having-duplicate-rows-on-a-pri= mary-key-and-unique-constraints-in-postgres


Best regards,
Mark Brady
From: mark bradley <mark= bradyju@outlook.com>
Sent: Friday, March 7, 2025 9:34 AM
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
 
This is what MS Copilot has to say about this apparent bug where Postgres i= nserts extra rows violating a primary keys uniqueness constraint:

Yes, this issue has been encountered by others. There are a few potential r= easons why this might happen:
  1. Sequence Out of Sync: Sometimes, the sequence that generates unique values for the primary key= can become out of sync, especially after a bulk import or a database resto= re. You can check if the sequence is out of sync and reset it if necessary.
  2. Index Corruption: I= ndex corruption can occur due to various reasons, such as hardware failures= or bugs in earlier versions of PostgreSQL. This can lead to duplicate prim= ary keys being inserted.
  3. Table Inheritance: = If you are using table inheritance, primary keys are not enforced among inh= erited tables. This can lead to duplicates if not handled correctly.
  4. Application Logic: = Sometimes, the application logic might inadvertently insert duplicate recor= ds. Reviewing the application code and insert statements can help identify = and resolve such issues.
To resolve the issue, you can:
  • Check and reset the sequen= ce if it's out of sync.
  • Rebuild the index if it's corrupted.

Any of the first 3 could be involved.  There isn't an application invo= lved other than pgAdmin.

  1. Originally, the key in the node = ;table was a sequence, but I changed it to a non-sequence.
  2. There is no index on the primary key nod= e_id, and I understand there should be one. 
  3. I didn't explicitly use Postgres inheritanc= e but there are two tables that are subclasses of node.  There are dataset nodes and processing_no= de [s] tables.  Each is a type of node and have primary keys = that are foreign keys from the node table.  This key is node_i= d. 

What to do?  I hesitate to just delete my tables and start over becaus= e this error will reoccur.  

Best regards,
Mark Brady
From: Adrian Klaver <a= drian.klaver@aklaver.com>
Sent: Thursday, March 6, 2025 3:34 PM
To: mark bradley <markbradyju@outlook.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: 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=3D# \d dataset
>                    &= nbsp;              Table "public.da= taset"
>            Column       &= nbsp;   |           Type      =      | Collation |
> Nullable | Defau
> lt
> ---------------------------+---------------------------+-----------+--= --------+------
> ---
>   node_id               &= nbsp;   | integer               &nb= sp;   |           |
> not null |
>   dataset_name             &nb= sp;| character varying(25)     |         &nbs= p; |
> not null |
>   notes               &nb= sp;     | text               &= nbsp;      |           |  = ;
>         |
>   dataset_type             &nb= sp;| database_type             |    = ;       |
> not null |
>   dataset_maturity          | datab= ase_maturity_type    |           |
> not null |
>   disposition             &nbs= p; | disposition_type          |     &nb= sp;     |
> not null |
>   start_date              = ;  | date                 &nbs= p;    |           |  
>         |
>   end_date               =    | date                =      |           |   >         |
>   most_recent_update        | date  = ;                    | &n= bsp;         |  
>         |
>   update_periodicity        | interval &= nbsp;                |    = ;       |  
>         |
>   system_of_record          | text =                      = ;|           |  
>         |
>   point_of_contact          | integ= er                   |   =         |
> not null |
>   dataset_url             &nbs= p; | text                   &n= bsp;  |           |  
>         |
>   classification_level      | classification_= level_type |           |
> not null |
>   physical_location         | text  = ;                    | &n= bsp;         |  
>         |
>   quality_control           | yes_n= o_type               |     &nb= sp;     |
> not null |
>   dataset_documentation_url | text       &nbs= p;              |       &= nbsp;   |
> not null |
>   description             &nbs= p; | text                   &n= bsp;  |           |  
>         |
>   node_type              =   | node_type                = |           |  
>         |
>   dummy               &nb= sp;     | integer              = ;     |           |  
>         |
> Indexes:
>      "dataset_pkey" PRIMARY KEY, btree (node_= id)
> Foreign-key constraints:
>      "node_id" FOREIGN KEY (node_id) REFERENC= ES node(node_id) NOT VALID
>      "poc" FOREIGN KEY (point_of_contact) REF= ERENCES poc(poc_id) NOT VALID
> Referenced by:
>      TABLE "dataset_table" CONSTRAINT "d= ataset" 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.co= m/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@p= ostgresql.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 dup= licate:
>>
>> 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?<= br> >>
>> 4) Have you tried reindexing the node_id field?
>>
>>>
>>>
>>> Mark Brady,
>>> _amazon.com/author/markjbrady <https://ama= zon.com/author/markjbrady
>> <https://amazon.com/author/markjbrady
> <https://amazon.co= m/author/markjbrady>>>_
>>> --------------------------------------------------------------= ----------
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

--
Adrian Klaver
adrian.klaver@aklaver.com

--_000_SJ2PR22MB4328EAEBDB0C930CC0655149BAD52SJ2PR22MB4328namp_--