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 TABLEHere, 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 TABLEHere, 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 1Check 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 | 2024Here , 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
producttable into specific product types likeclothing,electronics, andfurniture. 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 liketitle,author, andpublish_date.
- Vehicle Tracking Systems: Model different types of vehicles (e.g.,
car,bike,truck) with shared attributes such aslicense_plate,registration_date, andowner.
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…
