Working with Table Inheritance

1. Creating Parent Table

CREATE TABLE vehicle (id serial PRIMARY KEY, make text, model text);

CREATE TABLE car (doors int) INHERITS (vehicle);
CREATE TABLE truck (capacity_kg int) INHERITS (vehicle);

SELECT * FROM vehicle;     -- includes car & truck rows

SELECT * FROM ONLY vehicle;

Constraint	Inherited?
NOT NULL	Yes
CHECK	Yes (marked inherited)
PRIMARY KEY	No (child needs own)
UNIQUE	No
FOREIGN KEY	No (parent-side FKs don't see children)

Warning: A UNIQUE on parent is not enforced across children. Same value may exist in car and truck.

CREATE TABLE car (
   model text NOT NULL,           -- tightens parent
   doors int
) INHERITS (vehicle);

ALTER TABLE vehicle ADD COLUMN year int;
-- propagates to all descendants; use ONLY to skip
ALTER TABLE ONLY vehicle ADD COLUMN tag text;

DROP TABLE car;
-- or detach: ALTER TABLE car NO INHERIT vehicle;