Understanding Inheritance in PostgreSQL: A Powerful Database Feature

In PostgreSQL, inheritance is a feature that allows tables to inherit columns and properties from another (parent) table. This is somewhat similar to inheritance in OOP, where a child class inherits attributes and methods from a parent class.

How Does Inheritance Work?

Let’s start with a practical example. Suppose you want to manage different types of vehicles in a database, where all vehicles share common attributes but also have specific features.

Step-1: Create a Parent table

CREATE TABLE vehicle (
    id SERIAL PRIMARY KEY,
    make VARCHAR(50),
    model VARCHAR(50),
    year INTEGER
);
        

This vehicle table has all the common attributes of all the vehicle like bus, car,motorcycle etc

Step-2: Create child table car,bus and motorcycle  that inherit from the Parent table “vehicle”

CREATE TABLE bus (
    seating_capacity INTEGER
) INHERITS (vehicle);


CREATE TABLE motorcycle (
    type_ VARCHAR(50)
) INHERITS (vehicle);


CREATE TABLE car (
    ice TEXT
) INHERITS (vehicle);

        

In the above examples above car,bus and motorcycle tables inherit all the column from “vehicle” table

Step-3:Insert data for all the tables

INSERT INTO vehicle (make, model, year) VALUES ('General Motors', 'Sedan', 2020);


INSERT INTO bus (make, model, year, seating_capacity) VALUES ('Mercedes', 'Sprinter', 2019, 20);


INSERT INTO motorcycle (make, model, year, type_) VALUES ('Yamaha', 'YZF-R1', 2021, 'Sports');


INSERT INTO car (make, model, year, ice) VALUES ('BMW', 'BMW X1 ', 2021, 'gasoline');
        

Step-4: Querying in child table

Now run the following query for getting the data from child table

SELECT * FROM bus;
        

Output:

SELECT * FROM motorcycle;        

Output:

SELECT * FROM car;        

Output:

Now run the below query to get the data from parent table

SELECT * FROM vehicle;        

Now the output will be:

Notice how the query on the parent table retrieves data from car, bus, and motorcycle. This allows you to aggregate and manage related data more efficiently.

SELECT * FROM ONLY vehicle;        

Output: