<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.63 2006/09/20 21:30:20 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.64 2006/10/13 21:43:17 tgl Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

</para>

<para>

Table inheritancecan be defined using the<xref linkend="sql-createtable"

endterm="sql-createtable-title"> statement using the

<command>INHERITS</command> keyword. However the related statement

<command>CREATE TABLE AS</command> does not allow inheritance to be

specified.

Table inheritanceis typically established when thechild table is

created, using the <literal>INHERITS</> clause of the

<xref linkend="sql-createtable" endterm="sql-createtable-title">

statement. However the related statement<command>CREATE TABLE AS</command>

does not allow inheritance to bespecified.

</para>

<para>

Alternatively a table which is already defined in a compatible way can have

a new parent added with <xref linkend="sql-altertable"

endterm="sql-altertable-title"> using the <command>INHERIT</command>

subform.To do this the new child table must already include columns with

the samename andtype as the columns of the parent. It must also include

check constraints with the samename and checkexpression as those of the

Alternatively, a table which is already defined in a compatible way can

havea new parentrelationshipadded, using the <literal>INHERIT</literal>

variant of <xref linkend="sql-altertable"endterm="sql-altertable-title">.

To do this the new child table must already include columns with

the samenames andtypes as the columns of the parent. It must also include

check constraints with the samenames and checkexpressions as those of the

parent. Similarly an inheritance link can be removed from a child using the

<command>ALTER TABLE</command> using the <command>NO INHERIT</command>

subform.

<literal>NO INHERIT</literal> variant of <command>ALTER TABLE</>.

Dynamically adding and removing inheritance links like this can be useful

when the inheritance relationship is being used for table

partitioning (see <xref linkend="ddl-partitioning">).

</para>

<para>

One convenient way to create a compatible tableto be a new child

isspecifythe <command>LIKE</command> clause in <command>CREATE

One convenient way to create a compatible tablethat will later be made

a new childisto usethe <literal>LIKE</literal> clause in <command>CREATE

TABLE</command>. This creates a new table with the same columns as

the source table. If there are any <command>CHECK</command>

constraints defined on the parent table, the <command>INCLUDING

CONSTRAINTS</command> option to <command>LIKE</command> may be

useful, as the new child must have constraints matching the parent

to be considered compatible. Alternatively a compatible table can

be created by first creating a new child using <command>CREATE

TABLE</command> then removing the inheritance link with

<command>ALTER TABLE</command>.

the source table. If there are any <literal>CHECK</literal>

constraints defined on the source table, the <literal>INCLUDING

CONSTRAINTS</literal> option to <literal>LIKE</literal> should be

specified, as the new child must have constraints matching the parent

to be considered compatible.

</para>

<para>

A parent table cannot be dropped while any

of its children remain. If you wish to remove a table and all of its

A parent table cannot be dropped while any of its children remain. Neither

can columns 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

<literal>CASCADE</literal> option. Neither can columns of child tables be

dropped or altered if they are inherited from any parent tables.

<literal>CASCADE</literal> option.

</para>

<para>

<xref linkend="sql-altertable" endterm="sql-altertable-title"> will

propagate any changes in column data definitions and check constraints down

the inheritance hierarchy. <command>ALTER TABLE</command> follows the same

rules for duplicate column merging and rejection that apply during

<command>CREATE TABLE</command>.

propagate any changes in column data definitions and check

constraints down the inheritance hierarchy. Again, dropping

columns or constraints on parent tables is only possible when using

the <literal>CASCADE</literal> option. <command>ALTER

TABLE</command> follows the same rules for duplicate column merging

and rejection that apply during <command>CREATE TABLE</command>.

</para>

<sect2 id="ddl-inherit-caveats">

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

</para>

</listitem>

<listitem>

<para>

If a table is ever removed from the inheritance structure using

<command>ALTER TABLE</command> then all its columns will be marked as

being locally defined. This means <command>DROP COLUMN</command> on the

parent table will never cascade to drop those columns on the child

table. They must be dropped manually.

</para>

</listitem>

</itemizedlist>

These deficiencies will probably be fixed in some future release,

<itemizedlist>

<listitem>

<para>

Query performance can be improved when partition constraints can be

combined with local indexes to reduce the number of records that need to

be accessed for a query. Whereas the alternative, adding those columns

to every index, increases space usage which can erase any

performance gain.

</para>

<para>

When most of the heavily accessed area of the table is in a single

partition or a small number of partitions. That partition and its

indexes are more likely to fit within memory than the index of the

entire table.

Query performance can be improved dramatically in certain situations,

particularly when most of the heavily accessed rows of the table are in a

single partition or a small number of partitions. The partitioning

substitutes for leading columns of indexes, reducing index size and

making it more likely that the heavily-used parts of the indexes

fit in memory.

</para>

</listitem>

<listitem>

<para>

When queries or updates access a large percentage of a a single

partition performance can be improved dramatically by taking advantage

of sequentialdisk accessofa single partition instead of using an

index and random access reads across the whole table.

partition, performance can be improved by taking advantage

of sequentialscanofthat partition instead of using an

index and random access readsscatteredacross the whole table.

</para>

</listitem>

<listitem>

<para>

Bulk loads and deletes may be accomplished bysimply removingoradding

one of thepartitions. <command>ALTER TABLE</>isfar faster than a bulk

added or removed.It also entirely avoids the <command>VACUUM</command>

overhead caused by a bulk <command>delete</>.

Bulk loads and deletes may be accomplished byaddingorremoving

partitions, if that requirementisplanned into the partitioning design.

It also entirely avoids the <command>VACUUM</command>

overhead caused by a bulk <command>DELETE</>.

</para>

</listitem>

creating a new partition each month, so it may be wise to write a

script that generates the required DDL automatically.

</para>

<para>

Partitioning can also be arranged using a <literal>UNION ALL</literal>

view:

<programlisting>

CREATE VIEW measurement AS

SELECT * FROM measurement_y2004m02

UNION ALL SELECT * FROM measurement_y2004m03

...

UNION ALL SELECT * FROM measurement_y2005m11

UNION ALL SELECT * FROM measurement_y2005m12

UNION ALL SELECT * FROM measurement_y2006m01;

</programlisting>

However, the need to

recreate the view adds an extra step to adding and dropping

individual partitions of the dataset.

</para>

</sect2>

<sect2 id="ddl-partitioning-managing-partitions">

add new partitions for new data. One of the most important

advantages of partitioning is precisely that it allows this

otherwise painful task to be executed nearly instantaneously by

manipulating the partition structure, rather than moving large

amounts of data around physically.

manipulating the partition structure, rather thanphysicallymoving large

amounts of data around.

</para>

<para>

The simplest option for removing old data istosimply drop the partition

The simplest option for removing old data is simply to drop the partition

that is no longer necessary:

<programlisting>

DROP TABLEmeasurement_y2003mm02;

DROP TABLEmeasurement_y2003m02;

</programlisting>

This can very quickly delete millions of records because it doesn't have

to individually delete every record.

the partitioned table but retain access to it as a table in its own

right:

<programlisting>

ALTER TABLEmeasurement_y2003mm02 NO INHERIT measurement;

ALTER TABLEmeasurement_y2003m02 NO INHERIT measurement;

</programlisting>

This allows further operations to be performed on the data before

it is dropped. For example, this is often a useful time tobackup

it is dropped. For example, this is often a useful time toback up

the data using <command>COPY</>, <application>pg_dump</>, or

similar tools. It can also be a useful time to aggregate data

into smaller formats, perform other data manipulations, or run

transformed prior to it appearing in the partitioned table.

<programlisting>

CREATE TABLE measurement_y2006m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

\COPY measurement_y2006m02 FROM 'measurement_y2006m02'

UPDATE ... ;

ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02 CHECK ( logdate &gt;= DATE '2006-02-01' AND logdate &lt; DATE '2006-03-01' );

CREATE TABLE measurement_y2006m02

(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02

CHECK ( logdate &gt;= DATE '2006-02-01' AND logdate &lt; DATE '2006-03-01' );

\copy measurement_y2006m02 from 'measurement_y2006m02'

-- possibly some other data preparation work

ALTER TABLE measurement_y2006m02 INHERIT measurement;

</programlisting>

</para>

using a set of rules as suggested above.)

</para>

</listitem>

<listitem>

<para>

When using the <literal>LIKE</> option above to create new

partitions, <literal>CHECK</> constraints are not copied from

the parent. If there are any <literal>CHECK</> constraints

defined for the parent, they must be manually created in new

partitions before <command>ALTER TABLE</command> will allow them

to be added.

</para>

</listitem>

</itemizedlist>

</para>

<para>

Partitioning can also be arranged using a <literal>UNION ALL</literal>

view:

<programlisting>

CREATE VIEW measurement AS

SELECT * FROM measurement_y2004m02

UNION ALL SELECT * FROM measurement_y2004m03

...

UNION ALL SELECT * FROM measurement_y2005m11

UNION ALL SELECT * FROM measurement_y2005m12

UNION ALL SELECT * FROM measurement_y2006m01;

</programlisting>

However, the need to

recreate the view adds an extra step to adding and dropping

individual partitions of the dataset.

</para>

</sect2>

<sect2 id="ddl-partitioning-constraint-exclusion">

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.90 2006/09/16 00:30:16 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.91 2006/10/13 21:43:18 tgl Exp $

PostgreSQL documentation

-->

<term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>

<listitem>

<para>

This form adds a new parent table to the table. This won't add new

columns to the child table, instead all columns of the parent table must

already exist in the child table. They must have matching data types,

This form adds the target table as a new child of the specified parent

table. Subsequently, queries against the parent will include records

of the target table. To be added as a child, the target table must

already contain all the same columns as the parent (it could have

additional columns, too). The columns must have matching data types,

and if they have <literal>NOT NULL</literal> constraints in the parent

then they must also have <literal>NOT NULL</literal> constraints in the

child.

</para>

<para>

There must also be matching table constraints for all

<literal>CHECK</literal>tableconstraints of the parent. Currently

There must also be matchingchild-table constraints for all

<literal>CHECK</literal> constraints of the parent. Currently

<literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and

<literal>FOREIGN KEY</literal> constraints are ignored however this may

change in the future.

</para>

<para>

The easiest way to create a suitable table is to create a table using

<literal>INHERITS</literal> and then remove it via <literal>NO

INHERIT</literal>. Alternatively create a table using

<literal>LIKE</literal> however note that <literal>LIKE</literal> does

not create the necessary constraints.

<literal>FOREIGN KEY</literal> constraints are not considered, but

this may change in the future.

</para>

</listitem>

</varlistentry>

<term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>

<listitem>

<para>

This form removes a parent table from the list of parents of the table.

This form removes the target table from the list of children of the

specified parent table.

Queries against the parent table will no longer include records drawn

from the target table.

</para>

You must own the table to use <command>ALTER TABLE</>.

To change the schema of a table, you must also have

<literal>CREATE</literal> privilege on the new schema.

To add the table as a new child of a parent table, you must own the

parent table as well.

To alter the owner, you must also be a direct or indirect member of the new

owning role, and that role must have <literal>CREATE</literal> privilege on

the table's schema. (These restrictions enforce that altering the owner