public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Duplicate Key Values
Date: Thu, 13 Mar 2025 11:05:45 -0400
Message-ID: <CANzqJaDDg51R2UvWgSw10+zxN9BteocWOAyGwWpuPq9hYXKkJQ@mail.gmail.com> (raw)
In-Reply-To: <SJ2PR22MB43283C748A57C46E382FF4A3BAD32@SJ2PR22MB4328.namprd22.prod.outlook.com>
References: <CH3PR22MB4312C23476C0E67F9B4C2E10BACB2@CH3PR22MB4312.namprd22.prod.outlook.com>
<[email protected]>
<SJ2PR22MB43282F7C60DD81CA90502FA0BAD52@SJ2PR22MB4328.namprd22.prod.outlook.com>
<[email protected]>
<SJ2PR22MB4328C3D2704FB8F6CD079924BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
<[email protected]>
<SJ2PR22MB4328267BD9F601D90601715BBAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
<[email protected]>
<SJ2PR22MB4328A3EDC83E495DB9A4BD09BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
<[email protected]>
<SJ2PR22MB4328CEB1B47FC1AC4A996CB3BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
<[email protected]>
<SJ2PR22MB432802BD55A9AEFCF212F4A7BAD12@SJ2PR22MB4328.namprd22.prod.outlook.com>
<[email protected]>
<[email protected]>
<[email protected]>
<SJ2PR22MB43283C748A57C46E382FF4A3BAD32@SJ2PR22MB4328.namprd22.prod.outlook.com>
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 <parent_table>" clause to the "CREATE TABLE foo" statement.
Are you creating the tables via PgAdmin point-and-click?
On Thu, Mar 13, 2025 at 10:56 AM mark bradley <[email protected]>
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.
>
> 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.
>
> ...
> The reason for the current design is that I also have a *dataflow* table
> as shown below.
>
> A dataflow record describes the flow of data between two nodes. Now, if
> there are 3 types of node: dataset, processing, and user, then there are 9
> types of dataflow. Hence, 9 tables are needed to represent the dataflows
> instead of 1. In the below, *source_id* and *destination_id* are both
> *node_id*s. If I want to know if a node is a dataset, processing node,
> or a user, I just look that up in the nodes table.
>
>
> So, I think the crux of the problem is that Postgres assumes that
> inheritance is declared when it is not.
>
> More answers to your questions coming.
>
> Best regards,
> Mark Brady
> *amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>*
>
> ------------------------------
> *From:* Adrian Klaver <[email protected]>
> *Sent:* Wednesday, March 12, 2025 12:01 PM
> *To:* mark bradley <[email protected]>
> *Cc:* pgsql-general <[email protected]>
> *Subject:* 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
> [email protected]
>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Attachments:
[image/png] image.png (106.6K, 3-image.png)
download | view image
[image/png] image.png (39.4K, 4-image.png)
download | view image
[image/png] image.png (71.7K, 5-image.png)
download | view image
[image/png] image.png (24.8K, 6-image.png)
download | view image
view thread (25+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Duplicate Key Values
In-Reply-To: <CANzqJaDDg51R2UvWgSw10+zxN9BteocWOAyGwWpuPq9hYXKkJQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox