The brief history section of the PostGreSQL manual introduces: The object-relational database management system known as PostGreSQL was developed from the POSTGRES software package written by the University of California, Berkeley. After more than ten years of development, PostGreSQL is currently the most advanced open source database in the world.
The object-relational database management system now known as PostgreSQL is derived from the POSTGRES package written at the University of California at Berkeley. With decades of development behind it, PostgreSQL is now the most advanced open-source database available anywhere.
Different from traditional relational databases, object-relational databases (ORDBMS) are the product of a combination of object-oriented technology and traditional relational databases. Based on the basic idea of object-oriented programming (OOP), it regards all entities as objects. Through in-depth analysis of inheritance and association relationships between objects, it encapsulates objects and converts them into data tables, and provides a ” Table inheritance” mechanism to help database designers complete database design more conveniently.
Table inheritance
Case introduction
Assumption: We currently want to build a data model for a city, and cities are divided into two categories: provincial capital cities and non-provincial capital cities; it is required to quickly retrieve the capital city of any specific province.
Of course we can create a table and use an extra field to identify whether this city is the capital of the province, but the problem is that it will cause serious data redundancy. Because: among the 34 provinces, the number of non-capital cities is much greater than the number of capital cities.
CREATE TABLE cities (
name text,
population float,
altitude int -? in feet,
province_id int,
is_capital boolean
);
So we tried to create two tables, one to store provincial capital cities and the other to store data of non-provincial capital cities (PS:). However, it will increase the complexity of maintenance, because the two tables have the same structure, but are defined twice. From the perspective of a database designer, this situation is extremely disappointing.
CREATE TABLE cities (
name text,
population float,
altitude int -? in feet,
province_id int,
);
CREATE TABLE capitals (
name text,
population float,
altitude int -? in feet,
province_id int
) INHERITS (cities);
Table inheritance
Is there a compromise, just like the object-oriented mechanism-inheritance feature in Java, to reduce the definition of repeated fields? The answer is yes: Because PostgreSQL implements table inheritance, this is a useful tool for database designers. Like below,
---Create data table
CREATE TABLE myschema.cities (
name text,
population float,
altitude int,
province varchar(64)
);
--Create inheritance subtable
CREATE TABLE myschema.capitals (
) INHERITS (myschema.cities);
In this case, the capitals table inherits all columns from its parent table cities. We only need to store all provincial capital cities in capitals without considering other issues, because the inheritance feature helps us complete the reuse of the table structure.
How to query inherited tables
In PostgreSQL, a table can inherit from zero or more other tables, and a query on a table can reference all rows of a table or all rows of the table plus it All descendant representatives. The default is the latter.
We add several pieces of test data to the cities parent table and capitals child table respectively.
--Add data
INSERT INTO myschema.cities(name,population,altitude,province)
VALUES ('Luoyang',125,500,'Henan Province'),
('Pingdingshan',100,600,'Henan Province'),
('Jiaozuo',180,550,'Henan Province')
---Add data for inherited subtables
INSERT INTO myschema.capitals(name,population,altitude,province)
VALUES ('Zhengzhou City',125,500,'Henan Province')
SELECT name,population,altitude,province FROM myschema.cities
The query results include by default: all records in the parent table and inherited child tables,
The above query operation is equivalent to:
SELECT name,population,altitude,province FROM myschema.cities*
Among them: *Explicitly specify that all descendant tables are included, which is also the default behavior of the PG database.
How to query only the parent table
The PostGreSQL database provides the ONLY keyword, which is used to: Indicate that the query should only target the cities parent table, not including its descendants.
PS: Other commands (SELECT, UPDATE and DELETE) support the ONLY keyword.
SELECT name,population,altitude,province FROM ONLY myschema.cities
As shown below, the query result this time is only the records in cities.
How to determine which table the result row belongs to
Now, we may have doubts: If we query a parent table, how do we know whether each row in the result comes from the parent table or the child table?
The designers of the PostGreSQL database have obviously taken this issue into consideration: in each table we have a tableoid system property that can tell you who the source table is.
SELECT name,population,altitude,province,tableoid FROM myschema.cities*
The query results are as follows,
Through the tableoid field, we can classify records. So how to get the specific table name through the tableoid field? You only need to make a connection with pg_class to see the actual table name
--Do connection query with pg_class table
SELECT name,population,altitude,province,pg_class.relname
FROM myschema.cities* LEFT JOIN pg_class
ON pg_class.oid = myschema.cities.tableoid
Other usage details
①All check constraints and non-null constraints of the parent table will automatically be inherited by all child tables. However, other types of constraints (unique, primary key, foreign key constraints) will not be inherited.
②A child table can inherit from multiple parent tables. In this case, it will have the sum of all parent table fields, and the fields defined in the child table will also be added to it.
③ Any parent table in which a child table exists cannot be deleted. Likewise, any fields or constraints inherited from the parent table in the child table cannot be deleted or modified. If you want to delete a table and all its descendants, the easiest way is to delete the parent table using the CASCADE option.