Table inheritance
YSQL supports table inheritance with the INHERITS clause of CREATE TABLE. A child table inherits columns and certain constraints from one or more parent tables. For the allowed differences between a parent column and a child column, and how those differences are resolved, see the PostgreSQL documentation.
What a child inherits
Columns, column defaults, check constraints, and not-null constraints propagate from a parent to its children. A check constraint marked NO INHERIT stays on the parent only.
Primary keys, unique constraints, indexes, and foreign keys do not propagate. Define them on each child.
A table can inherit from more than one parent, and a child can itself be a parent. The parent can also contain rows that are not in any child.
Queries and updates
SELECT, UPDATE, and DELETE on a parent operate on the parent and every child. ONLY restricts the statement to the named table. INSERT always inserts into the named table only; rows inserted into a parent aren't routed to a child. For an example that uses ONLY, see Queries and updates on data.
Schema changes
Adding, dropping, or altering a column on the parent propagates to every child. The result is more complex when a child already has a column of the same name and type, or inherits that column from more than one parent with differing definitions, such as NULL versus NOT NULL.
Adding or dropping a check constraint propagates to every child. Adding or dropping a primary key, unique constraint, or foreign key does not.
ONLY restricts some schema changes to the parent. ALTER TABLE ONLY accounts DROP COLUMN profit drops the column on accounts and leaves it on the children.
Limitations
- Dropping or adding a column on a parent fails when a local column of that name exists on a child. #26094
- Obtaining a row lock on a parent crashes when a child is a
file_fdwtable. #27105
For an up-to-date list, see #27949.