SQL INHERITS

What is SQL INHERITS in PostgreSQL?

Creates a child table that automatically contains all columns and most constraints of one or more parent tables.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL INHERITS: PostgreSQL, Amazon Aurora PostgreSQL, YugabyteDB. Not supported in MySQL, SQL Server, Oracle, SQLite, or standard SQL.

SQL INHERITS Full Explanation

INHERITS is a PostgreSQL specific clause used in CREATE TABLE and ALTER TABLE statements to implement table inheritance. A child table defined with INHERITS physically stores its own rows but automatically includes the column definitions and CHECK constraints of each listed parent. Queries against a parent can optionally return rows from its descendants when the configuration setting enable_inheritances is on or when the SQL keyword ONLY is omitted. Inherited columns keep their data types and default values unless overridden in the child. Indexes, foreign keys, triggers, and NOT NULL constraints are not inherited and must be re-declared on the child if required. Inheritance is different from partitioning: it is purely a schema design feature, not an automatic data placement mechanism. Dropping a parent does not drop its children, but altering a parent to add or drop a column is immediately visible in all descendants.

SQL INHERITS Syntax

CREATE TABLE child_table
(
    column_definitions,
    [ table_constraints ]
) INHERITS (parent_table1 [, parent_table2 ...]);

-- Add or remove inheritance later
ALTER TABLE child_table INHERITS parent_table;   -- add
ALTER TABLE child_table NO INHERITS parent_table; -- remove

SQL INHERITS Parameters

child_table_name identifier - Name of the table being created or altered
column_definitions list - Columns specific to the child table
parent_table_list (list) - One or more existing tables to inherit from

Example Queries Using SQL INHERITS

-- Basic inheritance
CREATE TABLE accounts (
    account_id   serial PRIMARY KEY,
    opened_at    timestamp DEFAULT now(),
    balance      numeric CHECK (balance >= 0)
);

CREATE TABLE checking_accounts (
    overdraft_limit numeric DEFAULT 0
) INHERITS (accounts);

-- Query parent and children together
SELECT account_id, balance FROM accounts;

-- Query only the parent rows
SELECT account_id, balance FROM ONLY accounts;

-- Add inheritance after creation
CREATE TABLE savings_accounts (
    interest_rate numeric
);
ALTER TABLE savings_accounts INHERITS accounts;

Expected Output Using SQL INHERITS

checking_accounts and savings_accounts now contain account_id, opened_at, and balance columns from accounts in addition to their own fields
Queries on accounts without the ONLY keyword will return rows from all three tables

Use Cases with SQL INHERITS

Model shared attributes across multiple entity tables while allowing per-table extensions
Implement simple single-table polymorphism without joins
Maintain historical or archival tables that share a schema with an active table

Common Mistakes with SQL INHERITS

Assuming indexes and foreign keys are inherited automatically
Forgetting to use ONLY when you want to exclude child rows from a query
Thinking INHERITS is the same as declarative partitioning (it is not optimized for partition pruning)

First Introduced In

PostgreSQL 6.5

Frequently Asked Questions

What columns are inherited?

All columns and their default values from each parent are automatically present in the child. You can add extra columns in the child as needed.

Are CHECK constraints copied?

Yes. Every CHECK constraint defined on a parent is enforced on the child unless it is explicitly dropped from the child.

Can a table inherit from multiple parents?

Yes. PostgreSQL supports multiple inheritance, and the child will include the combined columns and CHECK constraints from all parents. Conflicting column names are not allowed.

How do I remove inheritance later?

Use ALTER TABLE child_table NO INHERITS parent_table; to detach the child from the specified parent.