CREATE TABLE capitals (

name text,

population real,

altitude int, -- (in ft)

elevation int, -- (in ft)

state char(2)

);

CREATE TABLE non_capitals (

name text,

population real,

altitude int -- (in ft)

elevation int -- (in ft)

);

CREATE VIEW cities AS

SELECT name, population,altitude FROM capitals

SELECT name, population,elevation FROM capitals

UNION

SELECT name, population,altitude FROM non_capitals;

SELECT name, population,elevation FROM non_capitals;

</programlisting>

This works OK as far as querying goes, but it gets ugly when you

CREATE TABLE cities (

name text,

population real,

altitude int -- (in ft)

elevation int -- (in ft)

);

CREATE TABLE capitals (

<para>

In this case, a row of <classname>capitals</classname>

<firstterm>inherits</firstterm> all columns (<structfield>name</structfield>,

<structfield>population</structfield>, and <structfield>altitude</structfield>) from its

<structfield>population</structfield>, and <structfield>elevation</structfield>) from its

<firstterm>parent</firstterm>, <classname>cities</classname>. The

type of the column <structfield>name</structfield> is

<type>text</type>, a native <productname>PostgreSQL</productname>

<para>

For example, the following query finds the names of all cities,

including state capitals, that are located at analtitude

including state capitals, that are located at anelevation

over 500 feet:

<programlisting>

SELECT name,altitude

SELECT name,elevation

FROM cities

WHEREaltitude &gt; 500;

WHEREelevation &gt; 500;

</programlisting>

which returns:

<screen>

name |altitude

-----------+----------

Las Vegas | 2174

Mariposa | 1953

Madison | 845

name |elevation

-----------+-----------

Las Vegas |2174

Mariposa |1953

Madison |845

(3 rows)

</screen>

</para>

<para>

On the other hand, the following query finds

all the cities that are not state capitals and

are situated at analtitude over 500 feet:

are situated at anelevation over 500 feet:

<programlisting>

SELECT name,altitude

SELECT name,elevation

FROM ONLY cities

WHEREaltitude &gt; 500;

WHEREelevation &gt; 500;

</programlisting>

<screen>

name |altitude

-----------+----------

Las Vegas | 2174

Mariposa | 1953

name |elevation

-----------+-----------

Las Vegas |2174

Mariposa |1953

(2 rows)

</screen>

</para>

CREATE TABLE cities (

name text,

population float,

altitude int -- in feet

elevation int -- in feet

);

CREATE TABLE capitals (

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 analtitude over

including state capitals, that are located at anelevation over

500 feet:

<programlisting>

SELECT name,altitude

SELECT name,elevation

FROM cities

WHEREaltitude &gt; 500;

WHEREelevation &gt; 500;

</programlisting>

Given the sample data from the <productname>PostgreSQL</productname>

tutorial (see <xref linkend="tutorial-sql-intro"/>), this returns:

<programlisting>

name |altitude

-----------+----------

Las Vegas | 2174

Mariposa | 1953

Madison | 845

name |elevation

-----------+-----------

Las Vegas |2174

Mariposa |1953

Madison |845

</programlisting>

</para>

<para>

On the other hand, the following query finds all the cities that

are not state capitals and are situated at analtitude over 500 feet:

are not state capitals and are situated at anelevation over 500 feet:

<programlisting>

SELECT name,altitude

SELECT name,elevation

FROM ONLY cities

WHEREaltitude &gt; 500;

WHEREelevation &gt; 500;

name |altitude

-----------+----------

Las Vegas | 2174

Mariposa | 1953

name |elevation

-----------+-----------

Las Vegas |2174

Mariposa |1953

</programlisting>

</para>

to explicitly specify that descendant tables are included:

<programlisting>

SELECT name,altitude

SELECT name,elevation

FROM cities*

WHEREaltitude &gt; 500;

WHEREelevation &gt; 500;

</programlisting>

Writing <literal>*</literal> is not necessary, since this behavior is always

originating table:

<programlisting>

SELECT c.tableoid, c.name, c.altitude

SELECT c.tableoid, c.name, c.elevation

FROM cities c

WHERE c.altitude &gt; 500;

WHERE c.elevation &gt; 500;

</programlisting>

which returns:

<programlisting>

tableoid | name |altitude

----------+-----------+----------

139793 | Las Vegas | 2174

139793 | Mariposa | 1953

139798 | Madison | 845

tableoid | name |elevation

----------+-----------+-----------

139793 | Las Vegas |2174

139793 | Mariposa |1953

139798 | Madison |845

</programlisting>

(If you try to reproduce this example, you will probably get

different numeric OIDs.) By doing a join with

<structname>pg_class</structname> you can see the actual table names:

<programlisting>

SELECT p.relname, c.name, c.altitude

SELECT p.relname, c.name, c.elevation

FROM cities c, pg_class p

WHERE c.altitude &gt; 500 AND c.tableoid = p.oid;

WHERE c.elevation &gt; 500 AND c.tableoid = p.oid;

</programlisting>

which returns:

<programlisting>

relname | name |altitude

----------+-----------+----------

cities | Las Vegas | 2174

cities | Mariposa | 1953

capitals | Madison | 845

relname | name |elevation

----------+-----------+-----------

cities | Las Vegas |2174

cities | Mariposa |1953

capitals | Madison |845

</programlisting>

</para>

alias type, which will print the table OID symbolically:

<programlisting>

SELECT c.tableoid::regclass, c.name, c.altitude

SELECT c.tableoid::regclass, c.name, c.elevation

FROM cities c

WHERE c.altitude &gt; 500;

WHERE c.elevation &gt; 500;

</programlisting>

</para>

other tables in the inheritance hierarchy. In our example, the

following <command>INSERT</command> statement will fail:

<programlisting>

INSERT INTO cities (name, population,altitude, state)

INSERT INTO cities (name, population,elevation, state)

VALUES ('Albany', NULL, NULL, 'NY');

</programlisting>

We might hope that the data would somehow be routed to the