From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Inheritance in PostgreSQL |
Date: | 2023-10-17 22:16:43 |
Message-ID: | 847f2a9d-86ae-49be-9b68-b806c67c81fb@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)example(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Laing | 2023-10-17 22:33:10 | RE: Inheritance in PostgreSQL |
Previous Message | David G. Johnston | 2023-10-17 21:56:08 | Re: Inheritance in PostgreSQL |