Re: Duplicate Key Values

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(dot)klaver(at)aklaver(dot)com