Postgres Pro
Downloads
Home   >   mailing lists

Inheritance in PostgreSQL - Mailing list pgsql-general

From Luis Bruno
Subject Inheritance in PostgreSQL
Date
Msg-id CAAtYR99oHdgXg39iORJcssyfArVAjBAThToWZ2PW+Cbq1P2o9g@mail.gmail.com
Whole thread Raw
Responses Re: Inheritance in PostgreSQL  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Inheritance in PostgreSQL  (Ron <ronljohnsonjr@gmail.com>)
List pgsql-general
Tree view

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

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Index based search have issues with 11.20
Next
From: "David G. Johnston"
Date:
Subject: Re: Inheritance in PostgreSQL