diff options
Diffstat (limited to 'doc/src/sgml/html/ddl-inherit.html')
| -rw-r--r-- | doc/src/sgml/html/ddl-inherit.html | 289 |
1 files changed, 289 insertions, 0 deletions
diff --git a/doc/src/sgml/html/ddl-inherit.html b/doc/src/sgml/html/ddl-inherit.html new file mode 100644 index 0000000..491a741 --- /dev/null +++ b/doc/src/sgml/html/ddl-inherit.html @@ -0,0 +1,289 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>5.10. Inheritance</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ddl-schemas.html" title="5.9. Schemas" /><link rel="next" href="ddl-partitioning.html" title="5.11. Table Partitioning" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">5.10. Inheritance</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-schemas.html" title="5.9. Schemas">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><th width="60%" align="center">Chapter 5. Data Definition</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-partitioning.html" title="5.11. Table Partitioning">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="DDL-INHERIT"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.10. Inheritance</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ddl-inherit.html#DDL-INHERIT-CAVEATS">5.10.1. Caveats</a></span></dt></dl></div><a id="id-1.5.4.12.2" class="indexterm"></a><a id="id-1.5.4.12.3" class="indexterm"></a><p> + <span class="productname">PostgreSQL</span> implements table inheritance, + which can be a useful tool for database designers. (SQL:1999 and + later define a type inheritance feature, which differs in many + respects from the features described here.) + </p><p> + Let's start with an example: suppose we are trying to build a data + model for cities. Each state has many cities, but only one + capital. We want to be able to quickly retrieve the capital city + for any particular state. This can be done by creating two tables, + one for state capitals and one for cities that are not + capitals. However, what happens when we want to ask for data about + a city, regardless of whether it is a capital or not? The + inheritance feature can help to resolve this problem. We define the + <code class="structname">capitals</code> table so that it inherits from + <code class="structname">cities</code>: + +</p><pre class="programlisting"> +CREATE TABLE cities ( + name text, + population float, + elevation int -- in feet +); + +CREATE TABLE capitals ( + state char(2) +) INHERITS (cities); +</pre><p> + + In this case, the <code class="structname">capitals</code> table <em class="firstterm">inherits</em> + all the columns of its parent table, <code class="structname">cities</code>. State + capitals also have an extra column, <code class="structfield">state</code>, that shows + their state. + </p><p> + In <span class="productname">PostgreSQL</span>, a table can inherit from + zero or more other tables, and a query can reference either all + rows of a table or all rows of a table plus all of its descendant tables. + The latter behavior is the default. + For example, the following query finds the names of all cities, + including state capitals, that are located at an elevation over + 500 feet: + +</p><pre class="programlisting"> +SELECT name, elevation + FROM cities + WHERE elevation > 500; +</pre><p> + + Given the sample data from the <span class="productname">PostgreSQL</span> + tutorial (see <a class="xref" href="tutorial-sql-intro.html" title="2.1. Introduction">Section 2.1</a>), this returns: + +</p><pre class="programlisting"> + name | elevation +-----------+----------- + Las Vegas | 2174 + Mariposa | 1953 + Madison | 845 +</pre><p> + </p><p> + On the other hand, the following query finds all the cities that + are not state capitals and are situated at an elevation over 500 feet: + +</p><pre class="programlisting"> +SELECT name, elevation + FROM ONLY cities + WHERE elevation > 500; + + name | elevation +-----------+----------- + Las Vegas | 2174 + Mariposa | 1953 +</pre><p> + </p><p> + Here the <code class="literal">ONLY</code> keyword indicates that the query + should apply only to <code class="structname">cities</code>, and not any tables + below <code class="structname">cities</code> in the inheritance hierarchy. Many + of the commands that we have already discussed — + <code class="command">SELECT</code>, <code class="command">UPDATE</code> and + <code class="command">DELETE</code> — support the + <code class="literal">ONLY</code> keyword. + </p><p> + You can also write the table name with a trailing <code class="literal">*</code> + to explicitly specify that descendant tables are included: + +</p><pre class="programlisting"> +SELECT name, elevation + FROM cities* + WHERE elevation > 500; +</pre><p> + + Writing <code class="literal">*</code> is not necessary, since this behavior is always + the default. However, this syntax is still supported for + compatibility with older releases where the default could be changed. + </p><p> + In some cases you might wish to know which table a particular row + originated from. There is a system column called + <code class="structfield">tableoid</code> in each table which can tell you the + originating table: + +</p><pre class="programlisting"> +SELECT c.tableoid, c.name, c.elevation +FROM cities c +WHERE c.elevation > 500; +</pre><p> + + which returns: + +</p><pre class="programlisting"> + tableoid | name | elevation +----------+-----------+----------- + 139793 | Las Vegas | 2174 + 139793 | Mariposa | 1953 + 139798 | Madison | 845 +</pre><p> + + (If you try to reproduce this example, you will probably get + different numeric OIDs.) By doing a join with + <code class="structname">pg_class</code> you can see the actual table names: + +</p><pre class="programlisting"> +SELECT p.relname, c.name, c.elevation +FROM cities c, pg_class p +WHERE c.elevation > 500 AND c.tableoid = p.oid; +</pre><p> + + which returns: + +</p><pre class="programlisting"> + relname | name | elevation +----------+-----------+----------- + cities | Las Vegas | 2174 + cities | Mariposa | 1953 + capitals | Madison | 845 +</pre><p> + </p><p> + Another way to get the same effect is to use the <code class="type">regclass</code> + alias type, which will print the table OID symbolically: + +</p><pre class="programlisting"> +SELECT c.tableoid::regclass, c.name, c.elevation +FROM cities c +WHERE c.elevation > 500; +</pre><p> + </p><p> + Inheritance does not automatically propagate data from + <code class="command">INSERT</code> or <code class="command">COPY</code> commands to + other tables in the inheritance hierarchy. In our example, the + following <code class="command">INSERT</code> statement will fail: +</p><pre class="programlisting"> +INSERT INTO cities (name, population, elevation, state) +VALUES ('Albany', NULL, NULL, 'NY'); +</pre><p> + We might hope that the data would somehow be routed to the + <code class="structname">capitals</code> table, but this does not happen: + <code class="command">INSERT</code> always inserts into exactly the table + specified. In some cases it is possible to redirect the insertion + using a rule (see <a class="xref" href="rules.html" title="Chapter 41. The Rule System">Chapter 41</a>). However that does not + help for the above case because the <code class="structname">cities</code> table + does not contain the column <code class="structfield">state</code>, and so the + command will be rejected before the rule can be applied. + </p><p> + All check constraints and not-null constraints on a parent table are + automatically inherited by its children, unless explicitly specified + otherwise with <code class="literal">NO INHERIT</code> clauses. Other types of constraints + (unique, primary key, and foreign key constraints) are not inherited. + </p><p> + A table can inherit from more than one parent table, in which case it has + the union of the columns defined by the parent tables. Any columns + declared in the child table's definition are added to these. If the + same column name appears in multiple parent tables, or in both a parent + table and the child's definition, then these columns are <span class="quote">“<span class="quote">merged</span>”</span> + so that there is only one such column in the child table. To be merged, + columns must have the same data types, else an error is raised. + Inheritable check constraints and not-null constraints are merged in a + similar fashion. Thus, for example, a merged column will be marked + not-null if any one of the column definitions it came from is marked + not-null. Check constraints are merged if they have the same name, + and the merge will fail if their conditions are different. + </p><p> + Table inheritance is typically established when the child table is + created, using the <code class="literal">INHERITS</code> clause of the + <a class="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></a> + statement. + Alternatively, a table which is already defined in a compatible way can + have a new parent relationship added, using the <code class="literal">INHERIT</code> + variant of <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></a>. + To do this the new child table must already include columns with + the same names and types as the columns of the parent. It must also include + check constraints with the same names and check expressions as those of the + parent. Similarly an inheritance link can be removed from a child using the + <code class="literal">NO INHERIT</code> variant of <code class="command">ALTER TABLE</code>. + Dynamically adding and removing inheritance links like this can be useful + when the inheritance relationship is being used for table + partitioning (see <a class="xref" href="ddl-partitioning.html" title="5.11. Table Partitioning">Section 5.11</a>). + </p><p> + One convenient way to create a compatible table that will later be made + a new child is to use the <code class="literal">LIKE</code> clause in <code class="command">CREATE + TABLE</code>. This creates a new table with the same columns as + the source table. If there are any <code class="literal">CHECK</code> + constraints defined on the source table, the <code class="literal">INCLUDING + CONSTRAINTS</code> option to <code class="literal">LIKE</code> should be + specified, as the new child must have constraints matching the parent + to be considered compatible. + </p><p> + A parent table cannot be dropped while any of its children remain. Neither + can columns or check constraints of child tables be dropped or altered + if they are inherited + from any parent tables. If you wish to remove a table and all of its + descendants, one easy way is to drop the parent table with the + <code class="literal">CASCADE</code> option (see <a class="xref" href="ddl-depend.html" title="5.14. Dependency Tracking">Section 5.14</a>). + </p><p> + <code class="command">ALTER TABLE</code> will + propagate any changes in column data definitions and check + constraints down the inheritance hierarchy. Again, dropping + columns that are depended on by other tables is only possible when using + the <code class="literal">CASCADE</code> option. <code class="command">ALTER + TABLE</code> follows the same rules for duplicate column merging + and rejection that apply during <code class="command">CREATE TABLE</code>. + </p><p> + Inherited queries perform access permission checks on the parent table + only. Thus, for example, granting <code class="literal">UPDATE</code> permission on + the <code class="structname">cities</code> table implies permission to update rows in + the <code class="structname">capitals</code> table as well, when they are + accessed through <code class="structname">cities</code>. This preserves the appearance + that the data is (also) in the parent table. But + the <code class="structname">capitals</code> table could not be updated directly + without an additional grant. In a similar way, the parent table's row + security policies (see <a class="xref" href="ddl-rowsecurity.html" title="5.8. Row Security Policies">Section 5.8</a>) are applied to + rows coming from child tables during an inherited query. A child table's + policies, if any, are applied only when it is the table explicitly named + in the query; and in that case, any policies attached to its parent(s) are + ignored. + </p><p> + Foreign tables (see <a class="xref" href="ddl-foreign-data.html" title="5.12. Foreign Data">Section 5.12</a>) can also + be part of inheritance hierarchies, either as parent or child + tables, just as regular tables can be. If a foreign table is part + of an inheritance hierarchy then any operations not supported by + the foreign table are not supported on the whole hierarchy either. + </p><div class="sect2" id="DDL-INHERIT-CAVEATS"><div class="titlepage"><div><div><h3 class="title">5.10.1. Caveats</h3></div></div></div><p> + Note that not all SQL commands are able to work on + inheritance hierarchies. Commands that are used for data querying, + data modification, or schema modification + (e.g., <code class="literal">SELECT</code>, <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>, + most variants of <code class="literal">ALTER TABLE</code>, but + not <code class="literal">INSERT</code> or <code class="literal">ALTER TABLE ... + RENAME</code>) typically default to including child tables and + support the <code class="literal">ONLY</code> notation to exclude them. + Commands that do database maintenance and tuning + (e.g., <code class="literal">REINDEX</code>, <code class="literal">VACUUM</code>) + typically only work on individual, physical tables and do not + support recursing over inheritance hierarchies. The respective + behavior of each individual command is documented in its reference + page (<a class="xref" href="sql-commands.html" title="SQL Commands">SQL Commands</a>). + </p><p> + A serious limitation of the inheritance feature is that indexes (including + unique constraints) and foreign key constraints only apply to single + tables, not to their inheritance children. This is true on both the + referencing and referenced sides of a foreign key constraint. Thus, + in the terms of the above example: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + If we declared <code class="structname">cities</code>.<code class="structfield">name</code> to be + <code class="literal">UNIQUE</code> or a <code class="literal">PRIMARY KEY</code>, this would not stop the + <code class="structname">capitals</code> table from having rows with names duplicating + rows in <code class="structname">cities</code>. And those duplicate rows would by + default show up in queries from <code class="structname">cities</code>. In fact, by + default <code class="structname">capitals</code> would have no unique constraint at all, + and so could contain multiple rows with the same name. + You could add a unique constraint to <code class="structname">capitals</code>, but this + would not prevent duplication compared to <code class="structname">cities</code>. + </p></li><li class="listitem"><p> + Similarly, if we were to specify that + <code class="structname">cities</code>.<code class="structfield">name</code> <code class="literal">REFERENCES</code> some + other table, this constraint would not automatically propagate to + <code class="structname">capitals</code>. In this case you could work around it by + manually adding the same <code class="literal">REFERENCES</code> constraint to + <code class="structname">capitals</code>. + </p></li><li class="listitem"><p> + Specifying that another table's column <code class="literal">REFERENCES + cities(name)</code> would allow the other table to contain city names, but + not capital names. There is no good workaround for this case. + </p></li></ul></div><p> + + Some functionality not implemented for inheritance hierarchies is + implemented for declarative partitioning. + Considerable care is needed in deciding whether partitioning with legacy + inheritance is useful for your application. + </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ddl-schemas.html" title="5.9. Schemas">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ddl-partitioning.html" title="5.11. Table Partitioning">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.9. Schemas </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.11. Table Partitioning</td></tr></table></div></body></html>
\ No newline at end of file |