codekingpro/portable-devtools
114k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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 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 16.3 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 /></div><div class="sect1" id="DDL-INHERIT"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.10. Inheritance <a href="#DDL-INHERIT" class="id_link">#</a></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>3 <span class="productname">PostgreSQL</span> implements table inheritance,4 which can be a useful tool for database designers. (SQL:1999 and5 later define a type inheritance feature, which differs in many6 respects from the features described here.)7 </p><p>8 Let's start with an example: suppose we are trying to build a data9 model for cities. Each state has many cities, but only one10 capital. We want to be able to quickly retrieve the capital city11 for any particular state. This can be done by creating two tables,12 one for state capitals and one for cities that are not13 capitals. However, what happens when we want to ask for data about14 a city, regardless of whether it is a capital or not? The15 inheritance feature can help to resolve this problem. We define the16 <code class="structname">capitals</code> table so that it inherits from17 <code class="structname">cities</code>:18 19</p><pre class="programlisting">20CREATE TABLE cities (21 name text,22 population float,23 elevation int -- in feet24);25 26CREATE TABLE capitals (27 state char(2)28) INHERITS (cities);29</pre><p>30 31 In this case, the <code class="structname">capitals</code> table <em class="firstterm">inherits</em>32 all the columns of its parent table, <code class="structname">cities</code>. State33 capitals also have an extra column, <code class="structfield">state</code>, that shows34 their state.35 </p><p>36 In <span class="productname">PostgreSQL</span>, a table can inherit from37 zero or more other tables, and a query can reference either all38 rows of a table or all rows of a table plus all of its descendant tables.39 The latter behavior is the default.40 For example, the following query finds the names of all cities,41 including state capitals, that are located at an elevation over42 500 feet:43 44</p><pre class="programlisting">45SELECT name, elevation46 FROM cities47 WHERE elevation > 500;48</pre><p>49 50 Given the sample data from the <span class="productname">PostgreSQL</span>51 tutorial (see <a class="xref" href="tutorial-sql-intro.html" title="2.1. Introduction">Section 2.1</a>), this returns:52 53</p><pre class="programlisting">54 name | elevation55-----------+-----------56 Las Vegas | 217457 Mariposa | 195358 Madison | 84559</pre><p>60 </p><p>61 On the other hand, the following query finds all the cities that62 are not state capitals and are situated at an elevation over 500 feet:63 64</p><pre class="programlisting">65SELECT name, elevation66 FROM ONLY cities67 WHERE elevation > 500;68 69 name | elevation70-----------+-----------71 Las Vegas | 217472 Mariposa | 195373</pre><p>74 </p><p>75 Here the <code class="literal">ONLY</code> keyword indicates that the query76 should apply only to <code class="structname">cities</code>, and not any tables77 below <code class="structname">cities</code> in the inheritance hierarchy. Many78 of the commands that we have already discussed —79 <code class="command">SELECT</code>, <code class="command">UPDATE</code> and80 <code class="command">DELETE</code> — support the81 <code class="literal">ONLY</code> keyword.82 </p><p>83 You can also write the table name with a trailing <code class="literal">*</code>84 to explicitly specify that descendant tables are included:85 86</p><pre class="programlisting">87SELECT name, elevation88 FROM cities*89 WHERE elevation > 500;90</pre><p>91 92 Writing <code class="literal">*</code> is not necessary, since this behavior is always93 the default. However, this syntax is still supported for94 compatibility with older releases where the default could be changed.95 </p><p>96 In some cases you might wish to know which table a particular row97 originated from. There is a system column called98 <code class="structfield">tableoid</code> in each table which can tell you the99 originating table:100 101</p><pre class="programlisting">102SELECT c.tableoid, c.name, c.elevation103FROM cities c104WHERE c.elevation > 500;105</pre><p>106 107 which returns:108 109</p><pre class="programlisting">110 tableoid | name | elevation111----------+-----------+-----------112 139793 | Las Vegas | 2174113 139793 | Mariposa | 1953114 139798 | Madison | 845115</pre><p>116 117 (If you try to reproduce this example, you will probably get118 different numeric OIDs.) By doing a join with119 <code class="structname">pg_class</code> you can see the actual table names:120 121</p><pre class="programlisting">122SELECT p.relname, c.name, c.elevation123FROM cities c, pg_class p124WHERE c.elevation > 500 AND c.tableoid = p.oid;125</pre><p>126 127 which returns:128 129</p><pre class="programlisting">130 relname | name | elevation131----------+-----------+-----------132 cities | Las Vegas | 2174133 cities | Mariposa | 1953134 capitals | Madison | 845135</pre><p>136 </p><p>137 Another way to get the same effect is to use the <code class="type">regclass</code>138 alias type, which will print the table OID symbolically:139 140</p><pre class="programlisting">141SELECT c.tableoid::regclass, c.name, c.elevation142FROM cities c143WHERE c.elevation > 500;144</pre><p>145 </p><p>146 Inheritance does not automatically propagate data from147 <code class="command">INSERT</code> or <code class="command">COPY</code> commands to148 other tables in the inheritance hierarchy. In our example, the149 following <code class="command">INSERT</code> statement will fail:150</p><pre class="programlisting">151INSERT INTO cities (name, population, elevation, state)152VALUES ('Albany', NULL, NULL, 'NY');153</pre><p>154 We might hope that the data would somehow be routed to the155 <code class="structname">capitals</code> table, but this does not happen:156 <code class="command">INSERT</code> always inserts into exactly the table157 specified. In some cases it is possible to redirect the insertion158 using a rule (see <a class="xref" href="rules.html" title="Chapter 41. The Rule System">Chapter 41</a>). However that does not159 help for the above case because the <code class="structname">cities</code> table160 does not contain the column <code class="structfield">state</code>, and so the161 command will be rejected before the rule can be applied.162 </p><p>163 All check constraints and not-null constraints on a parent table are164 automatically inherited by its children, unless explicitly specified165 otherwise with <code class="literal">NO INHERIT</code> clauses. Other types of constraints166 (unique, primary key, and foreign key constraints) are not inherited.167 </p><p>168 A table can inherit from more than one parent table, in which case it has169 the union of the columns defined by the parent tables. Any columns170 declared in the child table's definition are added to these. If the171 same column name appears in multiple parent tables, or in both a parent172 table and the child's definition, then these columns are <span class="quote">“<span class="quote">merged</span>”</span>173 so that there is only one such column in the child table. To be merged,174 columns must have the same data types, else an error is raised.175 Inheritable check constraints and not-null constraints are merged in a176 similar fashion. Thus, for example, a merged column will be marked177 not-null if any one of the column definitions it came from is marked178 not-null. Check constraints are merged if they have the same name,179 and the merge will fail if their conditions are different.180 </p><p>181 Table inheritance is typically established when the child table is182 created, using the <code class="literal">INHERITS</code> clause of the183 <a class="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></a>184 statement.185 Alternatively, a table which is already defined in a compatible way can186 have a new parent relationship added, using the <code class="literal">INHERIT</code>187 variant of <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></a>.188 To do this the new child table must already include columns with189 the same names and types as the columns of the parent. It must also include190 check constraints with the same names and check expressions as those of the191 parent. Similarly an inheritance link can be removed from a child using the192 <code class="literal">NO INHERIT</code> variant of <code class="command">ALTER TABLE</code>.193 Dynamically adding and removing inheritance links like this can be useful194 when the inheritance relationship is being used for table195 partitioning (see <a class="xref" href="ddl-partitioning.html" title="5.11. Table Partitioning">Section 5.11</a>).196 </p><p>197 One convenient way to create a compatible table that will later be made198 a new child is to use the <code class="literal">LIKE</code> clause in <code class="command">CREATE199 TABLE</code>. This creates a new table with the same columns as200 the source table. If there are any <code class="literal">CHECK</code>201 constraints defined on the source table, the <code class="literal">INCLUDING202 CONSTRAINTS</code> option to <code class="literal">LIKE</code> should be203 specified, as the new child must have constraints matching the parent204 to be considered compatible.205 </p><p>206 A parent table cannot be dropped while any of its children remain. Neither207 can columns or check constraints of child tables be dropped or altered208 if they are inherited209 from any parent tables. If you wish to remove a table and all of its210 descendants, one easy way is to drop the parent table with the211 <code class="literal">CASCADE</code> option (see <a class="xref" href="ddl-depend.html" title="5.14. Dependency Tracking">Section 5.14</a>).212 </p><p>213 <code class="command">ALTER TABLE</code> will214 propagate any changes in column data definitions and check215 constraints down the inheritance hierarchy. Again, dropping216 columns that are depended on by other tables is only possible when using217 the <code class="literal">CASCADE</code> option. <code class="command">ALTER218 TABLE</code> follows the same rules for duplicate column merging219 and rejection that apply during <code class="command">CREATE TABLE</code>.220 </p><p>221 Inherited queries perform access permission checks on the parent table222 only. Thus, for example, granting <code class="literal">UPDATE</code> permission on223 the <code class="structname">cities</code> table implies permission to update rows in224 the <code class="structname">capitals</code> table as well, when they are225 accessed through <code class="structname">cities</code>. This preserves the appearance226 that the data is (also) in the parent table. But227 the <code class="structname">capitals</code> table could not be updated directly228 without an additional grant. In a similar way, the parent table's row229 security policies (see <a class="xref" href="ddl-rowsecurity.html" title="5.8. Row Security Policies">Section 5.8</a>) are applied to230 rows coming from child tables during an inherited query. A child table's231 policies, if any, are applied only when it is the table explicitly named232 in the query; and in that case, any policies attached to its parent(s) are233 ignored.234 </p><p>235 Foreign tables (see <a class="xref" href="ddl-foreign-data.html" title="5.12. Foreign Data">Section 5.12</a>) can also236 be part of inheritance hierarchies, either as parent or child237 tables, just as regular tables can be. If a foreign table is part238 of an inheritance hierarchy then any operations not supported by239 the foreign table are not supported on the whole hierarchy either.240 </p><div class="sect2" id="DDL-INHERIT-CAVEATS"><div class="titlepage"><div><div><h3 class="title">5.10.1. Caveats <a href="#DDL-INHERIT-CAVEATS" class="id_link">#</a></h3></div></div></div><p>241 Note that not all SQL commands are able to work on242 inheritance hierarchies. Commands that are used for data querying,243 data modification, or schema modification244 (e.g., <code class="literal">SELECT</code>, <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>,245 most variants of <code class="literal">ALTER TABLE</code>, but246 not <code class="literal">INSERT</code> or <code class="literal">ALTER TABLE ...247 RENAME</code>) typically default to including child tables and248 support the <code class="literal">ONLY</code> notation to exclude them.249 Commands that do database maintenance and tuning250 (e.g., <code class="literal">REINDEX</code>, <code class="literal">VACUUM</code>)251 typically only work on individual, physical tables and do not252 support recursing over inheritance hierarchies. The respective253 behavior of each individual command is documented in its reference254 page (<a class="xref" href="sql-commands.html" title="SQL Commands">SQL Commands</a>).255 </p><p>256 A serious limitation of the inheritance feature is that indexes (including257 unique constraints) and foreign key constraints only apply to single258 tables, not to their inheritance children. This is true on both the259 referencing and referenced sides of a foreign key constraint. Thus,260 in the terms of the above example:261 262 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>263 If we declared <code class="structname">cities</code>.<code class="structfield">name</code> to be264 <code class="literal">UNIQUE</code> or a <code class="literal">PRIMARY KEY</code>, this would not stop the265 <code class="structname">capitals</code> table from having rows with names duplicating266 rows in <code class="structname">cities</code>. And those duplicate rows would by267 default show up in queries from <code class="structname">cities</code>. In fact, by268 default <code class="structname">capitals</code> would have no unique constraint at all,269 and so could contain multiple rows with the same name.270 You could add a unique constraint to <code class="structname">capitals</code>, but this271 would not prevent duplication compared to <code class="structname">cities</code>.272 </p></li><li class="listitem"><p>273 Similarly, if we were to specify that274 <code class="structname">cities</code>.<code class="structfield">name</code> <code class="literal">REFERENCES</code> some275 other table, this constraint would not automatically propagate to276 <code class="structname">capitals</code>. In this case you could work around it by277 manually adding the same <code class="literal">REFERENCES</code> constraint to278 <code class="structname">capitals</code>.279 </p></li><li class="listitem"><p>280 Specifying that another table's column <code class="literal">REFERENCES281 cities(name)</code> would allow the other table to contain city names, but282 not capital names. There is no good workaround for this case.283 </p></li></ul></div><p>284 285 Some functionality not implemented for inheritance hierarchies is286 implemented for declarative partitioning.287 Considerable care is needed in deciding whether partitioning with legacy288 inheritance is useful for your application.289 </p></div></div><div class="navfooter"><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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.11. Table Partitioning</td></tr></table></div></body></html>