Inheritance

Eugenia Kuzina

The inheritance allows you to create a child table that get column descriptions from a parent table.

For example, we need to create the following tables:

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    author VARCHAR(50) NOT NULL);

CREATE TABLE shelves (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    author VARCHAR(50) NOT NULL,
    location VARCHAR(10) NOT NULL);

The second table has the same structure as the first one but includes an additional column. You can use the INHERITS clause to create the second table:

CREATE TABLE shelves (
    location VARCHAR(10) NOT NULL
) INHERITS (books);

In this case, shelves inherits all columns (book_id, title, and author) from its parent books table. The shelves table also has an additional column — location.

The use of INHERITS creates a persistent relationship between a new child table and a parent table. Parent schema modifications propagate to the child table, and the data of the child table is included in scans of the parent.

Add data to the tables:

INSERT INTO books (title, author) VALUES
('Hyperion', 'Dan Simmons'),
('1984', 'George Orwell');

INSERT INTO shelves (title, author, location) VALUES
('The Time Machine', 'Herbert George Wells', 'B32'),
('The Great Gatsby', 'F. Scott Fitzgerald', 'C14');

Select data from books:

SELECT * FROM books;

The result also includes rows of the shelves table:

 book_id |      title       |        author
---------+------------------+----------------------
       1 | Hyperion         | Dan Simmons
       2 | 1984             | George Orwell
       3 | The Great Gatsby | F. Scott Fitzgerald
       4 | The Time Machine | Herbert George Wells

The query returns all rows of the parent table and its descendant.

To return only the parent table rows, use the ONLY keyword:

SELECT * FROM ONLY books;

The result:

 book_id |  title   |    author
---------+----------+---------------
       1 | Hyperion | Dan Simmons
       2 | 1984     | George Orwell

You can use the tableoid system column and data from the pg_class catalog to determine from which table each row:

SELECT p.relname, b.title
FROM books b, pg_class p
WHERE b.tableoid = p.oid;

The result:

 relname |      title
---------+------------------
 books   | Hyperion
 books   | 1984
 shelves | The Great Gatsby
 shelves | The Time Machine

Parent tables can be ordinary tables or foreign tables. A table can inherit columns from more than one table. If the same column name exists in more than one parent table, an error is reported unless the data types of the columns match each of the parent tables. If there is no conflict, the duplicate columns are merged into a single column in the new table.

If the column name list of the child table contains a column name that is also inherited from a parent, the data type of these columns must match, and the column definitions are merged into one. If the new table explicitly specifies a default value for the column, this default overrides any defaults from inherited declarations.

Child tables inherit all CHECK and NOT NULL constraints on the parent table unless a constraint description contains the NO INHERIT clause. Other types of constraints (UNIQUE, PRIMARY KEY, and FOREIGN KEY) are not inherited.

Columns or CHECK constraints of child tables cannot be dropped or altered if they are inherited from parent tables.

You can also add a new parent relationship to a table, which is already defined in a compatible way. A child table must already include columns with the same names and types as the columns of the parent. Use the INHERIT option of the ALTER TABLE command for this purpose:

CREATE TABLE books2 (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    author VARCHAR(50) NOT NULL);

CREATE TABLE shelves2 (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    author VARCHAR(50) NOT NULL,
    location VARCHAR(10) NOT NULL);

ALTER TABLE shelves2 INHERIT books2;

Operations with tables created by inheritance have the following specifics:

  • The SELECT, UPDATE, and DELETE commands that are executed on the parent table also perform operations on descendant tables. You can use the ONLY keyword to avoid this behavior.

    The INSERT and COPY commands executed on the parent do not operate with descendants.

  • PostgreSQL does not check access permissions for the child tables when processing them with their parent table. For example, granting the UPDATE permission on the books table implies the permission to update rows in the shelves table, when they are accessed through books.

  • PostgreSQL cannot drop a parent table while any of its children remain. If you wish to remove a table and all of its descendants, drop the parent table with the CASCADE option:

    DROP TABLE books CASCADE;
Found a mistake? Seleсt text and press Ctrl+Enter to report it