Mastering Table Inheritance in YugabyteDB YSQL

YugabyteDB YSQL supports PostgreSQL-style table inheritance using the INHERITS keyword—a powerful tool to streamline schema design and avoid redundancy

Why Use Table Inheritance?

DRY Schema: Share common columns and constraints among related tables.
Polymorphism: Perform queries across parent and children using ONLY or blanket selects.
Cleaner Maintenance: Updating shared columns happens in one place… your parent table.

Real-World Example: Pet Management App.

Let’s build a database for a veterinary clinic tracking various pets:

Step 1: Create the Parent Table

				
					CREATE TABLE pets (
  pet_id        UUID PRIMARY KEY,
  name          TEXT NOT NULL,
  admitted_date DATE NOT NULL DEFAULT CURRENT_DATE,
  is_vaccinated BOOLEAN NOT NULL DEFAULT FALSE,
  CHECK (name <> '')
);

Step 2: Define Child Tables

DOGS Table:

				
					CREATE TABLE dogs (
  breed     TEXT NOT NULL,
  bark_level INTEGER NOT NULL CHECK (bark_level BETWEEN 1 AND 10),
  PRIMARY KEY (pet_id, breed)
) INHERITS (pets);

CATS Table:

				
					CREATE TABLE cats (
  color         TEXT NOT NULL,
  night_vision  BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (pet_id)
) INHERITS (pets);

All dog and cat records automatically include pet_id, name, and other common columns, not needing to redefine them.

Sample Data Inserts

Let’s populate the pets, dogs, and cats tables with some example data.

Insert into Parent Table (pets):

				
					-- A generic pet (not a dog or cat)
INSERT INTO pets (pet_id, name, is_vaccinated)
VALUES ('00000000-0000-0000-0000-000000000001', 'Goldie', TRUE);

Insert into dogs:

				
					INSERT INTO dogs (pet_id, name, is_vaccinated, breed, bark_level)
VALUES 
  ('00000000-0000-0000-0000-000000000002', 'Rex', TRUE, 'German Shepherd', 8),
  ('00000000-0000-0000-0000-000000000003', 'Buddy', FALSE, 'Beagle', 5);

Insert into cats:

				
					INSERT INTO cats (pet_id, name, is_vaccinated, color, night_vision)
VALUES 
  ('00000000-0000-0000-0000-000000000004', 'Whiskers', TRUE, 'Gray', TRUE),
  ('00000000-0000-0000-0000-000000000005', 'Luna', FALSE, 'Black', TRUE);

How It Works

Shared structure: Both dogs and cats inherit automatic columns and constraints from pets, like is_vaccinated and the name <> '' check.
Independent column rules: Child tables can add extra fields and unique constraints.
Polymorphic queries: Query all pets together—even if they exist only in a child table.

Return all entries across pets, dogs, and cats:

				
					SELECT * FROM pets;

Example:

				
					yugabyte=# SELECT * FROM pets ORDER BY pet_id;
                pet_id                |   name   | admitted_date | is_vaccinated
--------------------------------------+----------+---------------+---------------
 00000000-0000-0000-0000-000000000001 | Goldie   | 2025-07-08    | t
 00000000-0000-0000-0000-000000000002 | Rex      | 2025-07-08    | t
 00000000-0000-0000-0000-000000000003 | Buddy    | 2025-07-08    | f
 00000000-0000-0000-0000-000000000004 | Whiskers | 2025-07-08    | t
 00000000-0000-0000-0000-000000000005 | Luna     | 2025-07-08    | f
(5 rows)

To focus just on base table data, use:

				
					SELECT * FROM ONLY pets;

Example:

				
					yugabyte=# SELECT * FROM ONLY pets;
                pet_id                |  name  | admitted_date | is_vaccinated
--------------------------------------+--------+---------------+---------------
 00000000-0000-0000-0000-000000000001 | Goldie | 2025-07-08    | t
(1 row)

Notes

Starting in YugabyteDB 2025.1, the Table Inheritence feature will be enabled by default.
In early version, enable Table Inheritence via the T-Server gFlag ysql_enable_inheritance
Check the official YugabyteDB doc pages for tips on handling Schema changes and current Limitions.

Final thoughts

Table inheritance in YugabyteDB’s YSQL is a clean, DRY, and powerful schema design pattern—especially when you manage overlapping entities. Once your foundation (pets) is set, child-specific tables like dogs and cats inherit valuable defaults and constraints, while still allowing specialization and polymorphic querying.

Have Fun!