Table Inheritance

What is Table Inheritance?

PostgreSQL supports table inheritance where a child table inherits all columns and some constraints from a parent table. Querying the parent table includes rows from all children.

Syntax

CREATE TABLE people (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE students (
  grade CHAR(1),
  enrollment_date DATE
) INHERITS (people);

CREATE TABLE employees (
  department VARCHAR(50),
  hire_date DATE
) INHERITS (people);

When to Use

• Modeling "is-a" relationships (a student is a person)
• Partitioning data across related tables (now prefer declarative partitioning)
• Sharing common columns across similar table types

Key Points

1. Column Inheritance — Child tables automatically have all parent columns.
2. Query Parent — SELECT * FROM people includes rows from students and employees.
3. ONLY Keyword — SELECT * FROM ONLY people queries only the parent, excluding children.
4. Constraints — CHECK constraints are inherited. UNIQUE and FK constraints are not.
5. Modern Alternative — For partitioning, use declarative partitioning (PARTITION BY) instead.
6. PostgreSQL Specific — Table inheritance is unique to PostgreSQL.