Inheritance
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
, andDELETE
commands that are executed on the parent table also perform operations on descendant tables. You can use theONLY
keyword to avoid this behavior.The
INSERT
andCOPY
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 thebooks
table implies the permission to update rows in theshelves
table, when they are accessed throughbooks
. -
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;