Inheritance

Inheritance allows the creation of a new table related to an existing table. Figure  shows the creation of an inherited table.

 

        test=> CREATE TABLE parent_test (col1 INTEGER);
        CREATE
        test=> CREATE TABLE child_test (col2 INTEGER) INHERITS (parent_test);
        CREATE
        test=> \d parent_test
                 Table "parent_test"
         Attribute |  Type   | Modifier 
        -----------+---------+----------
         col1      | integer | 
         
        test=> \d child_test
                 Table "child_test"
         Attribute |  Type   | Modifier 
        -----------+---------+----------
         col1      | integer | 
         col2      | integer |   

With inheritance, the child table receives all of the columns of its parent, plus the additional columns it defines. In the example, child_test gets col1 from parent_test, plus the column col2.

Inheritance also links rows in parent and child tables. If the parent table is referenced with an asterisk suffix, rows from the parent and all children are accessed. Figure  shows insertion into two tables related by inheritance.

 

        test=> INSERT INTO parent_test VALUES (1);
        INSERT 18837 1
        test=> INSERT INTO child_test VALUES (2,3);
        INSERT 18838 1
        test=> SELECT * FROM parent_test;
         col1 
        ------
            1
        (1 row) 
         
        test=> SELECT * FROM child_test;
         col1 | col2 
        ------+------
            2 |    3
        (1 row) 
         
        test=> SELECT * FROM parent_test*;
         col1 
        ------
            1
            2
        (2 rows)  

In the figure, parent_test accesses only the parent_test rows, but parent_test* accesses both parent_test and child_test rows. That is, parent_test* accesses only columns common to all tables. Because child_test.col2 is not in the parent table, it is not displayed. Figure  shows that inherited tables can be layered on top of one another.  

        test=> CREATE TABLE grandchild_test (col3 INTEGER) INHERITS (child_test);
        CREATE
        test=> INSERT INTO grandchild_test VALUES (4, 5, 6);
        INSERT 18853 1
        test=> SELECT * FROM parent_test*;
         col1 
        ------
            1
            2
            4
        (3 rows) 
         
        test=> SELECT * FROM child_test*;
         col1 | col2 
        ------+------
            2 |    3
            4 |    5
        (2 rows)  

Consider a practical example that records information about employees and managers. The table employee can hold information about nonmanagerial employees; the table manager can hold information about managers. The manager table can inherit all the columns from employee and have additional columns as well. You can then access nonmanagerial employees using employee, managers using manager, and all employees including managers using employee*. 

POSTGRESQL release 7.1 and later automatically accesses any inherited tables. An asterisk is not needed after the table name. The keyword ONLY is used to prevent inherited table access.