Inheritance is a very common task in programming, and this concept is also used in the PostgreSQL database. PostgreSQL allows its users to inherit specific features of one table into another table. This concept is referred to as “Table inheritance” in Postgres. It is useful when dealing with multiple tables that share similar attributes.

This post will illustrate a detailed guide on inheriting attributes of one table to another.

How to Do Table Inheritance in PostgreSQL?

In PostgreSQL, the “table inheritance” allows us to create a hierarchy of tables, where one table(child) can inherit the structure and properties of another (parent) table. To perform table inheritance in PostgreSQL, you simply need to use the "INHERITS" keyword.

Example 1: Apply Table Inheritance in PostgreSQL

First, create a parent table named “emp” with the following structure:

CREATE TABLE emp (
e_id SERIAL PRIMARY KEY,
e_name TEXT,
e_sal INT
);

The parent table has been successfully created with the desired columns:

Now, create another table named “author” and make it a child of the “emp” table:

CREATE TABLE author (
e_bonus INT
)INHERITS (emp);

The INHERITS clause is used along with the parent table’s name to create table inheritance:

Let’s create another table named “developer” and make it a child of the “emp” table as well:

CREATE TABLE developer(
dev_bonus INT
)INHERITS (emp);

Now execute the following INSERT INTO command to insert a new record into the child tables, i.e., author and developer:

INSERT INTO author (e_name, e_sal, e_bonus)
VALUES ('John', 25000, 12000);

Execute the same query to insert a new record into the developer table:

INSERT INTO developer (e_name, e_sal, dev_bonus)
VALUES ('Joseph', 35000, 5000);

Now, execute the following “SELECT *” command to see how the TABLE INHERITANCE work in Postgres:

SELECT * FROM emp;

The above command fetches the data from the parent table, the parent table includes the records of both child tables:

Now, run the “SELECT *” command with the “author” to see its records individually:

SELECT * FROM author;

The above command fetches the data from the selected child table:

Similarly, the SELECT query can be used with any other child table to fetch its record individually:

SELECT * FROM developer;

The above-stated command fetches the records from the developer table:

Example 2: Remove Table Inheritance in PostgreSQL

Use the ALTER TABLE command with the “NO INHERIT” clause to remove the inheritance from the selected table. In the following example, the inheritance is detached from the author table:

ALTER TABLE author
NO INHERIT emp;

That’s all about applying or removing table inheritance in PostgreSQL.

Conclusion

In PostgreSQL, the “table inheritance” allows us to create a hierarchy of tables, where one table(child) can inherit the structure and properties of another (parent) table. To perform table inheritance in PostgreSQL, the "INHERITS" keyword is used while inheriting the parent table. To remove the inheritance from a specific table, use the ALTER TABLE command with the “NO INHERIT” clause. This post has illustrated how to apply or remove table inheritance in PostgreSQL.