Horton
Menu

Thread

  1. Inheritance in PostgreSQL

    Luis Bruno <l.brunofidelis@gmail.com> — 2023-10-17T21:42:57Z

    Hello, I'm in the process of developing a basic database structure that
utilizes inheritance as part of a test for my work. The database consists
of two tables: ‘PERSON' and ‘CUSTOMER' with the ‘PERSON' table serving as
the parent table for ‘CUSTOMER' .

Initially, I defined the 'CREATE TABLE' statement as follows:

CREATE TABLE PERSON (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    dob DATE
);

CREATE TABLE CUSTOMER (
    registration_date DATE NOT NULL,
    contact VARCHAR(255)
) INHERITS (person);

INSERT INTO PERSON VALUES (1, 'Fulano', '1965-06-07');
INSERT INTO CUSTOMER VALUES (2, 'Beltrano', '1980-10-07', '2023-10-10',
'5561999999999');

With these ‘INSERTS’, we have three records, as expected:


The problem occurs when we try add the ‘Fulano’ as a customer:

INSERT INTO CUSTOMER (id, name, dob, registration_date, contact)
SELECT id, name, dob, '2023-10-17', 'contact@example.com'
FROM person
WHERE id = 1;

The 'CUSTOMER' table look like this:


However, this issue arises in the 'PERSON' table:

The primary key is duplicated when I attempted to add 'Fulano' as a
customer.

After that, I attempted a slightly different approach in creating the
‘CUSTOMER’ table, as I'll show below:

CREATE TABLE customer (
  "id" int4 NOT NULL PRIMARY KEY DEFAULT nextval('person_id_seq'::regclass),
  name VARCHAR(255) NOT NULL,
  dob DATE,
  registration_date DATE,
  contact varchar(255)
) INHERITS (person);

But, when I run the same ‘INSERTS’ above, the same problem occurs with the
‘PERSON’ table:


I would like to know where I might be going wrong with these simple
queries, and reinforce that my main question is: how to create a record for
a ‘CUSTOMER’ who already exists in the ‘PERSON’ table?

A question that arose was to see that in the 'PERSON' table, there was a
duplication of the record with the same 'id', considering that 'id' is a
primary key.

I'm particularly interested in the advantages of the inheritance concept in
PostgreSQL, considering that it can be easily applied to my business rules.
I'd also like to know if inheritance is commonly used. Any insights and
recommendations would be appreciated. Thank you.

My environment:

Oracle Linux Server 8.8

Postgres 15.4

This test was also performed in this environment:

Windows 10 Pro

Postgres 16

  2. Re: Inheritance in PostgreSQL

    David G. Johnston <david.g.johnston@gmail.com> — 2023-10-17T21:56:08Z

    On Tue, Oct 17, 2023 at 2:49 PM Luis Bruno <l.brunofidelis@gmail.com> wrote:

> I'm particularly interested in the advantages of the inheritance concept
> in PostgreSQL
>

There really are none nowadays and the entire feature should be avoided,
IMO.  No one else has tried to implement it in their engines for good
reasons.  Stick with relational models in a relational database.

Also of note:
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_varchar.28n.29_by_default

I'm tempted to add "Don't use inheritance" to that page...but fortunately
it doesn't come up that often.

David J.

  3. Re: Inheritance in PostgreSQL

    Ron Johnson <ronljohnsonjr@gmail.com> — 2023-10-17T22:16:43Z

    On 10/17/23 16:42, Luis Bruno wrote:
>
> Hello, I'm in the process of developing a basic database structure that 
> utilizes inheritance as part of a test for my work. The database consists 
> of two tables: ‘PERSON' and ‘CUSTOMER' with the ‘PERSON' table serving as 
> the parent table for ‘CUSTOMER' .
>
>
> Initially, I defined the 'CREATE TABLE' statement as follows:
>
> CREATETABLEPERSON (
> idSERIAL PRIMARY KEY,
> nameVARCHAR(255) NOTNULL,
>   dob DATE
> );
>
> CREATETABLECUSTOMER (
>   registration_date DATE NOTNULL,
>   contact VARCHAR(255)
> ) INHERITS (person);
>
> INSERTINTOPERSON VALUES(1, 'Fulano', '1965-06-07');
> INSERTINTOCUSTOMER VALUES(2, 'Beltrano', '1980-10-07', '2023-10-10', 
> '5561999999999');
>
>
> With these ‘INSERTS’, we have three records, as expected:
>
>
> The problem occurs when we try add the ‘Fulano’ as a customer:
>
> INSERTINTOCUSTOMER (id, name, dob, registration_date, contact)
> SELECTid, name, dob, '2023-10-17', 'contact@example.com'
> FROMperson
> WHEREid= 1;
>
>
> The 'CUSTOMER' table look like this:
>
>
> However, this issue arises in the 'PERSON' table:
>
> The primary key is duplicated when I attempted to add 'Fulano' as a customer.
>
>
> After that, I attempted a slightly different approach in creating the 
> ‘CUSTOMER’ table, as I'll show below:
>
> CREATETABLEcustomer (
> "id"int4 NOTNULLPRIMARY KEYDEFAULTnextval('person_id_seq'::regclass),
> nameVARCHAR(255) NOTNULL,
> dob DATE,
> registration_date DATE,
> contact varchar(255)
> ) INHERITS (person);
>
>
> But, when I run the same ‘INSERTS’ above, the same problem occurs with the 
> ‘PERSON’ table:
>
>
> I would like to know where I might be going wrong with these simple 
> queries, and reinforce that my main question is: how to create a record 
> for a ‘CUSTOMER’ who already exists in the ‘PERSON’ table?
>
>
> A question that arose was to see that in the 'PERSON' table, there was a 
> duplication of the record with the same 'id', considering that 'id' is a 
> primary key.
>
>
> I'm particularly interested in the advantages of the inheritance concept 
> in PostgreSQL, considering that it can be easily applied to my business 
> rules. I'd also like to know if inheritance is commonly used. Any insights 
> and recommendations would be appreciated. Thank you.
>

Data Normalization was "invented" to eliminate this problem (and many others).

CREATE TABLE person (
     id *BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY*,
     name *TEXT*,
     dob DATE );

CREATE TABLE customer (
     id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
     person_id BIGINT REFERENCES person(id),
     registration_date DATE NOT NULL,
     contact_info TEXT);


foo=# INSERT INTO person (name, dob) VALUES ('Fulano', '1965-06-07')
foo-#     RETURNING id;
  id
----
   1
(1 row)

INSERT 0 1
foo=#
foo=# INSERT INTO customer (person_id, registration_date, contact_info)
foo-# values (1, '2023-10-10', '867-5309');
INSERT 0 1
foo=#
foo=# INSERT INTO person (name, dob) VALUES ('Beltrano', '1980-10-07')
     RETURNING id;
  id
----
   2
(1 row)

INSERT 0 1
foo=#
foo=# INSERT INTO customer (person_id, registration_date, contact_info)
values (2, '2023-10-12', '555-1212');
INSERT 0 1
foo=#
foo=# SELECT p.*, c.*
foo-# FROM person p, customer c
foo-# WHERE p.id = c.person_id;
  id |   name   |    dob     | id | person_id | registration_date | 
contact_info
----+----------+------------+----+-----------+-------------------+--------------
   1 | Fulano   | 1965-06-07 |  1 |         1 | 2023-10-10        | 867-5309
   2 | Beltrano | 1980-10-07 |  2 |         2 | 2023-10-12        | 555-1212
(2 rows)

foo=# INSERT INTO customer (person_id, registration_date, contact_info)
values (8, '2023-09-11', '(212)555-1212');
ERROR:  insert or update on table "customer" violates foreign key constraint 
"customer_person_id_fkey"
DETAIL:  Key (person_id)=(8) is not present in table "person".

-- 
Born in Arizona, moved to Babylonia.

  4. RE: Inheritance in PostgreSQL

    Jeff Laing <jeff.laing@synchronoss.com> — 2023-10-17T22:33:10Z

    “Don’t use table inheritance” IS on that page

From: David G. Johnston <david.g.johnston@gmail.com>

Also of note:
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_varchar.28n.29_by_default<https://urldefense.com/v3/__https:/wiki.postgresql.org/wiki/Don*27t_Do_This*Don.27t_use_varchar.28n.29_by_default__;JSM!!LlG_G4lo9h6Y!KSyFO_eHWquBnSPfk3cz-UpSZeUo49Ag2KedAWUQPB9lhHLsRip4f3vB5yGcUpcXq8G9iWJVJAXPdm0ZIB6-9LDxyA3dsdlJ$>

I'm tempted to add "Don't use inheritance" to that page...but fortunately it doesn't come up that often.

David J.

  5. Re: Inheritance in PostgreSQL

    David G. Johnston <david.g.johnston@gmail.com> — 2023-10-17T22:36:43Z

    On Tue, Oct 17, 2023 at 3:33 PM Jeff Laing <Jeff.Laing@synchronoss.com>
wrote:

> “Don’t use table inheritance” IS on that page
>
>
>

Indeed - oddly under "tool usage"...along with rules and, an actual tool,
psql -W

David J.

  6. Re: Inheritance in PostgreSQL

    Merlin Moncure <mmoncure@gmail.com> — 2023-10-18T01:20:04Z

    On Tue, Oct 17, 2023 at 5:36 PM Jeff Laing <Jeff.Laing@synchronoss.com>
wrote:

> “Don’t use table inheritance” IS on that page
>

Yeah, inheritance probably would have been removed a long time ago except
that it was underneath the partitioning feature.  The basic idea of
implementing polymorphic record storage essentially hacks SQL in a way it
doesn't want to be hacks, thus the feature never really worked properly.
If you want variant storage, use hstore, json, etc. or use the various
techniques that split the entity across multiple tables.

The only thing you can't really do in SQL easily without writing nasty
triggers are things like, 'this table must be linked from one and only one
of these candidate tables'.  I think the language probably ought to support
this, but I don't think postgres would unless the standard did.  I try to
avoid handwriting RI when possible in triggers, but in this case it's the
only approach that works within language constraints and can
formally validate the model.

merlin

>

  7. Re: Inheritance in PostgreSQL

    Luis Bruno <l.brunofidelis@gmail.com> — 2023-10-18T12:16:30Z

    > Thank you all for your responses. I appreciate the input on the use of
> table inheritance in PostgreSQL, and I will take your recommendations into
> consideration. The provided link is also quite useful, and I'm grateful for
> the solution provided by Ron regarding normalization. Thanks!
>

  8. Re: Inheritance in PostgreSQL

    shammat@gmx.net — 2023-10-18T12:42:17Z

    Merlin Moncure schrieb am 18.10.2023 um 03:20:
> The only thing you can't really do in SQL easily without writing
> nasty triggers are things like, 'this table must be linked from one
> and only one of these candidate tables'.  I think the language
> probably ought to support this, but I don't think postgres would
> unless the standard did.

Isn't that what assertions are intended to solve in the SQL standard?

  9. Re: Inheritance in PostgreSQL

    Merlin Moncure <mmoncure@gmail.com> — 2023-10-18T18:26:48Z

    On Wed, Oct 18, 2023 at 7:43 AM Thomas Kellerer <shammat@gmx.net> wrote:

> Merlin Moncure schrieb am 18.10.2023 um 03:20:
> > The only thing you can't really do in SQL easily without writing
> > nasty triggers are things like, 'this table must be linked from one
> > and only one of these candidate tables'.  I think the language
> > probably ought to support this, but I don't think postgres would
> > unless the standard did.
>
> Isn't that what assertions are intended to solve in the SQL standard?
>

Hm, interesting, <googles> ...maybe, maybe not.   SQL '92 -- wow.  guessing
why not:  Are they deferrable, always deferred? Doesn't seem like it,
simple inheritance cases would seem problematic otherwise, chicken/egg
conditions. This might explain why they are not in common use despite being
innovated 30 years ago.  Here is an Oracle discussion on the topic:

https://forums.oracle.com/ords/apexds/post/sql-assertions-declarative-multi-row-constraints-8418

...with the discussion implying that interaction with the transaction state
may be an unsolved problem, at least within oracle.  I suspect there may
also be challenges relating to performant implementation.  Sadly, I think
the correct approach remains to utilize complex triggers, or nothing.

merlin