pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Inheritance in PostgreSQL
Date: Tue, 17 Oct 2023 17:16:43 -0500
Message-ID: <847f2a9d-86ae-49be-9b68-b806c67c81fb@gmail.com> (raw)
In-Reply-To: <CAAtYR99oHdgXg39iORJcssyfArVAjBAThToWZ2PW+Cbq1P2o9g@mail.gmail.com>
References: <CAAtYR99oHdgXg39iORJcssyfArVAjBAThToWZ2PW+Cbq1P2o9g@mail.gmail.com>

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.

view thread (9+ messages)  latest in thread

Message-ID: <847f2a9d-86ae-49be-9b68-b806c67c81fb@gmail.com>
Permalink:  ../847f2a9d-86ae-49be-9b68-b806c67c81fb@gmail.com/
Also on:    postgresql.org/message-id/847f2a9d-86ae-49be-9b68-b806c67c81fb@gmail.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: Inheritance in PostgreSQL
  In-Reply-To: <847f2a9d-86ae-49be-9b68-b806c67c81fb@gmail.com>
  References: <CAAtYR99oHdgXg39iORJcssyfArVAjBAThToWZ2PW+Cbq1P2o9g@mail.gmail.com> <847f2a9d-86ae-49be-9b68-b806c67c81fb@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox