<!--

$PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.44 2004/08/0801:51:05 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.45 2004/08/0821:33:11 tgl Exp $

-->

<chapter id="tutorial-advanced">

<programlisting>

CREATE TABLE cities (

city varchar(80) primary key,

location point

city varchar(80) primary key,

location point

);

CREATE TABLE weather (

city varchar(80) references cities(city),

temp_lo int,

temp_hi int,

prcp real,

date date

city varchar(80) references cities(city),

temp_lo int,

temp_hi int,

prcp real,

date date

);

</programlisting>

</indexterm>

<para>

Inheritance is a concept from object-oriented databases. Although

it opens up interesting new possibilities of database design,

this feature is currently unmaintained and known to have serious

gotchas in its foreign key implementation, which you should take

care to avoid. The fixes below are probably version-specific and may

require updates in the future.

Inheritance is a concept from object-oriented databases. It opens

up interesting new possibilities of database design.

</para>

<para>

The example below illustrates the gotcha.

</para>

<para>

<programlisting>

BEGIN;

CREATE TABLE foo (

foo_id SERIAL PRIMARY KEY

);

CREATE TABLE parent (

parent_id SERIAL PRIMARY KEY

, foo_id INTEGER NOT NULL REFERENCES foo(foo_id) ON DELETE CASCADE

, parent_1_text TEXT NOT NULL

);

CREATE TABLE child_1 (

child_1_text TEXT NOT NULL

) INHERITS(parent);

CREATE TABLE child_2 (

child_2_text TEXT NOT NULL

) INHERITS(parent);

INSERT INTO foo VALUES(DEFAULT);

INSERT INTO child_1 (foo_id, parent_1_text, child_1_text)

VALUES (currval('public.foo_foo_id_seq'), 'parent text 1', 'child_1 text 1');

INSERT INTO foo VALUES(DEFAULT);

INSERT INTO child_1 (foo_id, parent_1_text, child_1_text)

VALUES (currval('public.foo_foo_id_seq'), 'parent text 2', 'child_1 text 2');

INSERT INTO foo VALUES(DEFAULT);

INSERT INTO child_2 (foo_id, parent_1_text, child_2_text)

VALUES (currval('foo_foo_id_seq'), 'parent text 3', 'child_2 text 1');

DELETE FROM foo WHERE foo_id = 1;

SELECT * FROM parent;

parent_id | foo_id | parent_1_text

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

1 | 1 | parent text 1

2 | 2 | parent text 2

3 | 3 | parent text 3

(3 rows)

SELECT * FROM child_1;

parent_id | foo_id | parent_1_text | child_1_text

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

1 | 1 | parent text 1 | child_1 text 1

2 | 2 | parent text 2 | child_1 text 2

(2 rows)

ROLLBACK;

</programlisting>

</para>

<para>

Oops!! None of parent, child or foo should have any rows with

foo_id = 1 in them. Here is a way to fix the above tables.

</para>

<para>

To fix the gotcha, you must put foreign key constraints on each of

the child tables, as they will not be automatically inherited as

you might expect.

</para>

<para>

<programlisting>

ALTER TABLE child_1 ADD CONSTRAINT cascade_foo

FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE;

ALTER TABLE child_2 ADD CONSTRAINT cascade_foo

FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE;

</programlisting>

</para>

<para>

That caveat out of the way, let's create two tables: A table

<classname>cities</classname> and a table

<classname>capitals</classname>. Naturally, capitals are also cities,

so you want some way to show the capitals implicitly when you list all

cities. If you're really clever you might invent some scheme like

this:

Let's create two tables: A table <classname>cities</classname>

and a table <classname>capitals</classname>. Naturally, capitals

are also cities, so you want some way to show the capitals

implicitly when you list all cities. If you're really clever you

might invent some scheme like this:

<programlisting>

CREATE TABLE capitals (

<command>DELETE</command> -- support this <literal>ONLY</literal>

notation.

</para>

<note>

<para>

Although inheritance is frequently useful, it has not been integrated

with unique constraints or foreign keys, which limits its usefulness.

See <xref linkend="ddl-inherit"> for more detail.

</para>

</note>

</sect1>

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.29 2004/08/07 20:44:49 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.30 2004/08/08 21:33:11 tgl Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

) INHERITS (cities);

</programlisting>

In this case, a row of capitals <firstterm>inherits</firstterm> all

attributes (name, population, and altitude) from its

parent, cities. The type of the attribute name is

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

for variable length character strings. The type of the attribute

population is

<type>float</type>, a native <productname>PostgreSQL</productname> type for double precision

floating-point numbers. State capitals have an extra

attribute, state, that shows their state. In <productname>PostgreSQL</productname>,

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

descendants.

In this case, a row of capitals <firstterm>inherits</firstterm> all

attributes (name, population, and altitude) from its parent, cities. State

capitals have an extra attribute, state, that shows their state. In

<productname>PostgreSQL</productname>, 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 descendants.

<note>

<para>

support this <quote>ONLY</quote> notation.

</para>

<note>

<title>Deprecated</title>

<para>

In previous versions of <productname>PostgreSQL</productname>, the

default behavior was not to include child tables in queries. This was

found to be error prone and is also in violation of the SQL99

standard. Under the old syntax, to get the sub-tables you append

<literal>*</literal> to the table name.

For example

<programlisting>

SELECT * from cities*;

</programlisting>

You can still explicitly specify scanning child tables by appending

<literal>*</literal>, as well as explicitly specify not scanning child tables by

writing <quote>ONLY</quote>. But beginning in version 7.1, the default

behavior for an undecorated table name is to scan its child tables

too, whereas before the default was not to do so. To get the old

default behavior, set the configuration option

<literal>SQL_Inheritance</literal> to off, e.g.,

<programlisting>

SET SQL_Inheritance TO OFF;

</programlisting>

or add a line in your <filename>postgresql.conf</filename> file.

</para>

</note>

<para>

In some cases you may wish to know which table a particular row

originated from. There is a system column called

</para>

<note>

<title>Deprecated</title>

<para>

In previous versions of <productname>PostgreSQL</productname>, the

default behavior was not to include child tables in queries. This was

found to be error prone and is also in violation of the SQL99

standard. Under the old syntax, to get the sub-tables you append

<literal>*</literal> to the table name.

For example

<programlisting>

SELECT * from cities*;

</programlisting>

You can still explicitly specify scanning child tables by appending

<literal>*</literal>, as well as explicitly specify not scanning child tables by

writing <quote>ONLY</quote>. But beginning in version 7.1, the default

behavior for an undecorated table name is to scan its child tables

too, whereas before the default was not to do so. To get the old

default behavior, set the configuration option

<literal>SQL_Inheritance</literal> to off, e.g.,

<programlisting>

SET SQL_Inheritance TO OFF;

</programlisting>

or add a line in your <filename>postgresql.conf</filename> file.

</para>

</note>

<para>

A limitation of the inheritance feature is that indexes (including

Aseriouslimitation of the inheritance feature is that indexes (including

unique constraints) and foreign key constraints only apply to single

tables, not to their inheritance children. Thus, in the above example,

specifying that another table's column <literal>REFERENCES cities(name)</>

would allow the other table to contain city names but not capital names.

This deficiency will probably be fixed in some future release.

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:

<itemizedlist>

<listitem>

<para>

If we declared <structname>cities</>.<structfield>name</> to be

<literal>UNIQUE</> or a <literal>PRIMARY KEY</>, this would not stop the

<structname>capitals</> table from having rows with names duplicating

rows in <structname>cities</>. And those duplicate rows would by

default show up in SELECTs from <structname>cities</>. In fact, by

default <structname>capitals</> would have no unique constraint at all,

and so could contain multiple rows with the same name.

You could add a unique constraint to <structname>capitals</>, but this

would not prevent duplication compared to <structname>cities</>.

</para>

</listitem>

<listitem>

<para>

Similarly, if we were to specify that

<structname>cities</>.<structfield>name</> <literal>REFERENCES</> some

other table, this constraint would not automatically propagate to

<structname>capitals</>. In this case you could work around it by

manually adding the same <literal>REFERENCES</> constraint to

<structname>capitals</>.

</para>

</listitem>

<listitem>

<para>

Specifying that another table's column <literal>REFERENCES

cities(name)</> would allow the other table to contain city names, but

not capital names. There is no good workaround for this case.

</para>

</listitem>

</itemizedlist>

These deficiencies will probably be fixed in some future release,

but in the meantime considerable care is needed in deciding whether

inheritance is useful for your problem.

</para>

</sect1>

<!--

$PostgreSQL: pgsql/doc/src/sgml/query.sgml,v 1.36 2004/08/0801:51:05 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/query.sgml,v 1.37 2004/08/0821:33:11 tgl Exp $

-->

<chapter id="tutorial-sql">

<programlisting>

SELECT * FROM weather;

</programlisting>

(here <literal>*</literal> means <quote>all columns</quote>.

Note: While <literal>SELECT *</literal> is useful for off-the-cuff

queries, it is considered bad style in production code for

maintenance reasons) and the output should be:

(here <literal>*</literal> means <quote>all columns</quote>).

<footnote>

<para>

While <literal>SELECT *</literal> is useful for off-the-cuff

queries, it is considered bad style in production code for

maintenance reasons: adding a column to the table changes the results.

</para>

</footnote>

The output should be:

<screen>

city | temp_lo | temp_hi | prcp | date

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