How to Use Table Inheritance in PostgreSQL

Key Insights

PostgreSQL’s table inheritance lets child tables automatically inherit columns from parent tables, but critical constraints like PRIMARY KEY and FOREIGN KEY are not inherited—you must define them explicitly on each child table.
Querying a parent table includes all child table data by default; use the ONLY keyword to query just the parent table, which is essential for performance when you don’t need the full hierarchy.
Declarative partitioning has largely replaced table inheritance for data partitioning use cases, but inheritance remains valuable for modeling true polymorphic relationships where different entity types share common attributes.

Introduction to Table Inheritance

PostgreSQL’s table inheritance allows you to create child tables that automatically inherit the column structure of parent tables. This feature enables you to model hierarchical relationships where multiple entity types share common attributes but also have their own specific fields.

Unlike object-oriented programming inheritance, PostgreSQL table inheritance is primarily a schema organization tool. It doesn’t provide method inheritance or polymorphic behavior in the OOP sense. Instead, it gives you a way to query across related tables with a shared structure while maintaining separate storage for each table type.

Use table inheritance when you have entities that share a common set of attributes but differ in their specific properties. Classic examples include vehicle types (cars, motorcycles, trucks), content types (articles, videos, podcasts), or employee types (full-time, contractors, interns). However, be aware that PostgreSQL’s declarative partitioning has superseded inheritance for pure data partitioning scenarios.

Creating Parent and Child Tables

The basic syntax for table inheritance uses the INHERITS clause when creating child tables. The parent table defines the shared column structure, and child tables automatically receive those columns plus any additional columns you define.

-- Create the parent table
CREATE TABLE vehicles (
    vehicle_id SERIAL,
    make VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    year INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create child tables that inherit from vehicles
CREATE TABLE cars (
    num_doors INTEGER NOT NULL,
    trunk_capacity_liters INTEGER
) INHERITS (vehicles);

CREATE TABLE motorcycles (
    engine_cc INTEGER NOT NULL,
    has_sidecar BOOLEAN DEFAULT FALSE
) INHERITS (vehicles);

Child tables automatically receive all columns from the parent. In this example, both cars and motorcycles have vehicle_id, make, model, year, and created_at columns, plus their own specific columns.

You can verify the structure using \d in psql:

\d cars
-- Shows: vehicle_id, make, model, year, created_at, num_doors, trunk_capacity_liters

Querying Inherited Tables

One of the most powerful features of table inheritance is how queries against the parent table automatically include data from all child tables. This allows you to query the entire hierarchy with a single SELECT statement.

-- Insert data into child tables
INSERT INTO cars (make, model, year, num_doors, trunk_capacity_liters)
VALUES ('Toyota', 'Camry', 2023, 4, 425);

INSERT INTO motorcycles (make, model, year, engine_cc, has_sidecar)
VALUES ('Harley-Davidson', 'Street 750', 2023, 750, FALSE);

-- Query the parent table - includes all child data
SELECT make, model, year FROM vehicles;
-- Returns both the Toyota Camry and Harley-Davidson Street 750

-- Use ONLY to query just the parent table
SELECT make, model, year FROM ONLY vehicles;
-- Returns no rows (parent table is empty)

-- Query specific child tables
SELECT make, model, num_doors FROM cars;
-- Returns only the Toyota Camry

The ONLY keyword is crucial for performance. Without it, PostgreSQL scans all child tables even when you only need parent table data. This becomes expensive with many child tables.

PostgreSQL adds a hidden tableoid column to every row that identifies which table it came from:

SELECT tableoid::regclass, make, model FROM vehicles;
-- Shows: cars | Toyota | Camry
--        motorcycles | Harley-Davidson | Street 750

This is useful when you need to determine the specific type of each row in a polymorphic query.

Constraints and Inheritance

Understanding constraint inheritance is critical because the behavior is inconsistent and can lead to data integrity issues if you’re not careful.

CHECK constraints are inherited from parent to child tables:

CREATE TABLE products (
    product_id SERIAL,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10,2) CHECK (price > 0)
);

CREATE TABLE books (
    isbn VARCHAR(13),
    author VARCHAR(200)
) INHERITS (products);

-- This fails due to inherited CHECK constraint
INSERT INTO books (name, price, isbn, author)
VALUES ('PostgreSQL Guide', -10.00, '9781234567890', 'John Doe');
-- ERROR: new row violates check constraint "products_price_check"

NOT NULL and DEFAULT constraints are also inherited and work as expected.

However, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are NOT inherited. This is a major limitation that requires explicit handling:

-- Add PRIMARY KEY to parent (does NOT propagate to children)
ALTER TABLE vehicles ADD PRIMARY KEY (vehicle_id);

-- Must add PRIMARY KEY to each child table separately
ALTER TABLE cars ADD PRIMARY KEY (vehicle_id);
ALTER TABLE motorcycles ADD PRIMARY KEY (vehicle_id);

-- Foreign keys must also be added individually
CREATE TABLE maintenance_records (
    record_id SERIAL PRIMARY KEY,
    vehicle_id INTEGER NOT NULL,
    service_date DATE NOT NULL,
    description TEXT
);

-- This only works for the parent table
ALTER TABLE maintenance_records 
ADD FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id);

-- For proper referential integrity, you need separate foreign keys
-- or accept that foreign keys only work with specific child tables

This constraint limitation is the biggest gotcha with table inheritance and why many developers avoid it for production systems.

Partitioning vs. Inheritance

PostgreSQL 10 introduced declarative partitioning, which has largely replaced table inheritance for data partitioning scenarios. Declarative partitioning offers better performance, automatic constraint exclusion, and proper constraint handling.

Use declarative partitioning when:

You’re splitting a large table by date ranges, hash values, or list values
All partitions have identical schemas
You need optimal query performance with partition pruning
You want automatic routing of INSERT statements

Use table inheritance when:

You have genuinely different entity types with shared attributes
Child tables have different columns beyond the parent’s columns
You need true polymorphic associations in your data model
You’re modeling a type hierarchy (vehicles, content types, user roles)

For pure partitioning needs, always choose declarative partitioning. Here’s a quick comparison:

-- Declarative partitioning (preferred for time-series data)
CREATE TABLE measurements (
    measurement_id BIGSERIAL,
    sensor_id INTEGER NOT NULL,
    value NUMERIC,
    measured_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (measured_at);

CREATE TABLE measurements_2024_01 
PARTITION OF measurements 
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Table inheritance (for polymorphic entities)
CREATE TABLE content (
    content_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE articles (
    body TEXT,
    word_count INTEGER
) INHERITS (content);

CREATE TABLE videos (
    duration_seconds INTEGER,
    video_url VARCHAR(500)
) INHERITS (content);

Common Pitfalls and Best Practices

Indexes must be created on each table individually. Creating an index on the parent table does not automatically create indexes on child tables:

-- Create index on parent
CREATE INDEX idx_vehicles_year ON vehicles(year);

-- Must create indexes on each child table
CREATE INDEX idx_cars_year ON cars(year);
CREATE INDEX idx_motorcycles_year ON motorcycles(year);

-- Helper function to create indexes on all child tables
DO $$
DECLARE
    child_table TEXT;
BEGIN
    FOR child_table IN 
        SELECT tablename FROM pg_tables 
        WHERE schemaname = 'public' 
        AND tablename IN ('cars', 'motorcycles')
    LOOP
        EXECUTE format('CREATE INDEX idx_%s_year ON %s(year)', 
                      child_table, child_table);
    END LOOP;
END $$;

UPDATE and DELETE operations on parent tables affect all child tables, which can cause performance issues:

-- This scans all child tables
UPDATE vehicles SET make = 'TOYOTA' WHERE make = 'Toyota';

-- Better: update specific child tables when possible
UPDATE cars SET make = 'TOYOTA' WHERE make = 'Toyota';
UPDATE motorcycles SET make = 'TOYOTA' WHERE make = 'Toyota';

Schema migrations become complex because you must alter parent and child tables separately for non-inherited constraints. Use migration scripts that handle the entire hierarchy.

Practical Use Case

Here’s a complete implementation of a content management system using table inheritance:

-- Parent table for all content types
CREATE TABLE content (
    content_id SERIAL,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL,
    author_id INTEGER NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CHECK (status IN ('draft', 'published', 'archived'))
);

-- Child tables for specific content types
CREATE TABLE articles (
    body TEXT NOT NULL,
    word_count INTEGER,
    reading_time_minutes INTEGER
) INHERITS (content);

CREATE TABLE videos (
    video_url VARCHAR(500) NOT NULL,
    duration_seconds INTEGER NOT NULL,
    thumbnail_url VARCHAR(500)
) INHERITS (content);

-- Add primary keys to each table
ALTER TABLE content ADD PRIMARY KEY (content_id);
ALTER TABLE articles ADD PRIMARY KEY (content_id);
ALTER TABLE videos ADD PRIMARY KEY (content_id);

-- Create indexes on all tables
CREATE INDEX idx_content_status ON content(status);
CREATE INDEX idx_articles_status ON articles(status);
CREATE INDEX idx_videos_status ON videos(status);

-- Insert content
INSERT INTO articles (title, slug, author_id, body, word_count)
VALUES ('PostgreSQL Inheritance', 'postgresql-inheritance', 1, 
        'Full article text here...', 1200);

INSERT INTO videos (title, slug, author_id, video_url, duration_seconds)
VALUES ('PostgreSQL Tutorial', 'postgresql-tutorial', 1,
        'https://example.com/video.mp4', 1800);

-- Query all published content
SELECT content_id, title, tableoid::regclass AS content_type, published_at
FROM content
WHERE status = 'published'
ORDER BY published_at DESC;

-- Query only articles
SELECT content_id, title, word_count, reading_time_minutes
FROM articles
WHERE status = 'published';

-- Update specific content type
UPDATE videos 
SET thumbnail_url = 'https://example.com/thumb.jpg'
WHERE content_id = 2;

This implementation provides a clean way to manage different content types while maintaining a unified interface for common operations like listing all published content or filtering by author.

Table inheritance in PostgreSQL is a powerful but specialized feature. Use it when you genuinely need polymorphic entity modeling, but reach for declarative partitioning for data partitioning scenarios. Always remember to handle constraints and indexes explicitly on child tables, and be prepared for the added complexity in schema management.