Re: Duplicate Key Values

On 3/12/25 08:46, Adrian Klaver wrote:
> On 3/11/25 13:24, Adrian Klaver wrote:
>> On 3/11/25 12:55, mark bradley wrote:
>>> It happened again.  Now there are no sequences (although there once
>>> was).
>>
>> Read my previous post and provide the information requested.
>>
>
> Mark sent me the below, which answers some of the questions, namely
> there is inheritance going on:

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

This is explained here:

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

INHERITS ( parent_table [, ... ] )

"... , and by default the data of the child table is included in scans
of the parent(s)."

This explains why you see duplicates of node_id.

Though if you try to enter a duplicate value in to a particular table
you get:

insert into node_1 values (1, 'test', 't');
ERROR: duplicate key value violates unique constraint "node_1_pkey"
DETAIL: Key (node_id)=(1) already exists.

This still does not explain why REINDEX TABLE node; caused data to
disappear?

> > Did you ever run VALIDATE CONSTRAINT against them?
> Here is the run

As error notes VALIDATE CONSTRAINT only works on FK and check
constraints. You would need to run against the FK constraints that where
marked NOT VALID e.g "dataset" on the dataset table.

Honestly, I think you need rework your data model. Not sure what the
inheritance is getting you. Seems simpler to just have the node table
not be inherited and just use FK relationships back to it.

>
> Universal Metadata Schema=# ALTER TABLE node VALID
> ATE CONSTRAINT node_id;
> ERROR: constraint "node_id" of relation "node" is
> not a foreign key or check constraint
> Universal Metadata Schema=#
>
> Universal Metadata Schema=# ALTER TABLE dataset VA
> LIDATE CONSTRAINT node_id;
> ALTER TABLE
> Universal Metadata Schema=#
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com