Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qssMv-009sQG-7I for pgsql-general@arkaria.postgresql.org; Tue, 17 Oct 2023 22:16:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qssMs-00EpbG-8v for pgsql-general@arkaria.postgresql.org; Tue, 17 Oct 2023 22:16:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qssMr-00Epb8-GT for pgsql-general@lists.postgresql.org; Tue, 17 Oct 2023 22:16:50 +0000 Received: from mail-yw1-x1129.google.com ([2607:f8b0:4864:20::1129]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qssMn-000zLQ-Qb for pgsql-general@lists.postgresql.org; Tue, 17 Oct 2023 22:16:48 +0000 Received: by mail-yw1-x1129.google.com with SMTP id 00721157ae682-5a7a80a96dbso62059167b3.0 for ; Tue, 17 Oct 2023 15:16:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697581005; x=1698185805; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=VfuOxU60dfLDEGgurtlWsCXb8jk9UMbUJcibqqa+3K0=; b=cISHGXxuq+S/XOA8zqRo+gi40LyDGcR0Bb4E0Oeq74D2fntoOeyP9QIXlECFET4Seh 9C+/pxnl2gMhvBp9xxj7dx3B7J9aFXrxt1b4m6jGGXwWup4D/T7Tv62WVBaqsFh0FQLz m4ySMtXROqBXknGee6tTgrMVP6XlvRUVYHGtIMm2mV5eDOWWD27MgSJJQRGx/ep6yXGG gYi2Jf1z0D0Iyqug2aicO5LBFero8MOgbsrtFdN3ri868JTad1gHZD5T4OtYMt1sW4rq D1QPbINVjh0U+AhHqm/a1YbS/gcJVLHlr30fhIfAI7+RV/hg0IgNTsZazciRXU6DrvQ3 2l8A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697581005; x=1698185805; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=VfuOxU60dfLDEGgurtlWsCXb8jk9UMbUJcibqqa+3K0=; b=XjZuqZAIgd4sRTPeuu9Zzh+a2PV2Iutwtb1U0NiX9phW19U5rVHBFaA0HEB5lV6zuR EFdxLy8Swe9xM9VjCMfotvM9DkTzcsUl7yCLN9OP0oluPfq2k40cgUPZ7VJwX2+XaiNN 4aOzMXRrw7nrBHCS3J+JR4HH6gozZor/LY44lY+6LJ8FjefRD1dpXdgUypE1MtctYGRy UCZnNJCIeqTfvgB/cjr80mQSV9JQlCeQ9OwGTtoUDdPFJJBJIeF8wnxmL/8vXU2riSEA hljTsEM+wjdfsiMdT4dq+Qjh/vT/jCCRBhWm5QDgd4mtXB7g918Y3Ej2GzvJPYIGerOf eZaA== X-Gm-Message-State: AOJu0Yx/+1q9pDFY+Dq24M5ADQ75WmDSxFvuUCgdqdhqvkXVi6R55en4 XYj9x/AY4X7qwJO63/tCwWLgLKTYKck= X-Google-Smtp-Source: AGHT+IGml0jqbpB19MBxNyDodGzQMhl2nyEuSXtWgbMFr2Obeb/6Q4fkfpy5FG5gjfTrzK9gsLmPbw== X-Received: by 2002:a81:4814:0:b0:5a7:bfc6:96aa with SMTP id v20-20020a814814000000b005a7bfc696aamr2677060ywa.7.1697581004710; Tue, 17 Oct 2023 15:16:44 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:7c18:e478:1f0:7aca? ([2600:1700:bd2c:b810:7c18:e478:1f0:7aca]) by smtp.googlemail.com with ESMTPSA id e185-20020a8169c2000000b0057087e7691bsm976080ywc.56.2023.10.17.15.16.44 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 17 Oct 2023 15:16:44 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------v0yWVniyzY0hGgT3DjY99DiQ" Message-ID: <847f2a9d-86ae-49be-9b68-b806c67c81fb@gmail.com> Date: Tue, 17 Oct 2023 17:16:43 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Inheritance in PostgreSQL Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------v0yWVniyzY0hGgT3DjY99DiQ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------v0yWVniyzY0hGgT3DjY99DiQ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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:

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.


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.
--------------v0yWVniyzY0hGgT3DjY99DiQ--