The 101s of Mastering Database Inheritance in PostgreSQL

PostgreSQL is renowned for its powerful features and flexibility, and one of its most intriguing capabilities is support for table inheritance. Table inheritance in PostgreSQL allows you to model hierarchical data structures directly within your database schema. This feature can simplify schema design, reduce redundancy, and enhance query efficiency. In this blog post, we’ll explore how PostgreSQL implements inheritance, its benefits, and practical examples to help you master this powerful feature.

What is Table Inheritance in PostgreSQL?

Table inheritance in PostgreSQL allows you to create a hierarchical relationship between tables. This means a table known as a child can inherit columns and constraints from another table known as a parent . This design pattern helps model real-world relationships more naturally and supports object-oriented programming concepts within your relational database.

Key Concepts:

• Parent Table: The base table from which other tables inherit. It typically contains common attributes and constraints.
• Child Table: A table that inherits from a parent table. It can have additional attributes or constraints specific to its type.

All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.

Using Inheritance in PostgreSQL

Here are the steps to create and use inheritance in PostgreSQL

Create the Parent table

Creating the parent table with the common attributes that will be inherited by child tables.

postgres=# CREATE TABLE vehicle (
    id SERIAL PRIMARY KEY,
    brand VARCHAR(50),
    model VARCHAR(50),
    year INT
);
CREATE TABLE

Here, we have created the table vehicle as the parent table with attributes that are common to all vehicles.

Create Child tables

Creating the child tables that inherit from the parent table. You use the INHERITS keyword to specify inheritance.

postgres=# CREATE TABLE car (
    num_doors INT
) INHERITS (vehicle);

CREATE TABLE bike (
    has_sidecar BOOLEAN
) INHERITS (vehicle);
CREATE TABLE
CREATE TABLE

Here, car and bike are child tables that inherit the columns from the vehicle table. Each child table also has its own additional attributes.

Insert Data into child tables

Insert data into car and bike tables

postgres=# INSERT INTO car (brand, model, year, num_doors)
VALUES ('Toyota', 'Corolla', 2023, 4);

INSERT INTO bike (brand, model, year, has_sidecar)
VALUES ('Harley-Davidson', 'Sportster', 2024, FALSE);
INSERT 0 1
INSERT 0 1

Check Data in Parent table

Checking the data in Parent table

postgres=# SELECT * FROM vehicle;
-[ RECORD 1 ]----------
id    | 1
brand | Toyota
model | Corolla
year  | 2023
-[ RECORD 2 ]----------
id    | 2
brand | Harley-Davidson
model | Sportster
year  | 2024

Here , we can see the data of both the child tables in the parent table as well.

Add and check the constraints

We will first add the constraint and check the constraints

postgres=# ALTER TABLE car ADD CONSTRAINT check_num_doors CHECK (num_doors > 0);
ALTER TABLE
postgres=# SELECT
    conname AS constraint_name,
    contype AS constraint_type,
    condeferrable,
    condeferred,
    c.relname AS table_name
FROM                    
    pg_constraint AS con
JOIN                    
    pg_class AS c ON con.conrelid = c.oid
WHERE                                    
    c.relname IN ('vehicle', 'car', 'bike');
-[ RECORD 1 ]---+----------------
constraint_name | vehicle_pkey
constraint_type | p
condeferrable   | f
condeferred     | f
table_name      | vehicle
-[ RECORD 2 ]---+----------------
constraint_name | check_num_doors
constraint_type | c
condeferrable   | f
condeferred     | f
table_name      | car

Use Cases

• E-commerce Platforms: Inherit attributes of a generic product table into specific product types like clothing, electronics, and furniture. This approach streamlines managing different product categories while retaining common attributes.

• Content Management Systems: Use inheritance to manage various content types (e.g., article, video, blog_post) that share common attributes like title, author, and publish_date.

• Vehicle Tracking Systems: Model different types of vehicles (e.g., car, bike, truck) with shared attributes such as license_plate, registration_date, and owner.

Conclusion

PostgreSQL’s table inheritance is a robust feature that enhances schema design by allowing you to model hierarchical relationships and reduce redundancy. By understanding and leveraging inheritance, you can create more organized and flexible database schemas. However, it’s essential to balance its benefits with potential complexities and performance considerations. With thoughtful design and regular performance monitoring, you can effectively use inheritance to manage and query data in PostgreSQL.

Thank you n Stay tuned…