PostgreSQL Inheritance

intriguedrishi

Hrishikesh Mallick

Posted on March 30, 2023

PostgreSQL Inheritance

PostgreSQL has table inheritance feature. This concept is from object-oriented databases. In PostgresSQL a table can inherit from one or more other tables. Child table inherits column(s) along with all check constraints and not null constraints from one or more parent tables.
Inheritance allows a table to inherit some of its column attributes from one or more other tables, creating a parent-child relationship. This causes the child table to have each of the same columns and constraints as its inherited table (or tables), as well as its own defined columns.
When performing a query on an inherited table, the query can be instructed to retrieve either all rows of a table and its descendants, or just the rows in the parent table itself. The child table, on the other hand, will never return rows from its parent.

CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- (in ft)
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);
Enter fullscreen mode Exit fullscreen mode

In this case, a row of capitals inherits all attributes (name, population, and altitude) from its parent, cities. The type of the attribute name is text, a native PostgreSQL type for variable length ASCII strings. The type of the attribute population is float, a native PostgreSQL type for double precision floating-point numbers. State capitals have an extra attribute, state, that shows their state. In PostgreSQL, a table can inherit from zero or more other tables, and a query can reference either all rows of a table or all rows of a table plus all of its descendants.

Table inheritance is, to be sure, a power tool and thus something to use only when it brings an overall reduction in complexity to the design. Moreover, the current documentation doesn't provide a lot of guidance regarding what the tool actually helps with and where are the performance costs and because inheritance sits orthogonal to relational design, working this out individually is very difficult.

When a table is queried, by default, all child tables are also queried and their results appended to the result. Because of exclusion constraint processing, this takes out an ACCESS SHARE lock on all child tables at planning time. All rows are cast back to the type of the table target (in other words you get the columns of the table you queried).

Inheritance can be extended to use in a lot of places as per the user's requirement and thus is one of the most powerful features of PostgreSQL.

Want to extract all these type of benefits of PostgreSQL along with Graph Database features look at:
Apache AGE
Apache AGE Github

πŸ’– πŸ’ͺ πŸ™… 🚩
intriguedrishi
Hrishikesh Mallick

Posted on March 30, 2023

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related