Alright, let’s talk about table inheritance. This is one of those PostgreSQL features that sounds like an absolute dream on the whiteboard and can turn into a bit of a nightmare in production if you’re not careful. I’m not here to scare you off—it’s a powerful tool—but I am here to make sure you understand its quirks so you don’t end up cursing my name at 3 AM.

The core idea is simple: you can have a parent table and child tables that inherit its structure. This is classic object-oriented “is-a” thinking, bolted directly onto a relational database. It’s perfect for modeling situations where you have a central concept (like a “vehicle”) with specific subtypes (“car,” “truck,” “motorcycle”) that share common attributes but also have their own special columns.

The Basic Syntax: It’s All in the Family

You create a child table using the INHERITS clause. Let’s set up a classic, if slightly overused, example.

CREATE TABLE vehicles (
    id SERIAL PRIMARY KEY,
    make VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    year INT NOT NULL
);

CREATE TABLE cars (
    horsepower INT,
    trunk_space_cu_ft DECIMAL,
    -- This line is the magic:
    INHERITS (vehicles)
);

CREATE TABLE trucks (
    bed_length_ft DECIMAL,
    towing_capacity_lbs INT,
    INHERITS (vehicles)
);

Now, if you SELECT * FROM cars;, you’ll see columns id, make, model, year, horsepower, and trunk_space_cu_ft. The columns from vehicles are automatically present. This isn’t like a foreign key; it’s a true structural inheritance. When you insert a row into cars, that row is also a row in vehicles. This is the first and most important concept to grasp.

The Querying Quirk: Parental Oversight

Here’s where the designers made a choice you need to understand. By default, a query on the parent table vehicles will return rows from the parent and all its children. Check this out:

-- Inserts a row into the 'vehicles' table
INSERT INTO vehicles (make, model, year) VALUES ('Generic', 'Motors', 2023);

-- Inserts a row into the 'cars' table, which also appears in 'vehicles'
INSERT INTO cars (make, model, year, horsepower) VALUES ('Awesome', 'Roadster', 2023, 300);

-- This returns TWO rows: the generic vehicle and the car
SELECT * FROM vehicles;

This is the “polymorphic” part. A vehicle can be a generic entry or a specific car or truck. To query only the parent table, the base table without any children, you need to use the ONLY keyword.

-- This returns only the one row from the actual 'vehicles' table
SELECT * FROM ONLY vehicles;

Forgetting the ONLY keyword is a classic pitfall. You’ll be merrily querying your parent table, getting way more data than you expected, and wondering why your aggregate functions are giving you nonsense numbers. Always ask yourself: “Do I want the whole family, or just the parent?”

The Gotchas: Where the Dream Meets Reality

This elegance comes with some serious rough edges that you must plan for.

  1. Primary Keys and Uniqueness are a Joke: There is no automatic uniqueness constraint across the entire inheritance hierarchy. The id column in vehicles and cars are not automatically kept unique. You could absolutely insert a car with the same id as a truck. If you need a globally unique ID, you must manage it yourself with a central sequence or use UUIDs. This is, frankly, the biggest reason many people avoid table inheritance for serious applications.

  2. Foreign Keys are Not Inherited: A foreign key reference to the parent table vehicles will happily reference a row in any child table. But a foreign key reference from a child table is not automatically created on the parent. They are separate tables in the system catalogs, and this breaks the abstraction.

  3. Beware of REFERENCES: If you have a foreign key like FOREIGN KEY (vehicle_id) REFERENCES vehicles(id), it will correctly reference any row in the hierarchy. But if you have FOREIGN KEY (vehicle_id) REFERENCES ONLY vehicles(id), it will only reference rows from the explicit vehicles table, not any of its children. This level of control is powerful but easy to miss.

A More Robust Pattern: Using Inheritance for Storage, Not Logic

So, given the pitfalls, how do the pros use it? Often, they use inheritance more for physical storage organization than for pure logical modeling. A great use case is table partitioning, where you have a massive parent table (e.g., sensor_readings) and inherit from it to create child tables that hold data for specific time ranges (e.g., sensor_readings_2023, sensor_readings_2024). PostgreSQL’s query planner can then use constraint exclusion to automatically only query the relevant child tables when you search by date, making your massive table incredibly efficient.

You manage the uniqueness and foreign keys at the application level or via complex triggers, and you use the inheritance structure primarily as a way to organize data on disk. It’s less about elegant “is-a” relationships and more about brute-force performance.

In summary, table inheritance is a sharp tool. It’s not the Swiss Army knife you use for every problem; it’s the specialty blade you pull out when you have a very specific job to do, like partitioning, and you’re fully aware of the maintenance overhead it requires. Understand its polymorphic query behavior, respect its constraints (or lack thereof), and you can wield it effectively. Misunderstand it, and it’ll bite you.