Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to understand PostgreSQL table inheritance". In daily operation, I believe many people have doubts about how to understand PostgreSQL table inheritance. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how to understand PostgreSQL table inheritance". Next, please follow the editor to study!

PostgreSQL implements table inheritance, which is a useful tool for database designers (SQL:1999 and later versions define a type inheritance feature, but it is quite different from the inheritance described here). Let's start with an example: suppose we want to build a data model for the city. Each state has many cities, but only one capital. We want to be able to quickly retrieve the capital city of any particular state. This can be done by creating two tables: one for the state capital and the other for a city that is not the capital. However, what happens when we want to look at a city's data, whether it is a capital or not? Inheriting features will help solve this problem. We can define the capitals table as inheriting from the cities table:

Jydb=# CREATE TABLE cities (jydb (# name text,jydb (# altitude int-in feetjydb (#); CREATE TABLEjydb=# CREATE TABLE capitals (jydb (# state char (2) jydb (#) INHERITS (cities); CREATE TABLEjydb=# insert into cities values ('Las Vegas',600,2174); INSERT 0 1jydb=# insert into cities values (' Mariposa',500,1953); INSERT 0 1jydb=# insert into cities values ('Madison',450845); INSERT 0 1jydb=# insert into capitals values (' Houston',400,745,'LA')) INSERT 0 1jydb=# select * from cities; name | population | altitude-+-+- Las Vegas | 2174 Mariposa | 1953 Madison | 450 | 845 Houston | 400 | 745 (4 rows) jydb=# select * from capitals Name | population | altitude | state-+- Houston | 400 | 745 | LA (1 row)

In this case, the capitals table inherits all the columns of its parent table cities. The state capital has an additional column, state, to indicate the state to which it belongs.

In PostgreSQL, a table can inherit from 0 or more other tables, while a query on a table can refer to all rows of a table or all rows of the table plus all its representations. The default is the latter behavior. For example, the following query finds the names of all cities above 500 feet above sea level, including state capitals:

Jydb=# SELECT name, altitude FROM cities WHERE altitude > 500; name | altitude-+- Las Vegas | 2174 Mariposa | 1953 Madison | 845 Houston | 745 (4 rows)

On the other hand, the following query finds all cities that are more than 500 feet above sea level and are not state capitals:

Jydb=# SELECT name, altitude FROM ONLY cities WHERE altitude > 500; name | altitude-+- Las Vegas | 2174 Mariposa | 1953 Madison | 845 (3 rows)

The ONLY keyword here indicates that the query is only applied to cities, while other tables below cities in the inheritance hierarchy are not covered by the query. Many of the commands we have discussed, such as SELECT, UPDATE, and DELETE, support the ONLY keyword.

We can also explicitly include the post-representative in the query by writing a * after the table name:

Jydb=# SELECT name, altitude FROM cities* WHERE altitude > 500; name | altitude-+- Las Vegas | 2174 Mariposa | 1953 Madison | 845 Houston | 745 (4 rows)

* is not necessary because its corresponding behavior is default (unless you change the setting of the sql_inheritance configuration option). But writing * helps to emphasize that additional tables will be searched.

In some cases, we may want to know which table a particular row comes from. The system column tableoid in each table can tell us which table the row comes from:

Jydb=# SELECT c.tableoid, c.name, c.altitude FROM cities c WHERE c.altitude > 500; tableoid | name | altitude-+-+- 24653 | Las Vegas | 2174 24653 | Mariposa | 1953 24653 | Madison | 845 24659 | Houston | 745 (4 rows)

If you regenerate this result, you may get a different OID number. By connecting with pg_class, you can see the actual table name:

Jydb=# SELECT p.relname, c.name, c.altitudejydb-# FROM cities c, pg_class pjydb-# WHERE c.altitude > 500AND c.tableoid = p.oid; relname | name | altitude-+-+- cities | Las Vegas | 2174 cities | Mariposa | 1953 cities | Madison | 845 capitals | Houston | 745 (4 rows)

Another way to achieve the same effect is to use the regclass pseudo-type, which symbolically prints out the OID of the table:

Jydb=# SELECT c.tableoid::regclass, c.name, c.altitudejydb-# FROM cities cjydb-# WHERE c.altitude > 500; tableoid | name | altitude-+-+- cities | Las Vegas | 2174 cities | Mariposa | 1953 cities | Madison | 845 capitals | Houston | 745 (4 rows)

Inheritance does not automatically propagate data from INSERT or COPY commands to other tables in the inheritance hierarchy. In our example, the following INSERT statement will fail:

Jydb=# INSERT INTO cities (name, population, altitude, state) VALUES ('Albany', NULL, NULL,' NY'); ERROR: column "state" of relation "cities" does not existLINE 1: INSERT INTO cities (name, population, altitude, state) VALUE...

^

We might want the data to be introduced into the capitals table in some way, but that won't happen: INSERT is always inserted into the specified table. In some cases, you can redirect the insert action by using a rule (see Chapter 39). But this does not help in the above case, because the cities table does not contain a state column at all, so the command will be rejected before the rule is triggered.

All check constraints and non-null constraints on the parent table are automatically inherited by its descendants. Other types of constraints (unique, primary, and foreign key constraints) are not inherited.

A table can inherit from multiple parent tables, in which case it has a union of columns defined by the parent tables. Any columns defined on the child table will also be added to it. If renamed columns appear in this collection, the columns will be "merged" so that there will be only one such column in the child table. Renaming columns can be merged on the premise that they must have the same data type, otherwise an error will result. Inheritable check constraints and non-null constraints are merged in a similar manner. So, for example, if any column definition is marked as not-null, the merged column will be marked as not-null. If the check constraints have the same name, merge them; if the conditions are different, the merge will fail.

Table inheritance is usually established when a child table is created, using the INHERITS clause of the CREATE TABLE statement. A table that has been created can also add a new paternity in another way, using an INHERIT variant of ALTER TABLE. To do this, the new child table must already include columns with the same name and data type as the parent table. The child table must also include the same check constraints and check expressions as the parent table. Similarly, an inherited link can be removed from a child table using a NO INHERIT variant of ALTER TABLE. Dynamically adding and removing inherited links can be used to achieve table partitioning.

One way to create a new table that will be used as children in the future is to use the like clause in CREATE TABLE. This creates a new table with the same columns as the source table. If there are any CHECK constraints defined on the source table, the INCLUDING CONSTRAINTS option of LIKE can be used to make the new child table contain the same constraints as the parent table.

When any of the child tables exist, the parent table cannot be deleted. When the columns or check constraints of a child table are inherited from the parent table, they cannot be deleted or modified. If you want to remove a table and all its descendants, an easy way is to delete the parent table using the CASCADE option

ALTER TABLE will propagate any changes in the data definition of the column or check constraints down the inheritance hierarchy. Similarly, you can only use the CASCADE option to delete columns that are dependent on other tables. ALTER TABLE follows the same rules as CREATE TABLE for merging and rejecting renamed columns.

Inheritance queries only perform access checks on the parent table. So, for example, granting update permission to the cities table means that the capitals table can also be updated when the capitals table is accessed through cities. This shows that the data in the child table is also in the parent table. However, the capitals table cannot be updated directly without additional authorization. In a similar manner, the row security policy of the parent table is applied to the row records of the child table when the inheritance query is executed. The policy for the child table, if any, is applied only if it is an explicitly named table in the query; in this case, any policies attached to its parent are ignored.

An external table can also be part of an inheritance hierarchy, either as a parent or as a child, just like a regular table. If an external table is part of the inheritance hierarchy, any operations that are not supported by the external table are not supported by the entire hierarchy.

At this point, the study on "how to understand PostgreSQL table inheritance" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.