Table inheritance creates a parent-child relationship in which a child table inherits the parent’s columns. PostgreSQL makes this easy with CREATE TABLE ... INHERITS ..., but the result can surprise designs that expect ordinary relational relationships. section 4.0 section 4.1
Events and meetings
A calendar can define events with id, scheduled_time, and status, then create meetings with additional email and location columns using INHERITS (events). PostgreSQL reports the inheritance relationship, and inserting a meeting also produces a row in events containing the inherited fields. section 4.1
Listing 4.1 Table inheritance
CREATE SCHEMA calendar;
SET search_path = calendar, "$user", public;
CREATE TABLE events (
id int PRIMARY KEY,
scheduled_time timestamptz,
status smallint
);
CREATE TABLE
CREATE TABLE meetings (
invited_emails text[],
confirmed_emails text[],
location text
) INHERITS (events);
CREATE TABLE
\d events
Table "calendar.events"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
id | integer | | |
scheduled_time | timestamp with time zone | | |
status | smallint | | |
Number of child tables: 1 (Use \d+ to list them.)
\d meetings
Table "calendar.meetings"
Column | Type | Collation | Nullable | Default
------------------+------------------------+-----------+----------+---------
id | integer | | |
scheduled_time | timestamp with time zo.| | |
|.ne | | |
status | smallint | | |
invited_emails | text[] | | |
confirmed_emails | text[] | | |
location | text | | |
Inherits: events
Inspect the executable schema definition and psql descriptions for the parent and child tables. section 4.1
The tempting use case is partitioning a growing inherited table. That path is now a trap: declarative partitioning, introduced in PostgreSQL 10, is fundamentally incompatible with table inheritance. A partitioned table cannot inherit from another table, and inheritance cannot be added to a partitioned table. section 4.1
Prefer these operational conclusions:
- Use declarative table partitioning for managing and maintaining large tables rather than building a partitioning scheme with inheritance. section 4.1
- Model parent-child relationships with foreign keys and triggers when needed. section 4.5
- Recover an inherited design by creating a LIKE-based replacement, copying rows, adding an event identifier and foreign key, then renaming the tables. section 4.1
