PostgreSQL offers a unique and powerful feature called table inheritance, allowing you to create a hierarchy of tables where child tables inherit the schema of their parent table. While often misunderstood or underutilized, table inheritance can be a fantastic tool for organizing similar data, reducing redundancy, and simplifying certain types of queries.
What is Table Inheritance?
At its core, table inheritance in PostgreSQL means that a child table "inherits" the column definitions, NOT NULL constraints, and CHECK constraints from its parent table. This creates a logical parent-child relationship at the schema level. When you query the parent table, by default, PostgreSQL will return rows from the parent table and all its descendant child tables.
Think of it like this:
* Parent Table: Defines the common attributes shared by a group of entities.
* Child Tables: Extend the parent's definition with their own specific attributes, while still possessing all the parent's attributes.
How to Create and Use Table Inheritance
Let's illustrate with an example. Imagine we're building a system to manage different types of products in an e-commerce store. All products have a name, price, and description, but some are physical goods with weight and dimensions, while others are digital goods with a download URL.
Step 1: Create the Parent Table
First, we define our generic products table:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
description TEXT
);
Step 2: Create Child Tables using INHERITS
Now, let's create our child tables, physical_products and digital_products, inheriting from products:
CREATE TABLE physical_products (
weight NUMERIC(10, 3) NOT NULL CHECK (weight > 0),
dimensions VARCHAR(100)
) INHERITS (products);
CREATE TABLE digital_products (
download_url VARCHAR(255) NOT NULL,
file_size_mb INT CHECK (file_size_mb > 0)
) INHERITS (products);
Key Points:
Notice the INHERITS (products) clause. This is the magic that establishes the inheritance relationship.
Child tables automatically get product_id, name, price, and description columns, along with the NOT NULL and CHECK constraints defined on products.
Step 3: Inserting Data
You insert data into the specific child tables (or the parent table if it's a generic product).
-- Insert into physical_products
INSERT INTO physical_products (name, price, description, weight, dimensions)
VALUES ('Laptop Pro X', 1200.00, 'High-performance laptop', 2.5, '35x25x2 cm');
-- Insert into digital_products
INSERT INTO digital_products (name, price, description, download_url, file_size_mb)
VALUES ('Ebook: SQL Mastery', 29.99, 'Comprehensive guide to SQL', 'https://example.com/sql-mastery.pdf', 10);
-- You can also insert directly into the parent table for generic products
INSERT INTO products (name, price, description)
VALUES ('Gift Card', 50.00, 'A generic gift card');
Step 4: Querying Data
This is where one of the main benefits of inheritance shines.
Querying the Parent Table (Includes Children):
SELECT product_id, name, price FROM products;
This query will return results from products, physical_products, and digital_products. You'll see all laptops, chairs, ebooks, and software licenses, plus the gift card.
Querying Only the Parent Table:
If you only want data specifically from the products table itself (and not its descendants), use the ONLY keyword:
SELECT product_id, name, price FROM ONLY products;
This will only show the 'Gift Card' entry.
Querying a Specific Child Table:
To get data only for physical products:
SELECT product_id, name, price, weight, dimensions FROM physical_products;
Features and Considerations of Table Inheritance
* Inherited Columns and Constraints: Child tables automatically get all parent columns, NOT NULL, and CHECK constraints. PRIMARY KEY and UNIQUE constraints are NOT inherited and must be defined explicitly on child tables if needed. FOREIGN KEY constraints are also NOT inherited.
* Indexing: Indexes on parent tables are not automatically inherited by children, but PostgreSQL's query planner can intelligently use child table indexes when querying the parent.
* ALTER TABLE Operations: Changes to the parent table's columns (add, drop, type change) automatically propagate to all child tables.
* TRUNCATE and DROP TABLE: By default, these operations on a parent table affect all child tables. Use ONLY (e.g., TRUNCATE ONLY parent_table;) to affect only the parent.
* Data Consistency: Maintaining complex referential integrity without inherited primary/foreign keys can require triggers or application logic.
* UNION ALL vs. Inheritance: Inheritance automatically includes child rows in parent queries, propagates schema changes, and offers potential query planner optimizations, unlike a simple UNION ALL.
Conclusion
PostgreSQL's table inheritance is a powerful and elegant feature for structuring your database schemas. By understanding its strengths and limitations, you can leverage it to create more organized, flexible, and maintainable data models, especially for scenarios where you have varying types of entities sharing common core characteristics. While declarative partitioning has taken over its role in large-scale data management, table inheritance remains a valuable tool for logical schema design. Explore advanced backup strategies and discover how to Master PostgreSQL Backups with pgBackRest, utilizing its powerful features for seamless data protection and recovery.