Feedback-ID: i76984098:Fastmail
Message-ID: <76cc9aae-2ae9-4211-8d42-ca7f81ea2632@aklaver.com>
Date: Thu, 13 Mar 2025 08:23:04 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Duplicate Key Values
To: mark bradley <markbradyju@outlook.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
References: <CH3PR22MB4312C23476C0E67F9B4C2E10BACB2@CH3PR22MB4312.namprd22.prod.outlook.com>
 <513ddbee-135c-4af2-b6f6-acca667b8d09@aklaver.com>
 <SJ2PR22MB4328C3D2704FB8F6CD079924BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <d9c4c12e-fcf9-44a6-becc-f7c852f62ed4@aklaver.com>
 <SJ2PR22MB4328267BD9F601D90601715BBAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <c6ca9286-a0ea-48bb-adb0-2d4e1835ebee@aklaver.com>
 <SJ2PR22MB4328A3EDC83E495DB9A4BD09BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <84aac7bf-5638-4f1f-9772-ace0ea27e848@aklaver.com>
 <SJ2PR22MB4328CEB1B47FC1AC4A996CB3BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <9203d0df-0ddd-4564-a985-59bcfcf1d294@aklaver.com>
 <SJ2PR22MB432802BD55A9AEFCF212F4A7BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
 <d51484c7-6659-4552-b999-cc220a492f8e@aklaver.com>
 <d21319fa-169d-46d9-81c1-7e22bafe2a2b@aklaver.com>
 <08c96195-3d5f-4902-b5d7-15916a02e23f@aklaver.com>
 <SJ2PR22MB43283C748A57C46E382FF4A3BAD32@SJ2PR22MB4328.namprd22.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <SJ2PR22MB43283C748A57C46E382FF4A3BAD32@SJ2PR22MB4328.namprd22.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/76cc9aae-2ae9-4211-8d42-ca7f81ea2632%40aklaver.com>
Precedence: bulk

On 3/13/25 07:56, mark bradley wrote:
>  >Mark, to illustrate:
> 
>  >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);
>  >NOTICE:  merging column "node_id" with inherited definition
> 
>  >insert into node values (1, 'dog');
>  >insert into node_1 values (1, 'cat', 'f');
> 
>  >select * from node;
>   > node_id | fld1
>  >---------+------
>  >        1 | dog
>  >        1 | cat
> 
> That would make sense except that I never explicitly use the 
> *inherits* option in the *node_1* (my *dataset*) table.  Postgres seems 
> to be assuming that.

1) Well it is there from \d node:

Number of child tables: 2 (Use \d+ to list them.)

and from \d dataset and \d  processing_node

Inherits: node

Also it explains the behavior.


2) Postgres does not assume that, it was done explicitly by some command.


> 
> Also, the second column in *node* and in *dataset* are two different 
> columns.  However, Postgres insists on the *node_type* attribute being 
> included (last column) in table *dataset *and won't let me delete 
> it.**This is redundant because every dataset is a dataset type of node.

That is what inheritance does:

https://www.postgresql.org/docs/current/sql-createtable.html

"
     The optional INHERITS clause specifies a list of tables from which 
the new table automatically inherits all columns. Parent tables can be 
plain tables or foreign tables.

     Use of INHERITS creates a persistent relationship between the new 
child table and its parent table(s). Schema modifications to the 
parent(s) normally propagate to children as well, and by default the 
data of the child table is included in scans of the parent(s).

     If the same column name exists in more than one parent table, an 
error is reported unless the data types of the columns match in each of 
the parent tables. If there is no conflict, then the duplicate columns 
are merged to form a single column in the new table. If the column name 
list of the new table contains a column name that is also inherited, the 
data type must likewise match the inherited column(s), and the column 
definitions are merged into one. If the new table explicitly specifies a 
default value for the column, this default overrides any defaults from 
inherited declarations of the column. Otherwise, any parents that 
specify default values for the column must all specify the same default, 
or an error will be reported.

     CHECK constraints are merged in essentially the same way as 
columns: if multiple parent tables and/or the new table definition 
contain identically-named CHECK constraints, these constraints must all 
have the same check expression, or an error will be reported. 
Constraints having the same name and expression will be merged into one 
copy. A constraint marked NO INHERIT in a parent will not be considered. 
Notice that an unnamed CHECK constraint in the new table will never be 
merged, since a unique name will always be chosen for it.

     Column STORAGE settings are also copied from parent tables.

     If a column in the parent table is an identity column, that 
property is not inherited. A column in the child table can be declared 
identity column if desired.
"


> So, I think the crux of the problem is that Postgres assumes that 
> inheritance is declared when it is not.

No it does not, as the screenshot shows. There is an explicit setting 
for 'Inherited from table(s)'

> 
> More answers to your questions coming.
> 
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_

-- 
Adrian Klaver
adrian.klaver@aklaver.com