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 1tslWe-005NP1-UN for pgsql-general@arkaria.postgresql.org; Thu, 13 Mar 2025 16:35:17 +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 1tslWd-000DJA-7D for pgsql-general@arkaria.postgresql.org; Thu, 13 Mar 2025 16:35:15 +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 1tslWc-000DIp-In for pgsql-general@lists.postgresql.org; Thu, 13 Mar 2025 16:35:14 +0000 Received: from mail-mw2nam10olkn2076.outbound.protection.outlook.com ([40.92.42.76] helo=NAM10-MW2-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 1tslWZ-002duH-37 for pgsql-general@postgresql.org; Thu, 13 Mar 2025 16:35:13 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=SpwTWy3MhwRR8DDpb506DqI7w8djyaUyPeP3x8vPXaKTFfgk+11K6aPi9UsV3YmHDbYOl8Efx1U1NSglxwxjJKi2jMG0gws8ndsAA81/1utgTlhnfqu4N1nqtvnmjN2wcB4LWHwBKZu7zBCxbj5XDKeQFScB5p7LH8FbA/zRA9aQNuvtNIJSbmuaiOXiV9x521QJH6wshKuy8jMHkYZJuLcHt6hy9DWWRaAFoyJZ5Up7+EIzLYBqSlFwB8K+Sd4C1bQICbigbL9KRavshIxg4CoeY1BIhW7hziAmzMCrPUrW1l6iJcPc+KMune3PMyPsXlLzQOS46oNvagrzFLcNrw== 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=V5mkRfuIsqhGhs84rG+TXRVK4Jta39FIiYYr0qYW0LY=; b=fxIzfNgHyWv7MEM2wPLWzMXsK5/xzuFfBMPVgdH2HSA48KHMs9tb8fQPFENmYU/TPWtTPYUQGEKviyGCT41nDDWJ48jzsOI3tnLyrjcbs+khevoBK1AYGHicXoZS5lA6EED8U8hO2nT0dEIjJrMWY40YZcu9gIQeL073oz3lO/PyeGuS0aNvfnU9MQuI5sKZG191tRgn5YZBKVy2vRJEq0yBgltNRgI/ghFns5GUeWi0YsBdl44j3NSegqBBQVpiP0YRZWaK+Djbn0K6uFWBWoJMXOW4eK+zOdN5qNvxs6KaMXTh7GOYi7rfrt4VUwO3aP3uHzrdYT1onHEVxh0aoQ== 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=V5mkRfuIsqhGhs84rG+TXRVK4Jta39FIiYYr0qYW0LY=; b=A3cCwIstDHPfhx8MCSDdBH5CTGfucAcSM3Sr6+6fgU2yzWllk8o79gF184nSJzEvQu3gU7TmVG9XZ/Kj7/0j5yq0vR9JYYQ9iy/gP94CbpVA+v4QTe6cgiRTQmB+r8eE63vtMc+GbrF0QTcrZYOecq7vu+L84b7NIMR6tChrEOgAD7L+TCHgtH6L9FFitYF/bQ0DpQupboaQHtbrZeXLvsFsS4gPDQxrOWiRhN9Z2mqYANh7tUUA1ppoNyWj11xMVPI2tCdqhaXykmqkz8ZJX8OGY6U42HXIoQ4/ICj3LBKnUwX+A1lC+PG+02Or3F7rEQWd1U5xLNT3kJuLJHIxSQ== Received: from SJ2PR22MB4328.namprd22.prod.outlook.com (2603:10b6:a03:547::6) by CO6PR22MB2948.namprd22.prod.outlook.com (2603:10b6:303:14f::13) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8534.25; Thu, 13 Mar 2025 16:35:03 +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.8534.017; Thu, 13 Mar 2025 16:35:03 +0000 From: mark bradley To: Adrian Klaver , Ron Johnson , pgsql-general Subject: Re: Duplicate Key Values Thread-Topic: Duplicate Key Values Thread-Index: AQHbjfSQTi8IT+qnIE+vrsDEftWknrNk0i6AgABZWESAAAeigIAAA0qAgAEyu6OAAAQ9gIAACBsIgAAUTYCAAACl9oAACBWAgAEp+imAABowgIAAQNjvgAAKxYCABeQlYIAACUYAgAACETqAAAJXAIAAALnDgAAMoYCAABfCkYAAJO+AgAACozmAAAp6gIABRLeAgAAEAwCAAXIJboAAENCAgAAL/Z+AAAS9gIAAB1UM Date: Thu, 13 Mar 2025 16:35:03 +0000 Message-ID: References: <84aac7bf-5638-4f1f-9772-ace0ea27e848@aklaver.com> <9203d0df-0ddd-4564-a985-59bcfcf1d294@aklaver.com> <08c96195-3d5f-4902-b5d7-15916a02e23f@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_|CO6PR22MB2948:EE_ x-ms-office365-filtering-correlation-id: 3742a1e6-14e8-4c1f-3383-08dd624d0196 x-microsoft-antispam: BCL:0;ARA:14566002|461199028|9400799030|15080799006|7092599003|12050799009|19110799003|8060799006|15030799003|12121999004|8062599003|1602099012|10035399004|3412199025|440099028|4302099013|41001999003|102099032; x-microsoft-antispam-message-info: =?us-ascii?Q?nC3GqgcR00MZWt561k8Xfc2ExQr4e1u8vmO4cgkDdoJsDmQYMLocYDdKJ/kU?= =?us-ascii?Q?2fWmE1gGyKs0fDK89B//s7l0prJO6yULuWm//TSnsSRU5Rk+U41FVRbV1CGR?= =?us-ascii?Q?3kK4mawV11Iw4QWa0Sfg/r53VKPoNVR3uy0w3abQBHB5PP+fU7C8S/9DIVWX?= =?us-ascii?Q?V63i4bgObObZosA/NHIsLqTC53P+tO90QilI4KHKkh4Vy9FCz/NkLQChfdvq?= =?us-ascii?Q?8mgTp/+gQAkXUS130mboJ4756Xu0g1EXH14hexvUkz9rDu3BlYY047vAPS/3?= =?us-ascii?Q?izEzd6ZOX78o61ncc/Dsn6O4EmZ5wOWQsE5gzCCK97A39yiraSLgWuUl3OoG?= =?us-ascii?Q?IFl9qTwfjrzCEjhaq7uDk5JCleivVIyTuLvteGEY+8puME/dHzOAcFPrrNu+?= =?us-ascii?Q?2KvnI982nZlp4ITptiFp7Uo0uXexKXcAh4K9pulNH2r34WenYHu8nhGS/G1K?= =?us-ascii?Q?FAg+vN43ypI2MKyktKZDCKmrRywU21Y4rmo90/VXOPF9IprSKGheAQsuC43B?= =?us-ascii?Q?Ndx3mz7vfRR2Sb3McLw8y2bCAFJcuPeRwrcfUc1mmGkMPfBhKNd27MX8OG1m?= =?us-ascii?Q?Ba7GTbsDgGTBrikxv5jtTKexMID9YiiGu3azH8QuKQ0pMGa22fHFMDZ5RixZ?= =?us-ascii?Q?yuwp+38Z2xHzaC1Q2iOGqof5iPQbqj29BXQmFlM8vTBgr9K3c1TnlcjqIqqa?= =?us-ascii?Q?iiOmPMh8S/ZLZU6ENhKM8t3po+3lJa7cipoXCR65u41+SHXwuD1PCtZUCiKp?= =?us-ascii?Q?bs85ftsl3XbVzELD0cjOfpGQh/swBuY3kx4HAg6IV2VKNGfgfPKGZtWbUH0i?= =?us-ascii?Q?62mkqSv75MNX/LSb7cb39rzFc8j/+ehTcScrFjH0K6vLroAHLTh+hnxzcqOF?= =?us-ascii?Q?ODoSdJa2WyJidrnQ/z2lmyhFWghaFAoJKz6Y/he/tF1KGzUgfnNQ2P9OG0zR?= =?us-ascii?Q?XpND4NcUmjQOyfx1200TuvPpax7U2BGCfXZs7Ezm4zzO48RST7blpc90AmUt?= =?us-ascii?Q?GX0+bU8Ia4E7EFI1pm+nngNK4qncer7HBtJ88YYV7dmeme8V7BSqfaoftfuB?= =?us-ascii?Q?dU9E1eXsIX46or+OY60Ge5nw64mXEbhFT0Upm96dWsgx+OJJO7d7370yqpAq?= =?us-ascii?Q?zL/uhKe1kq3c2GkowhE930UfH2W2z7UH9ac9gnxf9OTbvuqswu1ILKhUU9sw?= =?us-ascii?Q?kaJfdfgtB2HIj9b6/jFEJbnGf+v6fT1kLaULvNf0Xq1tJEbP+zVZD+OLj+sv?= =?us-ascii?Q?rHkmuxBTIAPp9MUT92p4bECNCM6g65y7m6Fp81200Gd+I882Wn8xYd1as2PG?= =?us-ascii?Q?m3L7ZqFGYDExcHxa9nfUVBdV9c/TIZlr8dD9y7hmPOHh1A=3D=3D?= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?3vMC29wEuCOI1be3yYe2DDaPm3LIvdQ+pSJGcHCP0ALOg3M7uN/j/kJ1AM2N?= =?us-ascii?Q?IJUwWaSEgMivzcLilD6DmpjbT4ECDXqLCj/iUgP1o3g7zs9fyct/SqOaN+lS?= =?us-ascii?Q?H9K68ZnkOHyADrt1hw8Zk3eYayqTov3nx4xXoYZVzw3J5gX5ypgBoUi32jMr?= =?us-ascii?Q?Xe6c1xED3J/VeBnXmsqsw0XoXXi3v+Qakgfy1Uw9OkLUzCz8ZkCrFlQCrSBK?= =?us-ascii?Q?kGVT+BqLMaKWcxTvDmp5UpPO3zIwzRTiQQ/XwfRdtf0yJobsMRcHW1ENuWMR?= =?us-ascii?Q?yZjgIyQHMWQUt9ljAKl8YAgbhQtzbrRQ1YBpZqRJEiWmVt2V+3Yk57GreO24?= =?us-ascii?Q?REsTmFpBkngD3IAJwO8zSeAM47niHBW0ejuY3/YFNxNQ/dzGimJfn0qv0S3Q?= =?us-ascii?Q?KFazICRG7gnU9PqcG9n0dPGe37to5i7iPRcFZgaFnWJnsCVWZQ7p7nRFTg1M?= =?us-ascii?Q?yqsIbgFxtLuIfKtgvNyLlAWyht3wWsKIHmtPEuJPWklvHD6ai5pbnWa/Qez2?= =?us-ascii?Q?ieXGjBEvSQdzz97Z4tWBo/C/xqQcjuk8s6Xor1SkFe3saHi7ifAV2eXMU5wg?= =?us-ascii?Q?qux3r/D++SMweH+CMDx4SqkQSoXF3C/zU/+gNSaRRL4vA8PH8aFCuAo8Jscb?= =?us-ascii?Q?tMxRibhQUALdlGnduaw6AlM8P8Hh38jgOpTbgd/SEPef1IYaHg16BksKv/N3?= =?us-ascii?Q?xvVE2K+7OLGx9pdocx971ixkcZQTkFdT2yA3hb6p31HyRVSIzT8y8oMGpDWI?= =?us-ascii?Q?S6tqasls/9I7aQcnBqEqrBRefgfGP2fLP4Y776EKKuIS/hE4mOABSwJo1v5L?= =?us-ascii?Q?TwXMU+UYuqC26xyRU4qU+FfmzKYq/dMFeEJ3JHcKfLcxB36Z0v6Oj7/kqAJt?= =?us-ascii?Q?qivtI9OP+9QyrDXOTN7MPXftSZ2Ldaocn7/bPCbJEk6TO0S8oOyr54QcHsNb?= =?us-ascii?Q?rvghRIlO6fK36b8H+TI0i7FFJ/R7pdGfdbPBv9xS5Ur3GnlsRAqZ9p3rjvo4?= =?us-ascii?Q?+0AGty1LZLc9wawBbO9bOal32L/8n4Uu0D3IAN8mZ0yNW2jZeBa9ZVykvJVl?= =?us-ascii?Q?K6ntBeNA+CMCxcnMYfkDR4lsVuAiCNz5eRoVePTnLO03es4EkewpX4DshW1/?= =?us-ascii?Q?HuCIIZKp811xZyD8FHxDkHNA+pJV4BnloDaX3c+3pTZIXldKCvNJuH5WZbqt?= =?us-ascii?Q?8/NYHvf//yQDfWPKBl5FBJfm8bHkYmnpPhgCZb3mMGHWtYnbK1Qqm79tCvA?= =?us-ascii?Q?=3D?= Content-Type: multipart/alternative; boundary="_000_SJ2PR22MB4328D554FB55EF3C08633273BAD32SJ2PR22MB4328namp_" 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: 3742a1e6-14e8-4c1f-3383-08dd624d0196 X-MS-Exchange-CrossTenant-originalarrivaltime: 13 Mar 2025 16:35:03.5057 (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: CO6PR22MB2948 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SJ2PR22MB4328D554FB55EF3C08633273BAD32SJ2PR22MB4328namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable > I'm assuming that by 'simple version' you mean no inheritance. Anyway, inheritance can be undone via ALTER TABLE dataset NO INHERIT node; Now, there are no dups and hopefully it will stay that way. Best regards, Mark Brady amazon.com/author/markjbrady ________________________________ From: Adrian Klaver Sent: Thursday, March 13, 2025 12:05 PM To: mark bradley ; Ron Johnson ; pgsql-general Subject: Re: Duplicate Key Values On 3/13/25 08:56, mark bradley wrote: > >Postgresql does not assume / default to inheritance. In text-mode > clients where you type >in "raw" SQL, you have to explicitly add an > explicit "INHERITS " clause to the >"CREATE TABLE foo" > statement. > > >Are you creating the tables via PgAdmin point-and-click? > > I am using PgAdmin 4 v9.1. > > I think the problem may also be related to the fact that I had > *node_id* and *node_type *were in both tables from an earlier design and > Postgres would not let me delete* node_type* from the* dataset* table. Because it was inherited: create table node (node_id integer primary key, fld1 varchar); create table node_1 (node_id integer primary key, node_1_fld boolean) inherits ( node); alter table node_1 drop column fld1; ERROR: cannot drop inherited column "fld1" > > As an experiment, I created a simple version of the same tables from > scratch without *node_type* in the *dataset* table. So far, no dups are > appearing. I'm assuming that by 'simple version' you mean no inheritance. > > Best regards, > Mark Brady > _amazon.com/author/markjbrady _ > ------------------------------------------------------------------------ -- Adrian Klaver adrian.klaver@aklaver.com --_000_SJ2PR22MB4328D554FB55EF3C08633273BAD32SJ2PR22MB4328namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable


>  I'm assuming that by 'simple version' you mean no inheritance.

Anyway, inheritance can be undone via 
ALTER TABLE dataset NO INHERIT node;
Now, there are no dup= s and hopefully it will stay that way.  

Best regards,
Mark Brady
From: Adrian Klaver <adr= ian.klaver@aklaver.com>
Sent: Thursday, March 13, 2025 12:05 PM
To: mark bradley <markbradyju@outlook.com>; Ron Johnson <ro= nljohnsonjr@gmail.com>; pgsql-general <pgsql-general@postgresql.org&g= t;
Subject: Re: Duplicate Key Values
 
On 3/13/25 08:56, mark bradley wrote:
>  >Postgresql does not assume / default to inheritance.  I= n text-mode
> clients where you type >in "raw" SQL, you have to explici= tly add an
> explicit "INHERITS <parent_table>" clause to the >&= quot;CREATE TABLE foo" 
> statement.
>
>  >Are you creating the tables via PgAdmin point-and-click?
>
> I am using PgAdmin 4 v9.1.
>
> I think the problem may also be related to the fact that I had
> *node_id* and *node_type *were in both tables from an earlier des= ign and
> Postgres would not let me delete* node_type* from the* = dataset* table.

Because it was inherited:

create table node (node_id integer primary key, fld1 varchar);

create table node_1 (node_id integer primary key, node_1_fld boolean)
inherits ( node);

alter table node_1 drop column fld1;
ERROR:  cannot drop inherited column "fld1"

>
> As an experiment, I created a simple version of the same tables from <= br> > scratch without *node_type* in the *dataset* table.  So= far, no dups are
> appearing.

I'm assuming that by 'simple version' you mean no inheritance.

>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
> ----------------------------------------------------------------------= --


--
Adrian Klaver
adrian.klaver@aklaver.com

--_000_SJ2PR22MB4328D554FB55EF3C08633273BAD32SJ2PR22MB4328namp_--