Thread: Duplicate Key Values
What does "and unique identifier" mean?
(Creating a Primary Key constraint automatically creates a unique no-nulls-allowed index on the column(s), so no other index is needed.)
Can you show the table definition?
Anyway, you should be able to delete one of the duplicate node_id records by specifying another column with a different value;
On Wed, Mar 5, 2025 at 6:14 PM mark bradley <markbradyju@outlook.com> wrote:
Hi Ron,The key is an integer. I'm using pGAdmin4 and recently updated to the latest version.The records are not all identical, some have NULL values of the non-key for some unknown reason. Here is a screenshot. The key is node_id, which I have specified as a key and unique identifier.Mark Brady,From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Wednesday, March 5, 2025 12:50 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Duplicate Key ValuesOn Wed, Mar 5, 2025 at 12:36 PM mark bradley <markbradyju@outlook.com> wrote:Although I did not enter them, somehow duplicate primary key values have appeared in one of my tables.Is it a text/varchar column? Has the distro been upgraded "recently", or maybe streamed from an older Linux system to a newer Linux system?I can't remove the offending rows because the key is a foreign key in another table.What to do?Are the records completely (i.e., all columns) identical?--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Attachment
Here is the table definition:
And here is the error message I get when I try to delete a duplicate:
Mark Brady,
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, March 5, 2025 6:49 PM
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
Sent: Wednesday, March 5, 2025 6:49 PM
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
On 3/5/25 15:37, Ron Johnson wrote:
> What does "and unique identifier" mean?
>
> (Creating a Primary Key constraint automatically creates a unique
> no-nulls-allowed index on the column(s), so no other index is needed.)
>
> Can you show the table definition?
>
> Anyway, you /should/ be able to delete one of the duplicate node_id
> records by specifying another column with a different value;
>
> On Wed, Mar 5, 2025 at 6:14 PM mark bradley <markbradyju@outlook.com
> <mailto:markbradyju@outlook.com>> wrote:
>
> Hi Ron,
>
> The key is an integer. I'm using pGAdmin4 and recently updated to
> the latest version.
1) Did you not see duplicates with the old version of pgAdmin4?
2) What do you see if you use psql?
3) Did you upgrade/move the Postgres server or the underlying OS?
4) Have you tried reindexing the node_id field?
>
> The records are not all identical, some have NULL values of the
> non-key for some unknown reason. Here is a screenshot. The key is
> node_id, which I have specified as a key and unique identifier.
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
> What does "and unique identifier" mean?
>
> (Creating a Primary Key constraint automatically creates a unique
> no-nulls-allowed index on the column(s), so no other index is needed.)
>
> Can you show the table definition?
>
> Anyway, you /should/ be able to delete one of the duplicate node_id
> records by specifying another column with a different value;
>
> On Wed, Mar 5, 2025 at 6:14 PM mark bradley <markbradyju@outlook.com
> <mailto:markbradyju@outlook.com>> wrote:
>
> Hi Ron,
>
> The key is an integer. I'm using pGAdmin4 and recently updated to
> the latest version.
1) Did you not see duplicates with the old version of pgAdmin4?
2) What do you see if you use psql?
3) Did you upgrade/move the Postgres server or the underlying OS?
4) Have you tried reindexing the node_id field?
>
> The records are not all identical, some have NULL values of the
> non-key for some unknown reason. Here is a screenshot. The key is
> node_id, which I have specified as a key and unique identifier.
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Attachment
I tried adding a dummy column with different values and then deleting one, as you suggested. Got the same message.
From: mark bradley <markbradyju@outlook.com>
Sent: Thursday, March 6, 2025 1:11 PM
To: Adrian Klaver <adrian.klaver@aklaver.com>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
Sent: Thursday, March 6, 2025 1:11 PM
To: Adrian Klaver <adrian.klaver@aklaver.com>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
Attachment
It happened again. Now there are no sequences (although there once was).
Sequence of data reentry:
- I reentered the node_ids in table node as a primary key.
- I reentered the datasets, in table dataset.
- Node_id was already a PK in dataset.
- I set node_id to also be a foreign key in dataset.
- I went back to look at node and see that duplicate key values appeared.
Processing nodes are unaffected because I didn't add any data to the processing_node table.
Next, I'm going to create a simple database from scratch and see if I can duplicate this behavior.
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, March 11, 2025 3:37 PM
To: mark bradley <markbradyju@outlook.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
Sent: Tuesday, March 11, 2025 3:37 PM
To: mark bradley <markbradyju@outlook.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
On 3/11/25 11:52, mark bradley wrote:
>
>
> there is an index on node_id as it is the Primary Key.
>
> Why do you think there is not?
>
> My mistake, I misread the output from \d dataset
>
>
>
> Can you elaborate more on point 3.
>
> Are you calling the Foreign Key relationships subclassing?
>
>
> Although I did not explicitly use Postgres to declare inheritance,
> logically speaking table /dataset/ and /processing _node/ inherit or are
> subclasses of /node/ because they are subclasses of /node/ in a dataflow
> diagram.
>
>
> In terms of keys, this is accomplished by having the /node_id/ key in
> the /node/ table appear as a foreign key and as a primary key in both
> the /dataset/ and /processing_node/ tables.
You will need to show the schema definitions for:
node
dataset
processing_node
Best to do using psql \d <table_name>
Also in from previous \d dataset there where NOT VALID FK definitions.
Did you ever run VALIDATE CONSTRAINT against them?
>
>
>
> Is there anything in Postgres log at the time you did the above that
> showed it did more then a REINDEX?
>
>
> Not that I can tell.
>
>
>
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
--
Adrian Klaver
adrian.klaver@aklaver.com
>
>
> there is an index on node_id as it is the Primary Key.
>
> Why do you think there is not?
>
> My mistake, I misread the output from \d dataset
>
>
>
> Can you elaborate more on point 3.
>
> Are you calling the Foreign Key relationships subclassing?
>
>
> Although I did not explicitly use Postgres to declare inheritance,
> logically speaking table /dataset/ and /processing _node/ inherit or are
> subclasses of /node/ because they are subclasses of /node/ in a dataflow
> diagram.
>
>
> In terms of keys, this is accomplished by having the /node_id/ key in
> the /node/ table appear as a foreign key and as a primary key in both
> the /dataset/ and /processing_node/ tables.
You will need to show the schema definitions for:
node
dataset
processing_node
Best to do using psql \d <table_name>
Also in from previous \d dataset there where NOT VALID FK definitions.
Did you ever run VALIDATE CONSTRAINT against them?
>
>
>
> Is there anything in Postgres log at the time you did the above that
> showed it did more then a REINDEX?
>
>
> Not that I can tell.
>
>
>
>
> Best regards,
> Mark Brady
> _amazon.com/author/markjbrady <https://amazon.com/author/markjbrady>_
--
Adrian Klaver
adrian.klaver@aklaver.com
Attachment
>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
>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_ids. 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.
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, March 12, 2025 12:01 PM
To: mark bradley <markbradyju@outlook.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
Sent: Wednesday, March 12, 2025 12:01 PM
To: mark bradley <markbradyju@outlook.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
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
adrian.klaver@aklaver.com
> 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.klaver@aklaver.com
Attachment
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 <markbradyju@outlook.com> 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 | catThat 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_ids. 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.From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, March 12, 2025 12:01 PM
To: mark bradley <markbradyju@outlook.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key ValuesOn 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.klaver@aklaver.com
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Attachment
>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?
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.
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.
From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Thursday, March 13, 2025 11:05 AM
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
Sent: Thursday, March 13, 2025 11:05 AM
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
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 <markbradyju@outlook.com> 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 | catThat 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_ids. 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.From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, March 12, 2025 12:01 PM
To: mark bradley <markbradyju@outlook.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key ValuesOn 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.klaver@aklaver.com
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Attachment
> I'm assuming that by 'simple version' you mean no inheritance.
Inheritance was not specified by me in either case. By simple, I mean that I used fewer columns in the test version, like so:
Where node_id is a foreign key in dataset, and node_type is not.
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, March 13, 2025 12:05 PM
To: mark bradley <markbradyju@outlook.com>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Duplicate Key Values
Sent: Thursday, March 13, 2025 12:05 PM
To: mark bradley <markbradyju@outlook.com>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general <pgsql-general@postgresql.org>
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 <parent_table>" 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 <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
--
Adrian Klaver
adrian.klaver@aklaver.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?
>
> 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 <https://amazon.com/author/markjbrady>_
> ------------------------------------------------------------------------
--
Adrian Klaver
adrian.klaver@aklaver.com