In this tutorial, we will explore the role of table inheritance in PostgreSQL, how to create them, and the other operations that we can perform on them.
Create a Parent Table in PostgreSQL
When working with table inheritance, the root of it all is the parent table. The parent table serves as the template for all the child tables from which they can inherit the structure and properties.
The parent table defines the common attributes that are shared across the child tables.
Consider the following example that creates a parent table called “vehicles”:
id SERIAL PRIMARY KEY,
brand VARCHAR(100),
model VARCHAR(100),
year INT
);
The previous query sets up a basic table with three columns: id, brand, model, and year.
Create a Child Table in PostgreSQL
Once we define the template or parent table, we then create the child tables from it. Each child table can have additional columns that are specific to it. These private attributes are limited to that child table while it inherits the other columns from the parent table.
Consider the following example queries that create two child tables:
wheels INT
) INHERITS (vehicles);
CREATE TABLE bikes (
gears INT
) INHERITS (vehicles);
We create two tables in the previous queries. The “cars” column has an additional column called “wheels”, while the “bikes” table has an extra column called “gears”. Both child tables inherit the parent table’s id, brand, model, and year columns.
Insert a Data Into the Child Tables
Once we define the child tables, we can use the standard SQL INSERT statements to add the data to them, as demonstrated in the following example:
VALUES ('Ford', 'Mustang', 2022, 4);
INSERT INTO bikes (brand, model, year, gears)
VALUES ('Trek', 'Mountain Bike', 2021, 10);
This should insert a single row to the “cars” and “bikes” tables.
Query the Child and Parent Tables
As you can guess, we can use a typical select statement to select the data from both parent and child tables.
This should query the parent table and return the output as follows:
You can also query the individual child tables as follows:
SELECT * FROM bikes;
To query the parent table and all child tables, we can use a union query as shown in the following example:
FROM cars
UNION ALL
SELECT id, brand, model, year, NULL AS wheels, gears, NULL AS frame_size
FROM bikes;
In this case, we explicitly specify the columns for each SELECT statement and add the NULL values for the missing columns in each table. This way, all queries in the union have the same number of columns that allow the union to run successfully.
NOTE: Similar to regular tables, you can use the ALTER TABLE and ALTER COLUMN statements to modify the table properties and columns. You can also use the DROP COLUMN clause to remove the columns from the child and parent tables.
Constraints and Indexes in Table Inheritance
In the case of constraints and indexes, PostgreSQL table inheritance allows us to define the constraints and indexes on both parent and child tables.
ADD CONSTRAINT unique_model UNIQUE (model);
The given example query adds a UNIQUE constraint to the parent table.
Detach the Inheritance
Once you are done with the table inheritance and you wish the tables to stand as individual tables, you can use the ALTER TABLE with the NO INHERIT option as follows:
NO INHERIT vehicles;
This detaches the table and allows it to identify as a standalone table.
Conclusion
You learned how to work with the PostgreSQL table inheritance to inherit the structure and properties of the parent table. You also learned how to query the child and parent tables, modify the tables, and detach the child tables from the inheritance.
