Contents / Previous / Next

Inheritance

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

With inheritance, the child table receives all of the columns of its parent, plus the additional columns it defines:

CREATE TABLE table_name ( column_name_list ) INHERITS ( parent_table [, ... ] ) ]

Example:

CREATE TABLE stud_childs( age INT ) INHERITS (stud) ; CREATE workshop=# SELECT * FROM stud_childs ; id | name | semester | diploma | gender | age ----+------+----------+---------+--------+----- (0 rows) Inherited tables can be layered on top of one another.

If the parent table is referenced with an asterisk suffix, rows from the parent and all children are accessed:

SELECT * FROM stud_childs ; id | name | semester | diploma | gender | age ----+----------------+----------+---------+--------+----- 77 | "TheOnlyChild" | | bio | f | 7 SELECT * FROM stud* ; id | name | semester | diploma | gender ----+----------------+----------+---------+-------- 1 | fred | 2 | bio | m 3 | tom | 1 | bio | m 4 | john | 3 | phy | m 2 | lisa | 2 | bio | f 77 | "TheOnlyChild" | | bio | f (5 rows) stud* accesses only columns common to all tables. Because stud_child.age is not in the parent table, it is not displayed.


Original Table Name Lookup

In some cases you may wish to know which table a particular tuple originated from. There is a system column called TABLEOID in each table which can tell you the originating table. If you do a join with pg_class you can see the actual table name: SELECT s.tableoid, p.relname, s.name FROM stud s, pg_class p WHERE s.tableoid = p.oid ; tableoid | relname | name ----------+-------------+---------------- 16558 | stud | fred 16558 | stud | tom 16558 | stud | john 16558 | stud | lisa 16783 | stud_childs | "TheOnlyChild"