diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/alter_table.sgml | 16 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 2685 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table_as.sgml | 239 | ||||
| -rw-r--r-- | doc/src/sgml/ref/select_into.sgml | 10 | ||||
| -rw-r--r-- | doc/src/sgml/stylesheet.dsl | 12 |
5 files changed, 842 insertions, 2120 deletions
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index f5ac58705b2..5907a64a6b0 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.29 2001/10/12 00:07:14 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.30 2001/10/22 18:14:47 petere Exp $ Postgres documentation --> @@ -161,15 +161,14 @@ ALTER TABLE <replaceable class="PARAMETER">table</replaceable> <para> <command>ALTER TABLE</command> changes the definition of an existing table. The <literal>ADD COLUMN</literal> form adds a new column to the table - using the same syntax as <xref linkend="SQL-CREATETABLE" - endterm="SQL-CREATETABLE-title">. + using the same syntax as <xref linkend="SQL-CREATETABLE">. The <literal>ALTER COLUMN SET/DROP DEFAULT</literal> forms allow you to set or remove the default for the column. Note that defaults only apply to subsequent <command>INSERT</command> commands; they do not cause rows already in the table to change. The <literal>ALTER COLUMN SET STATISTICS</literal> form allows you to set the statistics-gathering target for subsequent - <xref linkend="sql-analyze" endterm="sql-analyze-title"> operations. + <xref linkend="sql-analyze"> operations. The <literal>RENAME</literal> clause causes the name of a table or column to change without changing any of the data contained in the affected table. Thus, the table or column will @@ -177,7 +176,7 @@ ALTER TABLE <replaceable class="PARAMETER">table</replaceable> executed. The ADD <replaceable class="PARAMETER">table constraint definition</replaceable> clause adds a new constraint to the table using the same syntax as <xref - linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-title">. + linkend="SQL-CREATETABLE">. The DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> clause drops all CHECK constraints on the table (and its children) that match <replaceable class="PARAMETER">constraint</replaceable>. The OWNER clause changes the owner of the table to the user <replaceable class="PARAMETER"> @@ -205,18 +204,17 @@ ALTER TABLE <replaceable class="PARAMETER">table</replaceable> You can use the <literal>SET DEFAULT</literal> form of <command>ALTER TABLE</command> to set the default later. (You may also want to update the already existing rows to the - new default value, using <xref linkend="sql-update" - endterm="sql-update-title">.) + new default value, using <xref linkend="sql-update">.) </para> <para> Currently only CHECK constraints can be dropped from a table. The RESTRICT keyword is required, although dependencies are not checked. The CASCADE option is unsupported. To remove a PRIMARY or UNIQUE constraint, drop the - relevant index using the <xref linkend="SQL-DROPINDEX" endterm="SQL-DROPINDEX-TITLE"> command. + relevant index using the <xref linkend="SQL-DROPINDEX"> command. To remove FOREIGN KEY constraints you need to recreate and reload the table, using other parameters to the - <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-title"> + <xref linkend="SQL-CREATETABLE"> command. </para> <para> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 49bb05ea13e..8607f895df2 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1,1976 +1,822 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v 1.47 2001/10/09 18:46:00 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v 1.48 2001/10/22 18:14:47 petere Exp $ Postgres documentation --> <refentry id="SQL-CREATETABLE"> <refmeta> - <refentrytitle id="sql-createtable-title"> - CREATE TABLE - </refentrytitle> + <refentrytitle>CREATE TABLE</refentrytitle> <refmiscinfo>SQL - Language Statements</refmiscinfo> </refmeta> + <refnamediv> - <refname> - CREATE TABLE - </refname> - <refpurpose> - define a new table - </refpurpose> + <refname>CREATE TABLE</refname> + <refpurpose>define a new table</refpurpose> </refnamediv> + <refsynopsisdiv> - <refsynopsisdivinfo> - <date>2001-01-11</date> - </refsynopsisdivinfo> - <synopsis> -CREATE [ TEMPORARY | TEMP ] TABLE <replaceable class="PARAMETER">table_name</replaceable> ( - { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ] - | <replaceable>table_constraint</replaceable> } [, ... ] ) - [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ] - [ WITH OIDS | WITHOUT OIDS ] - -where <replaceable class="PARAMETER">column_constraint</replaceable> can be: +<synopsis> +CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PARAMETER">table_name</replaceable> ( + { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ DEFAULT <replaceable>default_expr</> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [, ... ] ] + | <replaceable>table_constraint</replaceable> } [, ... ] +) +[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ] +[ WITH OIDS | WITHOUT OIDS ] + +where <replaceable class="PARAMETER">column_constraint</replaceable> is: + [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ] -{ NOT NULL | NULL | UNIQUE | PRIMARY KEY | DEFAULT <replaceable class="PARAMETER">value</replaceable> | CHECK (<replaceable class="PARAMETER">condition</replaceable>) | - REFERENCES <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL ] - [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] - [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] -} +{ NOT NULL | NULL | UNIQUE | PRIMARY KEY | + CHECK (<replaceable class="PARAMETER">expression</replaceable>) | + REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL ] + [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] } +[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] + +and <replaceable class="PARAMETER">table_constraint</replaceable> is: -and <replaceable class="PARAMETER">table_constraint</replaceable> can be: [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ] { UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) | PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) | - CHECK ( <replaceable class="PARAMETER">condition</replaceable> ) | - FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ... ] ) ] - [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] - [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] -} - </synopsis> + CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) | + FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ] + [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] } +[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] +</synopsis> - <refsect2 id="R2-SQL-CREATETABLE-1"> - <title> - Inputs - </title> - - <para> - - <variablelist> - <varlistentry> - <term>TEMPORARY or TEMP</term> - <listitem> - <para> - If specified, the table is created only for this session, and is - automatically dropped on session exit. - Existing permanent tables with the same name are not visible - (in this session) while the temporary table exists. - Any indexes created on a temporary table are automatically - temporary as well. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">table_name</replaceable></term> - <listitem> - <para> - The name of the new table to be created. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">column_name</replaceable></term> - <listitem> - <para> - The name of a column to be created in the new table. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">type</replaceable></term> - <listitem> - <para> - The type of the column. This may include array specifiers. - Refer to the <citetitle>PostgreSQL User's Guide</citetitle> for - further information about data types and arrays. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">parent_table</replaceable></term> - <listitem> - <para> - The optional INHERITS clause specifies a list of table - names from which this table automatically inherits all fields. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>WITH OIDS or WITHOUT OIDS</term> - <listitem> - <para> - This optional clause specifies whether rows of the new table should - have OIDs (object identifiers) assigned to them. The default is - WITH OIDS. (If the new table inherits from any tables that have OIDs, - then WITH OIDS is forced even if the command says WITHOUT OIDS.) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">constraint_name</replaceable></term> - <listitem> - <para> - An optional name for a column or table constraint. If not specified, - the system generates a name. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">value</replaceable></term> - <listitem> - <para> - A default value for a column. - See the DEFAULT clause for more information. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">condition</replaceable></term> - <listitem> - <para> - CHECK clauses specify integrity constraints or tests which new or - updated rows must satisfy for an insert or update operation to - succeed. Each constraint must be an expression producing - a boolean result. - A condition appearing within a column definition should reference - that column's value only, while a condition appearing as a table - constraint may reference multiple columns. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">table</replaceable></term> - <listitem> - <para> - The name of an existing table to be referenced by a foreign - key constraint. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">column</replaceable></term> - <listitem> - <para> - The name of a column in an existing table to be referenced by a - foreign key constraint. If not specified, the primary key of - the existing table is assumed. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">action</replaceable></term> - <listitem> - <para> - A keyword indicating the action to take when a foreign key - constraint is violated. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect2> - - <refsect2 id="R2-SQL-CREATETABLE-2"> - <title> - Outputs - </title> - <para> - - <variablelist> - <varlistentry> - <term><computeroutput> -CREATE - </computeroutput></term> - <listitem> - <para> - Message returned if table is successfully created. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><computeroutput> -ERROR - </computeroutput></term> - <listitem> - <para> - Message returned if table creation failed. - This is usually accompanied by some descriptive text, such as: - <computeroutput> -ERROR: Relation '<replaceable class="parameter">table</replaceable>' already exists - </computeroutput> - , which occurs at runtime if the table specified already exists - in the database. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect2> </refsynopsisdiv> - <refsect1 id="R1-SQL-CREATETABLE-1"> - <title> - Description - </title> + <refsect1 id="SQL-CREATETABLE-description"> + <title>Description</title> <para> - <command>CREATE TABLE</command> will enter a new, initially empty table - into the current database. The table will be owned by the user issuing the + <command>CREATE TABLE</command> will create a new, initially empty table + in the current database. The table will be owned by the user issuing the command. </para> <para> - Each <replaceable class="PARAMETER">type</replaceable> - may be a simple type, a complex type (set) or an array type. - Each attribute may be specified to be non-null and - each may have a default value, specified by the - <xref linkend="R1-SQL-DEFAULTCLAUSE-1" endterm="R1-SQL-DEFAULTCLAUSE-1-TITLE">. + <command>CREATE TABLE</command> also automatically creates a data + type that represents the tuple type (structure type) corresponding + to one row of the table. Therefore, tables cannot have the same + name as any existing data type. </para> <para> - <note> - <para> - Consistent array dimensions within an - attribute are not enforced. This will likely change in a future - release. - </para> - </note> + A table cannot have more than 1600 columns. (In practice, the + effective limit is lower because of tuple-length constraints). A + table cannot have the same name as a system catalog table. </para> <para> - <command>CREATE TABLE</command> also automatically creates a data type - that represents the tuple type (structure type) corresponding to one - row of the table. Therefore, tables can't have the same name as any - existing datatype. + The optional constraint clauses specify constraints (or tests) that + new or updated rows must satisfy for an insert or update operation + to succeed. A constraint is a named rule: an SQL object which + helps define valid sets of values by putting limits on the results + of insert, update, or delete operations performed on a table. </para> <para> - A table can have no more than 1600 columns (in practice, the - effective limit is lower because of tuple-length constraints). - A table cannot have the same name as a system catalog table. + There are two ways to define constraints: table constraints and + column constraints. A column constraint is defined as part of a + column definition. A table constraint definition is not tied to a + particular column, and it can encompass more than one column. + Every column constraint can also be written as a table constraint; + a column constraint is only a notational convenience if the + constraint only affects one column. </para> </refsect1> - <refsect1 id="R1-SQL-INHERITSCLAUSE-1"> - <title id="R1-SQL-INHERITSCLAUSE-1-TITLE"> - INHERITS Clause - </title> - <para> - <synopsis> -INHERITS ( <replaceable class="PARAMETER">parent_table</replaceable> [, ... ] ) - </synopsis> - </para> + <refsect1> + <title>Parameters</title> - <para> - The optional INHERITS - clause specifies a list of table names from which the new table - automatically inherits all fields. If the same field name appears in - more than one parent table, Postgres reports an error unless the field - definitions match in each of the parent tables. If there is no - definition conflict, then the duplicate fields are merged to form a single - field of the new table. If the new table's own field list contains a - field name that is also inherited, this declaration must likewise match - the inherited field(s), and the field definitions are merged into one. - </para> - - <para> - Inherited and new field declarations of the same name must specify exactly - the same data type to avoid an error. They need not specify identical - constraints --- all constraints provided from any declaration are merged - together and all are applied to the new table. If the new table explicitly - specifies a default value for the field, this default overrides any - defaults from inherited declarations of the field. Otherwise, any parents - that specify default values for the field must all specify the same - default, or an error will be reported. - </para> + <variablelist> - <para> - Postgres automatically allows the created table to inherit functions on - tables above it in the inheritance hierarchy; that is, if we create table - <literal>foo</literal> inheriting from <literal>bar</literal>, then - functions that accept the tuple type <literal>bar</literal> can also be - applied to instances of <literal>foo</literal>. (Currently, this works - reliably for functions on the first or only parent table, but not so well - for functions on additional parents.) - </para> - </refsect1> - - <refsect1 id="R1-SQL-OIDSCLAUSE-1"> - <title id="R1-SQL-OIDSCLAUSE-1-TITLE"> - OIDS Clause - </title> - <para> - <synopsis> - WITH OIDS | WITHOUT OIDS - </synopsis> - </para> - - <para> - This clause controls whether an OID (object ID) is generated and assigned - to each row inserted into the table. The default is WITH OIDS. - Specifying WITHOUT OIDS allows the user to suppress generation of - OIDs for rows of a table. This may be worthwhile for large - tables, since it will reduce OID consumption and thereby postpone - wraparound of the 32-bit OID counter. Once the counter wraps around, - uniqueness of OIDs can no longer be assumed, which considerably reduces - their usefulness. - </para> - - <para> - Whenever an application makes use of OIDs to identify specific rows of - a table, it is recommended that you create a unique index on OID for - that table, to ensure that OIDs in the table will indeed uniquely - identify rows even after counter wraparound. (An index on OID is needed - anyway for fast lookup of rows by OID.) Avoid assuming that OIDs are - unique across tables --- if you need a database-wide unique identifier, - use the combination of tableoid and row OID for the purpose. (It is - likely that future Postgres releases will use a separate OID counter - for each table, so that it will be <emphasis>necessary</> not optional - to include tableoid to have a unique identifier database-wide.) - </para> - - <tip> - <para> - WITHOUT OIDS is not recommended for tables with no primary key, since - without either an OID or a unique data key, it is difficult to identify - specific rows. - </para> - </tip> - </refsect1> - - <refsect1 id="R1-SQL-DEFAULTCLAUSE-1"> - <title id="R1-SQL-DEFAULTCLAUSE-1-TITLE"> - DEFAULT Clause - </title> - <para> - <synopsis> -DEFAULT <replaceable class="PARAMETER">value</replaceable> - </synopsis> - </para> + <varlistentry> + <term><literal>[LOCAL] TEMPORARY</> or <literal>[LOCAL] TEMP</></term> + <listitem> + <para> + If specified, the table is created as a temporary table. + Temporary tables are automatically dropped at the end of a + session. Existing persistent tables with the same name are not + visible to the current session while the temporary table exists. + Any indexes created on a temporary table are automatically + temporary as well. + </para> - <para> - The DEFAULT clause assigns a default data value for the column whose - column definition it appears within. The value is any variable-free - expression (note that sub-selects and cross-references to other - columns in the current table are not supported). - The data type of a default value must match the column definition's - data type. - </para> + <para> + The <literal>LOCAL</literal> word is optional. But see under + <xref linkend="sql-createtable-compatibility" + endterm="sql-createtable-compatibility-title">. + </para> + </listitem> + </varlistentry> - <para> - The DEFAULT expression will be used in any INSERT operation that does - not specify a value for the column. If there is no DEFAULT clause, - then the default is NULL. - </para> + <varlistentry> + <term><replaceable class="PARAMETER">table_name</replaceable></term> + <listitem> + <para> + The name of the table to be created. + </para> + </listitem> + </varlistentry> - <refsect2 id="R2-SQL-DEFAULTCLAUSE-2"> - <title> - Usage - </title> + <varlistentry> + <term><replaceable class="PARAMETER">column_name</replaceable></term> + <listitem> + <para> + The name of a column to be created in the new table. + </para> + </listitem> + </varlistentry> - <para> - <programlisting> -CREATE TABLE distributors ( - name VARCHAR(40) DEFAULT 'luso films', - did INTEGER DEFAULT NEXTVAL('distributors_serial'), - modtime TIMESTAMP DEFAULT now() -); - </programlisting> - The above assigns a literal constant default value for the column - <literal>name</literal>, and arranges for the default value of column - <literal>did</literal> to be generated by selecting the next value of a - sequence object. The default value of <literal>modtime</literal> will - be the time at which the row is inserted. - </para> + <varlistentry> + <term><replaceable class="PARAMETER">data_type</replaceable></term> + <listitem> + <para> + The data type of the column. This may include array specifiers. + Refer to the <citetitle>User's Guide</citetitle> for further + information about data types and arrays. + </para> + </listitem> + </varlistentry> - <para> - It is worth remarking that - <programlisting> - modtime TIMESTAMP DEFAULT 'now' - </programlisting> - would produce a result that is probably not the intended one: the - string <literal>'now'</literal> will be coerced to a timestamp value - immediately, and so the default value of <literal>modtime</literal> will - always be the time of table creation. This difficulty is avoided by - specifying the default value as a function call. - </para> - </refsect2> - </refsect1> + <varlistentry> + <term><literal>DEFAULT + <replaceable>default_expr</replaceable></literal></term> + <listitem> + <para> + The <literal>DEFAULT</> clause assigns a default data value for + the column whose column definition it appears within. The value + is any variable-free expression (subselects and cross-references + to other columns in the current table are not allowed). The + data type of the default expression must match the data type of the + column. + </para> - <refsect1 id="R1-SQL-COLUMNCONSTRAINT-1"> - <title id="R1-SQL-COLUMNCONSTRAINT-1-TITLE"> - Column Constraints - </title> - <para> - <synopsis> -[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] { - NULL | NOT NULL | UNIQUE | PRIMARY KEY | CHECK <replaceable - class="parameter">condition</replaceable> | - REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] - [ MATCH <replaceable class="parameter">matchtype</replaceable> ] - [ ON DELETE <replaceable class="parameter">action</replaceable> ] - [ ON UPDATE <replaceable class="parameter">action</replaceable> ] - [ [ NOT ] DEFERRABLE ] - [ INITIALLY <replaceable class="parameter">checktime</replaceable> ] } - </synopsis> - </para> + <para> + The default expression will be used in any insert operation that + does not specify a value for the column. If there is no default + for a column, then the default is NULL. + </para> + </listitem> + </varlistentry> - <refsect2 id="R2-SQL-COLUMNCONSTRAINT-1"> - <title> - Inputs - </title> - <para> + <varlistentry> + <term><literal>INHERITS ( <replaceable>parent_table</replaceable> [, ... ] )</literal></term> + <listitem> + <para> + The optional <literal>INHERITS</> clause specifies a list of + tables from which the new table automatically inherits all + columns. If the same column name exists in more than one parent + table, an error is reported unless the data types of the columns + match in each of the parent tables. If there is no conflict, + then the duplicate columns are merged to form a single column in + the new table. If the column name list of the new table + contains a column that is also inherited, the data type must + likewise match the inherited column(s), and the column + definitions are merged into one. However, inherited and new + column declarations of the same name need not specify identical + constraints: all constraints provided from any declaration are + merged together and all are applied to the new table. If the + new table explicitly specifies a default value for the column, + this default overrides any defaults from inherited declarations + of the column. Otherwise, any parents that specify default + values for the column must all specify the same default, or an + error will be reported. + </para> +<!-- + <para> + Postgres automatically allows the created table to inherit + functions on tables above it in the inheritance hierarchy; that + is, if we create table <literal>foo</literal> inheriting from + <literal>bar</literal>, then functions that accept the tuple + type <literal>bar</literal> can also be applied to instances of + <literal>foo</literal>. (Currently, this works reliably for + functions on the first or only parent table, but not so well for + functions on additional parents.) + </para> +--> + </listitem> + </varlistentry> - <variablelist> - <varlistentry> - <term><replaceable class="parameter">constraint_name</replaceable></term> - <listitem> - <para> - An arbitrary name given to a constraint clause. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>NULL</term> - <listitem> - <para> - The column is allowed to contain NULL values. This is the default. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>NOT NULL</term> - <listitem> - <para> - The column is not allowed to contain NULL values. - This is equivalent to the column constraint - CHECK (<replaceable class="PARAMETER">column</replaceable> NOT NULL). - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>UNIQUE</term> - <listitem> - <para> - The column must have unique values. In <productname>Postgres</productname> - this is enforced by automatic creation of a unique index on the column. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>PRIMARY KEY</term> - <listitem> - <para> - This column is a primary key, which implies that other tables may rely - on this column as a unique identifier for rows. Both UNIQUE and - NOT NULL are implied by PRIMARY KEY. See PRIMARY KEY for more - information. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term> -<replaceable class="parameter">condition</replaceable> - </term> - <listitem> - <para> - An arbitrary boolean-valued constraint condition. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect2> + <varlistentry> + <term><literal>WITH OIDS</> or <literal>WITHOUT OIDS</></term> + <listitem> + <para> + This optional clause specifies whether rows of the new table + should have OIDs (object identifiers) assigned to them. The + default is to have OIDs. (If the new table inherits from any + tables that have OIDs, then <literal>WITH OIDS</> is forced even + if the command says <literal>WITHOUT OIDS</>.) + </para> - <refsect2 id="R2-SQL-COLUMNCONSTRAINT-2"> - <title> - Description - </title> + <para> + Specifying <literal>WITHOUT OIDS</> allows the user to suppress + generation of OIDs for rows of a table. This may be worthwhile + for large tables, since it will reduce OID consumption and + thereby postpone wraparound of the 32-bit OID counter. Once the + counter wraps around, uniqueness of OIDs can no longer be + assumed, which considerably reduces their usefulness. + </para> + </listitem> + </varlistentry> - <para> - The optional constraint clauses specify constraints or tests which - new or updated rows must satisfy for an insert or update - operation to succeed. - </para> + <varlistentry> + <term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term> + <listitem> + <para> + An optional name for a column or table constraint. If not specified, + the system generates a name. + </para> + </listitem> + </varlistentry> - <para> - A constraint is a named rule: an SQL object which helps define - valid sets of values by putting limits on the results of INSERT, - UPDATE or DELETE operations performed on a table. - </para> + <varlistentry> + <term><literal>NOT NULL</></term> + <listitem> + <para> + The column is not allowed to contain NULL values. This is + equivalent to the column constraint <literal>CHECK (<replaceable + class="PARAMETER">column</replaceable> NOT NULL)</literal>. + </para> + </listitem> + </varlistentry> - <para> - There are two ways to define integrity constraints: - table constraints, covered later, and column constraints, covered here. - </para> + <varlistentry> + <term><literal>NULL</></term> + <listitem> + <para> + The column is allowed to contain NULL values. This is the default. + </para> - <para> - A column constraint is an integrity constraint defined as part of a - column definition, and logically becomes a table constraint as soon - as it is created. The column constraints available are: - - <simplelist columns="1"> - <member>PRIMARY KEY</member> - <member>REFERENCES</member> - <member>UNIQUE</member> - <member>CHECK</member> - <member>NOT NULL</member> - </simplelist> - </para> - </refsect2> + <para> + This clause is only available for compatibility with + non-standard SQL databases. Its use is discouraged in new + applications. + </para> + </listitem> + </varlistentry> - <refsect2 id="R2-SQL-NOTNULL-1"> - <title> - NOT NULL Constraint - </title> - <synopsis> -[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] NOT NULL - </synopsis> - <para> - The NOT NULL constraint specifies a rule that a column may - contain only non-null values. - This is a column constraint only, and not allowed - as a table constraint. - </para> - - <refsect3 id="R3-SQL-NOTNULL-1"> - <title> - Outputs - </title> - <para> - <variablelist> - <varlistentry> - <term><replaceable>status</replaceable></term> - <listitem> - <para> - <variablelist> - <varlistentry> - <term><computeroutput> -ERROR: ExecAppend: Fail to add null value in not null attribute "<replaceable class="parameter">column</replaceable>". - </computeroutput></term> - <listitem> - <para> - This error occurs at runtime if one tries to insert a null value - into a column which has a NOT NULL constraint. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> - - <refsect3 id="R3-SQL-NOTNULL-2"> - <title> - Description - </title> - <para> - </para> - </refsect3> - - <refsect3 id="R3-SQL-NOTNULL-3"> - <title> - Usage - </title> - - <para> - Define two NOT NULL column constraints on the table - <classname>distributors</classname>, - one of which is explicitly given a name: + <varlistentry> + <term><literal>UNIQUE</> (column constraint)</term> + <term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term> - <programlisting> -CREATE TABLE distributors ( - did DECIMAL(3) CONSTRAINT no_null NOT NULL, - name VARCHAR(40) NOT NULL -); - </programlisting> - </para> - </refsect3> - </refsect2> + <listitem> + <para> + The <literal>UNIQUE</literal> constraint specifies a rule that a + group of one or more distinct columns of a table may contain + only unique values. The behavior of the unique table constraint + is the same as that for column constraints, with the additional + capability to span multiple columns. + </para> - <refsect2 id="R2-SQL-UNIQUECLAUSE-1"> - <title> - UNIQUE Constraint - </title> - <synopsis> -[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] UNIQUE - </synopsis> + <para> + For the purpose of a unique constraint, NULL values are not + considered equal. + </para> - <refsect3> - <title>Inputs</title> - <para> + <para> + Each unique table constraint must name a set of columns that is + different from the set of columns named by any other unique or + primary key constraint defined for the table. (Otherwise it + would just be the same constraint listed twice.) + </para> + </listitem> + </varlistentry> - <variablelist> - <varlistentry> - <term><replaceable class="parameter">constraint_name</replaceable></term> - <listitem> - <para> - An arbitrary name given to a constraint clause. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> - - <refsect3> - <title>Outputs</title> - <para> - <variablelist> - <varlistentry> - <term><replaceable>status</replaceable></term> - <listitem> - <para> - <variablelist> - <varlistentry> - <term><computeroutput> -ERROR: Cannot insert a duplicate key into a unique index. - </computeroutput></term> - <listitem> - <para> - This error occurs at runtime if one tries to insert a - duplicate value into a column. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> + <varlistentry> + <term><literal>PRIMARY KEY</> (column constraint)</term> + <term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term> + <listitem> + <para> + The primary key constraint specifies that a column or columns of a table + may contain only unique (non-duplicate), non-NULL values. + Technically, <literal>PRIMARY KEY</literal> is merely a + combination of <literal>UNIQUE</> and <literal>NOT NULL</>, but + identifying a set of columns as primary key also provides + meta-data about the design of the schema, as a primary key + implies that other tables + may rely on this set of columns as a unique identifier for rows. + </para> - <refsect3> - <title> - Description - </title> + <para> + Only one primary key can be specified for a table, whether as a + column constraint or a table constraint. + </para> - <para> - The UNIQUE constraint specifies a rule that a group of one or - more distinct columns of a table may contain only unique values. - </para> - <para> - The column definitions of the specified columns do not have to - include a NOT NULL constraint to be included in a UNIQUE - constraint. Having more than one null value in a column without a - NOT NULL constraint, does not violate a UNIQUE constraint. (This - deviates from the <acronym>SQL92</acronym> definition, but is a - more sensible convention. See the section on compatibility for more - details.) - </para> - <para> - Each UNIQUE column constraint must name a column that is - different from the set of columns named by any other UNIQUE or - PRIMARY KEY constraint defined for the table. - </para> - <note> <para> - <productname>Postgres</productname> automatically creates a unique - index for each UNIQUE constraint, to assure - data integrity. See CREATE INDEX for more information. + The primary key constraint should name a set of columns that is + different from other sets of columns named by any unique + constraint defined for the same table. </para> - </note> - </refsect3> + </listitem> + </varlistentry> - <refsect3 id="R3-SQL-UNIQUECLAUSE-3"> - <title> - Usage - </title> + <varlistentry> + <term><literal>CHECK (<replaceable class="PARAMETER">expression</replaceable>)</literal></term> + <listitem> + <para> + <literal>CHECK</> clauses specify integrity constraints or tests + which new or updated rows must satisfy for an insert or update + operation to succeed. Each constraint must be an expression + producing a Boolean result. A condition appearing within a + column definition should reference that column's value only, + while a condition appearing as a table constraint may reference + multiple columns. + </para> - <para> - Defines a UNIQUE constraint for the <literal>name</literal> column: - <programlisting> -CREATE TABLE distributors ( - did DECIMAL(3), - name VARCHAR(40) UNIQUE -); - </programlisting> + <para> + Currently, <literal>CHECK</literal> expressions cannot contain + subselects nor refer to variables other than columns of the + current row. + </para> - which is equivalent to the following specified as a table constraint: - <programlisting> -CREATE TABLE distributors ( - did DECIMAL(3), - name VARCHAR(40), - UNIQUE(name) -); - </programlisting> - </para> - </refsect3> - </refsect2> + </listitem> + </varlistentry> - <refsect2 id="R2-SQL-CHECK-1"> - <title> - The CHECK Constraint - </title> - <synopsis> -[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] CHECK ( <replaceable>condition</replaceable> ) - </synopsis> - <refsect3 id="R3-SQL-CHECK-1"> - <title>Inputs</title> - <para> - <variablelist> - <varlistentry> - <term><replaceable class="parameter">constraint_name</replaceable></term> - <listitem> - <para> - An arbitrary name given to a constraint clause. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable>condition</replaceable></term> - <listitem> - <para> - Any valid conditional expression evaluating to a boolean result. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> + <varlistentry> + <term><literal>REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal> (column constraint)</term> - <refsect3 id="R3-SQL-CHECK-2"> - <title> - Outputs - </title> - <para> + <term><literal>FOREIGN KEY ( <replaceable class="parameter">column</replaceable> [, ... ] ) + REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ] + [ MATCH <replaceable class="parameter">matchtype</replaceable> ] + [ ON DELETE <replaceable class="parameter">action</replaceable> ] + [ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal> + (table constraint)</term> - <variablelist> - <varlistentry> - <term><replaceable>status</replaceable></term> - <listitem> - <para> - - <variablelist> - <varlistentry> - <term><computeroutput> -ERROR: ExecAppend: rejected due to CHECK constraint "<replaceable class="parameter">constraint_name</replaceable>". - </computeroutput></term> - <listitem> - <para> - This error occurs at runtime if one tries to insert an illegal - value into a column subject to a CHECK constraint. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> + <listitem> + <para> + The <literal>REFERENCES</literal> column constraint specifies + that a group of one or more columns of the new table must only + contain values which match against values in the referenced + column(s) <replaceable class="parameter">refcolumn</replaceable> + of the referenced table <replaceable + class="parameter">reftable</replaceable>. If <replaceable + class="parameter">refcolumn</replaceable> is omitted, the + primary key of the <replaceable + class="parameter">reftable</replaceable> is used. The + referenced columns must be the columns of a unique or primary + key constraint in the referenced table. + </para> - <refsect3> - <title>Description</title> - <para> - The CHECK constraint specifies a generic restriction on allowed values - within a column. The CHECK constraint is also allowed as a table - constraint. - </para> - <para> - CHECK specifies a general boolean expression involving one or more - columns of a table. A new row will be rejected if the boolean - expression evaluates to FALSE when applied to the row's values. - </para> - <para> - Currently, CHECK expressions cannot contain sub-selects nor refer - to variables other than fields of the current row. - </para> - <para> - The SQL92 standard says that CHECK column constraints may only refer - to the column they apply to; only CHECK table constraints may refer - to multiple columns. - <productname>Postgres</productname> does not enforce this restriction. - It treats column and table CHECK constraints alike. - </para> - </refsect3> - </refsect2> - - <refsect2 id="R2-SQL-PRIMARYKEY-1"> - <title> - PRIMARY KEY Constraint - </title> - <synopsis> -[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ] PRIMARY KEY - </synopsis> - - <refsect3> - <title>Inputs</title> - <para> - <variablelist> - <varlistentry> - <term><replaceable class="PARAMETER">constraint_name</replaceable></term> - <listitem> - <para> - An arbitrary name given to a constraint clause. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> - - <refsect3> - <title>Outputs</title> - <variablelist> - <varlistentry> - <term><computeroutput> -ERROR: Cannot insert a duplicate key into a unique index. - </computeroutput></term> - <listitem> - <para> - This occurs at runtime if one tries to insert a duplicate value into - a column subject to a PRIMARY KEY constraint. - </para> - </listitem> - </varlistentry> - </variablelist> - </refsect3> - - <refsect3> - <title>Description</title> - <para> - The PRIMARY KEY column constraint specifies that a column of a - table may contain only unique (non-duplicate), non-NULL values. The - definition of the specified column does not have to include an - explicit NOT NULL constraint to be included in a PRIMARY KEY - constraint. - </para> - <para> - Only one PRIMARY KEY can be specified for a table, whether as a - column constraint or a table constraint. - </para> - </refsect3> + <para> + A value added to these columns is matched against the values of + the referenced table and referenced columns using the given + match type. There are three match types: <literal>MATCH + FULL</>, <literal>MATCH PARTIAL</>, and a default match type if + none is specified. <literal>MATCH FULL</> will not allow one + column of a multi-column foreign key to be NULL unless all + foreign key columns are NULL. The default match type allows some + foreign key columns to be NULL while other parts of the foreign + key are not NULL. <literal>MATCH PARTIAL</> is not yet + implemented. + </para> - <refsect3 id="R3-SQL-PRIMARYKEY-3"> - <title> - Notes - </title> - <para> - <productname>Postgres</productname> automatically creates - a unique index to assure - data integrity (see CREATE INDEX statement). - </para> - <para> - The PRIMARY KEY constraint should name a set of columns that is - different from other sets of columns named by any UNIQUE constraint - defined for the same table, since it will result in duplication - of equivalent indexes and unproductive additional runtime overhead. - However, <productname>Postgres</productname> does not specifically - disallow this. - </para> - </refsect3> - </refsect2> + <para> + In addition, when the data in the referenced columns is changed, + certain actions are performed on the data in this table's + columns. The <literal>ON DELETE</literal> clause specifies the + action to do when a referenced row in the referenced table is + being deleted. Likewise, the <literal>ON UPDATE</literal> + clause specifies the action to perform when a referenced column + in the referenced table is being updated to a new value. If the + row is updated, but the referenced column is not actually + changed, no action is done. There are the following possible + actions for each clause: + + <variablelist> + <varlistentry> + <term><literal>NO ACTION</literal></term> + <listitem> + <para> + Produce an error indicating that the deletion or update + would create a foreign key constraint violation. This is + the default action. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>RESTRICT</literal></term> + <listitem> + <para> + Same as <literal>NO ACTION</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>CASCADE</literal></term> + <listitem> + <para> + Delete any rows referencing the deleted row, or update the + value of the referencing column to the new value of the + referenced column, respectively. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>SET NULL</literal></term> + <listitem> + <para> + Set the referencing column values to NULL. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>SET DEFAULT</literal></term> + <listitem> + <para> + Set the referencing column values to their default value. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> - <refsect2 id="R2-SQL-REFERENCES-1"> - <refsect2info> - <date>2000-02-04</date> - </refsect2info> - <title> - REFERENCES Constraint - </title> - <synopsis> -[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] - [ MATCH <replaceable class="parameter">matchtype</replaceable> ] - [ ON DELETE <replaceable class="parameter">action</replaceable> ] - [ ON UPDATE <replaceable class="parameter">action</replaceable> ] - [ [ NOT ] DEFERRABLE ] - [ INITIALLY <replaceable class="parameter">checktime</replaceable> ] - </synopsis> - <para> - The REFERENCES constraint specifies a rule that a column - value is checked against the values of another column. - REFERENCES can also be specified as part of - a FOREIGN KEY table constraint. - </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DEFERRABLE</literal> or <literal>NOT DEFERRABLE</literal></term> + <listitem> + <para> + This controls whether the constraint can be deferred. A + constraint that is not deferrable will be checked immediately + after every command. Checking of constraints that are + deferrable may be postponed until the end of the transaction + (using the <xref linkend="sql-set-constraints"> command). + <literal>NOT DEFERRABLE</literal> is the default. Only foreign + key constraints currently accept this clause. All other + constraint types are not deferrable. + </para> + </listitem> + </varlistentry> - <refsect3 id="R3-SQL-REFERENCES-1"> - <title>Inputs</title> + <varlistentry> + <term><literal>INITIALLY IMMEDIATE</literal> or <literal>INITIALLY DEFERRED</literal></term> + <listitem> + <para> + If a constraint is deferrable, this clause specifies the default + time to check the constraint. If the constraint is + <literal>INITIALLY IMMEDIATE</literal>, it is checked after each + statement. This is the default. If the constraint is + <literal>INITIALLY DEFERRED</literal>, it is checked only at the + end of the transaction. The constraint check time can be + altered with the <xref linkend="sql-set-constraints"> command. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + + <refsect1 id="SQL-CREATETABLE-diagnostics"> + <title>Diagnostics</title> + + <msgset> + <msgentry> + <msg> + <msgmain> + <msgtext> + <simpara><computeroutput>CREATE</computeroutput></simpara> + </msgtext> + </msgmain> + </msg> + + <msgexplan> + <para> + Message returned if table is successfully created. + </para> + </msgexplan> + </msgentry> - <para> - <variablelist> - <varlistentry> - <term><replaceable class="PARAMETER">constraint_name</replaceable></term> - <listitem> - <para> - An arbitrary name given to a constraint clause. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">reftable</replaceable></term> - <listitem> - <para> - The table that contains the data to check against. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">refcolumn</replaceable></term> - <listitem> - <para> - The column in <replaceable class="parameter">reftable</replaceable> - to check the data against. If this is not specified, the PRIMARY KEY of the - <replaceable class="parameter">reftable</replaceable> is used. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>MATCH <replaceable class="parameter">matchtype</replaceable></term> - <listitem> - <para> - There are three match types: MATCH FULL, MATCH PARTIAL, and a - default match type if none is specified. MATCH FULL will not - allow one column of a multi-column foreign key to be NULL - unless all foreign key columns are NULL. The default MATCH type - allows some foreign key columns to be NULL while other parts - of the foreign key are not NULL. MATCH PARTIAL is currently not - supported. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>ON DELETE <replaceable class="parameter">action</replaceable></term> - <listitem> - <para> - The action to do when a referenced row in the referenced table is being - deleted. There are the following actions. - <variablelist> - <varlistentry> - <term>NO ACTION</term> - <listitem> - <para> - Produce error if foreign key violated. This is the default. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>RESTRICT</term> - <listitem> - <para> - Same as NO ACTION. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>CASCADE</term> - <listitem> - <para> - Delete any rows referencing the deleted row. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>SET NULL</term> - <listitem> - <para> - Set the referencing column values to NULL. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>SET DEFAULT</term> - <listitem> - <para> - Set the referencing column values to their default value. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>ON UPDATE <replaceable class="parameter">action</replaceable></term> - <listitem> - <para> - The action to do when a referenced column in the referenced - table is being updated to a new value. If the row is updated, - but the referenced column is not changed, no action is done. - There are the following actions. - <variablelist> - <varlistentry> - <term>NO ACTION</term> - <listitem> - <para> - Produce error if foreign key violated. This is the default. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>RESTRICT</term> - <listitem> - <para> - Same as NO ACTION. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>CASCADE</term> - <listitem> - <para> - Update the value of the referencing column to the new value of the - referenced column. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>SET NULL</term> - <listitem> - <para> - Set the referencing column values to NULL. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>SET DEFAULT</term> - <listitem> - <para> - Set the referencing column values to their default value. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term> [ NOT ] DEFERRABLE </term> - <listitem> - <para> - This controls whether the constraint can be deferred to the end - of the transaction. If DEFERRABLE, SET CONSTRAINTS ALL DEFERRED - will cause the foreign key to be checked only at the end of the - transaction. NOT DEFERRABLE is the default. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>INITIALLY <replaceable class="parameter">checktime</replaceable></term> - <listitem> - <para> - <replaceable class="parameter">checktime</replaceable> has two possible values - which specify the default time to check the constraint. - <variablelist> - <varlistentry> - <term>DEFERRED</term> - <listitem> - <para> - Check constraint only at the end of the transaction. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>IMMEDIATE</term> - <listitem> - <para> - Check constraint after each statement. This is the default. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> - - <refsect3 id="R3-SQL-REFERENCES-2"> - <refsect3info> - <date>2000-02-04</date> - </refsect3info> - <title> - Outputs - </title> - <para> - <variablelist> - <varlistentry> - <term><replaceable>status</replaceable></term> - <listitem> - <para> - <variablelist> - <varlistentry> - <term><computeroutput> -ERROR: <replaceable class="parameter">name</replaceable> referential integrity violation - key referenced from -<replaceable class="parameter">table</replaceable> not found in <replaceable class="parameter">reftable</replaceable> - </computeroutput></term> - <listitem> - <para> - This error occurs at runtime if one tries to insert a value - into a column which does not have a matching column in the - referenced table. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> + <msgentry> + <msg> + <msgmain> + <msgtext> + <simpara><computeroutput>ERROR</computeroutput></simpara> + </msgtext> + </msgmain> + </msg> - <refsect3 id="R3-SQL-REFERENCES-3"> - <title>Description</title> - <para> - The REFERENCES column constraint specifies that a - column of a table must only contain values which match against - values in a referenced column of a referenced table. - </para> - <para> - A value added to this column is matched against the values of the - referenced table and referenced column using the given match type. - In addition, when the referenced column data is changed, actions - are run upon this column's matching data. - </para> - </refsect3> - - <refsect3 id="R3-SQL-REFERENCES-4"> - <refsect3info> - <date>1998-09-11</date> - </refsect3info> - <title> - Notes - </title> - <para> - Currently <productname>Postgres</productname> only supports MATCH - FULL and a default match type. In addition, the referenced - columns are supposed to be the columns of a UNIQUE constraint in - the referenced table, however <productname>Postgres</productname> - does not enforce this. - </para> - </refsect3> - </refsect2> + <msgexplan> + <para> + Message returned if table creation failed. This is usually + accompanied by some descriptive text, such as: + <computeroutput>ERROR: Relation '<replaceable + class="parameter">table</replaceable>' already + exists</computeroutput>, which occurs at runtime if the table + specified already exists in the database. + </para> + </msgexplan> + </msgentry> + </msgset> </refsect1> - - <refsect1 id="R1-SQL-TABLECONSTRAINT-1"> - <title> - Table Constraints - </title> - <para> - <synopsis> -[ CONSTRAINT name ] { PRIMARY KEY | UNIQUE } ( <replaceable class="parameter">column</replaceable> [, ... ] ) -[ CONSTRAINT name ] CHECK ( <replaceable>constraint</replaceable> ) -[ CONSTRAINT name ] FOREIGN KEY ( <replaceable class="parameter">column</replaceable> [, ... ] ) - REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ] - [ MATCH <replaceable class="parameter">matchtype</replaceable> ] - [ ON DELETE <replaceable class="parameter">action</replaceable> ] - [ ON UPDATE <replaceable class="parameter">action</replaceable> ] - [ [ NOT ] DEFERRABLE ] - [ INITIALLY <replaceable class="parameter">checktime</replaceable> ] - </synopsis> - </para> - <refsect2 id="R2-SQL-TABLECONSTRAINT-1"> - <title> - Inputs - </title> - <para> - <variablelist> - <varlistentry> - <term><replaceable class="parameter">constraint_name</replaceable></term> - <listitem> - <para> - An arbitrary name given to a constraint clause. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable class="parameter">column</replaceable> [, ... ]</term> - <listitem> - <para> - The column name(s) for which to define a unique index - and, for PRIMARY KEY, a NOT NULL constraint. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>CHECK ( <replaceable class="parameter">constraint</replaceable> )</term> - <listitem> - <para> - A boolean expression to be evaluated as the constraint. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect2> - - <refsect2 id="R2-SQL-TABLECONSTRAINT-2"> - <title> - Outputs - </title> - - <para> - The possible outputs for the table constraint clause are the same - as for the corresponding portions of the column constraint clause. - </para> - </refsect2> - - <refsect2 id="R2-SQL-TABLECONSTRAINT-3"> - <title> - Description - </title> - - <para> - A table constraint is an integrity constraint defined on one or - more columns of a table. The four variations of <quote>Table - Constraint</quote> are: - <simplelist columns="1"> - <member>UNIQUE</member> - <member>CHECK</member> - <member>PRIMARY KEY</member> - <member>FOREIGN KEY</member> - </simplelist> - </para> - </refsect2> - - <refsect2 id="R2-SQL-UNIQUECLAUSE-4"> - <title> - UNIQUE Constraint - </title> - <para> - <synopsis> -[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] UNIQUE ( <replaceable class="parameter">column</replaceable> [, ... ] ) - </synopsis> - </para> - <refsect3> - <title>Inputs</title> - <variablelist> - <varlistentry> - <term><replaceable class="parameter">constraint_name</replaceable></term> - <listitem> - <para> - An arbitrary name given to a constraint clause. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable class="parameter">column</replaceable></term> - <listitem> - <para> - A name of a column in a table. - </para> - </listitem> - </varlistentry> - </variablelist> - </refsect3> - - <refsect3> - <title>Outputs</title> - <para> - <variablelist> - <varlistentry> - <term><replaceable>status</replaceable></term> - <listitem> - <para> - <variablelist> - <varlistentry> - <term>ERROR: Cannot insert a duplicate key into a unique index</term> - <listitem> - <para> - This error occurs at runtime if one tries to insert a - duplicate value into a column. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> - - <refsect3> - <title> - Description - </title> - - <para> - The UNIQUE constraint specifies a rule that a group of one or more - distinct columns of a table may contain only unique values. The - behavior of the UNIQUE table constraint is the same as that for - column constraints, with the additional capability to span multiple - columns. - </para> - <para> - See the section on the UNIQUE column constraint for more details. - </para> - </refsect3> + <refsect1 id="SQL-CREATETABLE-notes"> + <title>Notes</title> - <refsect3 id="R3-SQL-UNIQUECLAUSE-4"> - <title> - Usage - </title> - + <itemizedlist> + <listitem> <para> - Prevent duplicate rows in the table distributors: - <programlisting> -CREATE TABLE distributors ( - did DECIMAL(3), - name VARCHAR(40), - UNIQUE(did,name) -); - </programlisting> + Whenever an application makes use of OIDs to identify specific + rows of a table, it is recommended to create a unique constraint + on the <structfield>oid</> column of that table, to ensure that + OIDs in the table will indeed uniquely identify rows even after + counter wraparound. Avoid assuming that OIDs are unique across + tables; if you need a database-wide unique identifier, use the + combination of <structfield>tableoid</> and row OID for the + purpose. (It is likely that future <productname>PostgreSQL</> + releases will use a separate OID counter for each table, so that + it will be <emphasis>necessary</>, not optional, to include + <structfield>tableoid</> to have a unique identifier + database-wide.) </para> - </refsect3> - </refsect2> - <refsect2 id="R2-SQL-PRIMARYKEY-4"> - <title> - PRIMARY KEY Constraint - </title> - <para> - <synopsis> -[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ] PRIMARY KEY ( <replaceable class="PARAMETER">column</replaceable> [, ... ] ) - </synopsis> - </para> - <refsect3> - <title>Inputs</title> - <para> + <tip> + <para> + The use of <literal>WITHOUT OIDS</literal> is not recommended + for tables with no primary key, since without either an OID or a + unique data key, it is difficult to identify specific rows. + </para> + </tip> + </listitem> - <variablelist> - <varlistentry> - <term><replaceable class="PARAMETER">constraint_name</replaceable></term> - <listitem> - <para> - An arbitrary name given to a constraint clause. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable class="PARAMETER">column</replaceable> [, ... ]</term> - <listitem> - <para> - The names of one or more columns in the table. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> - - <refsect3> - <title>Outputs</title> - <variablelist> - <varlistentry> - <term><replaceable>status</replaceable></term> - <listitem> - <para> - <variablelist> - <varlistentry> - <term>ERROR: Cannot insert a duplicate key into a unique index.</term> - <listitem> - <para> - This occurs at run-time if one tries to insert a duplicate - value into a column subject to a PRIMARY KEY constraint. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - </variablelist> - </refsect3> - - <refsect3> - <title>Description</title> + <listitem> <para> - The PRIMARY KEY constraint specifies a rule that a group of one - or more distinct columns of a table may contain only unique - (nonduplicate), non-null values. The column definitions of - the specified columns do not have to include a NOT NULL - constraint to be included in a PRIMARY KEY constraint. + <productname>PostgreSQL</productname> automatically creates an + index for each unique constraint and primary key constraint to + enforce the uniqueness. Thus, it is not necessary to create an + explicit index for primary key columns. (See <xref + linkend="sql-createindex"> for more information.) </para> + </listitem> + <listitem> <para> - The PRIMARY KEY table constraint is similar to that for column constraints, - with the additional capability of encompassing multiple columns. + The SQL92 standard says that <literal>CHECK</> column constraints + may only refer to the column they apply to; only + <literal>CHECK</> table constraints may refer to multiple + columns. <productname>PostgreSQL</productname> does not enforce + this restriction; it treats column and table check constraints + alike. </para> - <para> - Refer to the section on the PRIMARY KEY column constraint for more - information. - </para> - </refsect3> - </refsect2> + </listitem> - <refsect2 id="R2-SQL-REFERENCES-2"> - <refsect2info> - <date>2000-02-04</date> - </refsect2info> - <title> - REFERENCES Constraint - </title> - <synopsis> -[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] FOREIGN KEY ( <replaceable class="parameter">column</replaceable> [, ... ] ) - REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ] - [ MATCH <replaceable class="parameter">matchtype</replaceable> ] - [ ON DELETE <replaceable class="parameter">action</replaceable> ] - [ ON UPDATE <replaceable class="parameter">action</replaceable> ] - [ [ NOT ] DEFERRABLE ] - [ INITIALLY <replaceable class="parameter">checktime</replaceable> ] - </synopsis> - <para> - The REFERENCES constraint specifies a rule that a column value or set - of column values is - checked against the values in another table. - </para> - - <refsect3 id="R3-SQL-REFERENCES-5"> - <title>Inputs</title> + <listitem> <para> - <variablelist> - <varlistentry> - <term><replaceable class="PARAMETER">constraint_name</replaceable></term> - <listitem> - <para> - An arbitrary name given to a constraint clause. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable class="PARAMETER">column</replaceable> [, ... ]</term> - <listitem> - <para> - The names of one or more columns in the table. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable class="parameter">reftable</replaceable></term> - <listitem> - <para> - The table that contains the data to check against. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable class="parameter">referenced column</replaceable> [, ... ]</term> - <listitem> - <para> - One or more columns in the <replaceable class="parameter">reftable</replaceable> - to check the data against. If this is not specified, the PRIMARY KEY of the - <replaceable class="parameter">reftable</replaceable> is used. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>MATCH <replaceable class="parameter">matchtype</replaceable></term> - <listitem> - <para> - There are three match types: MATCH FULL, MATCH PARTIAL, and a - default match type if none is specified. MATCH FULL will not - allow one column of a multi-column foreign key to be NULL - unless all foreign key columns are NULL. The default MATCH type - allows some foreign key columns to be NULL while other parts - of the foreign key are not NULL. MATCH PARTIAL is currently not - supported. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>ON DELETE <replaceable class="parameter">action</replaceable></term> - <listitem> - <para> - The action to do when a referenced row in the referenced table is being - deleted. There are the following actions. - <variablelist> - <varlistentry> - <term>NO ACTION</term> - <listitem> - <para> - Produce error if foreign key violated. This is the default. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>RESTRICT</term> - <listitem> - <para> - Same as NO ACTION. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>CASCADE</term> - <listitem> - <para> - Delete any rows referencing the deleted row. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>SET NULL</term> - <listitem> - <para> - Set the referencing column values to NULL. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>SET DEFAULT</term> - <listitem> - <para> - Set the referencing column values to their default value. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>ON UPDATE <replaceable class="parameter">action</replaceable></term> - <listitem> - <para> - The action to do when a referenced column in the referenced - table is being updated to a new value. If the row is updated, - but the referenced column is not changed, no action is done. - There are the following actions. - <variablelist> - <varlistentry> - <term>NO ACTION</term> - <listitem> - <para> - Produce error if foreign key violated. This is the default. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>RESTRICT</term> - <listitem> - <para> - Disallow update of row being referenced. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>CASCADE</term> - <listitem> - <para> - Update the value of the referencing column to the new value - of the referenced column. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>SET NULL</term> - <listitem> - <para> - Set the referencing column values to NULL. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>SET DEFAULT</term> - <listitem> - <para> - Set the referencing column values to their default value. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - <varlistentry> - <term> [ NOT ] DEFERRABLE </term> - <listitem> - <para> - This controls whether the constraint can be deferred to the end - of the transaction. If DEFERRABLE, SET CONSTRAINTS ALL DEFERRED - will cause the foreign key to be checked only at the end of the - transaction. NOT DEFERRABLE is the default. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>INITIALLY <replaceable class="parameter">checktime</replaceable></term> - <listitem> - <para> - <replaceable class="parameter">checktime</replaceable> has two - possible values which specify the default time to check the - constraint. - <variablelist> - <varlistentry> - <term>IMMEDIATE</term> - <listitem> - <para> - Check constraint after each statement. This is the default. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>DEFERRED</term> - <listitem> - <para> - Check constraint only at the end of the transaction. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> - <refsect3 id="R3-SQL-REFERENCES-6"> - <refsect3info> - <date>2000-02-04</date> - </refsect3info> - <title> - Outputs - </title> - <para> - <variablelist> - <varlistentry> - <term><replaceable>status</replaceable></term> - <listitem> - <para> - <variablelist> - <varlistentry> - <term><computeroutput> -ERROR: <replaceable class="parameter">name</replaceable> referential integrity violation - key referenced from -<replaceable class="parameter">table</replaceable> not found in <replaceable class="parameter">reftable</replaceable> - </computeroutput></term> - <listitem> - <para> - This error occurs at runtime if one tries to insert a value - into a column which does not have a matching column in the - referenced table. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> - <refsect3> - <title>Description</title> - <para> - The FOREIGN KEY constraint specifies a rule that a group of one - or more distinct columns of a table is related to a group - of distinct columns in the referenced table. + Unique constraints and primary keys are not inherited in the + current implementation. This makes the combination of + inheritance and unique constraints rather disfunctional. </para> + </listitem> + </itemizedlist> + </refsect1> - <para> - The FOREIGN KEY table constraint is similar to that for column - constraints, with the additional capability of encompassing - multiple columns. - </para> - <para> - Refer to the section on the FOREIGN KEY column constraint for more - information. - </para> - </refsect3> - - </refsect2> - </refsect1> - - <refsect1 id="R1-SQL-CREATETABLE-2"> - <title> - Usage - </title> + <refsect1 id="SQL-CREATETABLE-examples"> + <title>Examples</title> + <para> - Create table films and table distributors: + Create table <structname>films</> and table + <structname>distributors</>: - <programlisting> +<programlisting> CREATE TABLE films ( - code CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY, - title CHARACTER VARYING(40) NOT NULL, - did DECIMAL(3) NOT NULL, - date_prod DATE, - kind CHAR(10), - len INTERVAL HOUR TO MINUTE + code CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY, + title CHARACTER VARYING(40) NOT NULL, + did DECIMAL(3) NOT NULL, + date_prod DATE, + kind CHAR(10), + len INTERVAL HOUR TO MINUTE ); - </programlisting> +</programlisting> - <programlisting> +<programlisting> CREATE TABLE distributors ( - did DECIMAL(3) PRIMARY KEY DEFAULT NEXTVAL('serial'), - name VARCHAR(40) NOT NULL CHECK (name <> '') + did DECIMAL(3) PRIMARY KEY DEFAULT NEXTVAL('serial'), + name VARCHAR(40) NOT NULL CHECK (name <> '') ); - </programlisting> +</programlisting> </para> <para> Create a table with a 2-dimensional array: - <programlisting> - CREATE TABLE array ( - vector INT[][] - ); - </programlisting> +<programlisting> +CREATE TABLE array ( + vector INT[][] +); +</programlisting> </para> <para> - Define a UNIQUE table constraint for the table films. - UNIQUE table constraints can be defined on one or more - columns of the table: + Define a unique table constraint for the table films. Unique table + constraints can be defined on one or more columns of the table: - <programlisting> +<programlisting> CREATE TABLE films ( - code CHAR(5), - title VARCHAR(40), - did DECIMAL(3), - date_prod DATE, - kind CHAR(10), - len INTERVAL HOUR TO MINUTE, + code CHAR(5), + title VARCHAR(40), + did DECIMAL(3), + date_prod DATE, + kind VARCHAR(10), + len INTERVAL HOUR TO MINUTE, CONSTRAINT production UNIQUE(date_prod) ); - </programlisting> +</programlisting> </para> <para> - Define a CHECK column constraint: + Define a check column constraint: - <programlisting> +<programlisting> CREATE TABLE distributors ( - did DECIMAL(3) CHECK (did > 100), - name VARCHAR(40) + did DECIMAL(3) CHECK (did > 100), + name VARCHAR(40) ); - </programlisting> +</programlisting> </para> <para> - Define a CHECK table constraint: + Define a check table constraint: - <programlisting> +<programlisting> CREATE TABLE distributors ( - did DECIMAL(3), - name VARCHAR(40) - CONSTRAINT con1 CHECK (did > 100 AND name > '') + did DECIMAL(3), + name VARCHAR(40) + CONSTRAINT con1 CHECK (did > 100 AND name <> '') ); - </programlisting> +</programlisting> </para> <para> - Define a PRIMARY KEY table constraint for the table films. - PRIMARY KEY table constraints can be defined on one or more - columns of the table: + Define a primary key table constraint for the table + <structname>films</>. Primary key table constraints can be defined + on one or more columns of the table. - <programlisting> +<programlisting> CREATE TABLE films ( - code CHAR(5), - title VARCHAR(40), - did DECIMAL(3), - date_prod DATE, - kind CHAR(10), - len INTERVAL HOUR TO MINUTE, + code CHAR(5), + title VARCHAR(40), + did DECIMAL(3), + date_prod DATE, + kind VARCHAR(10), + len INTERVAL HOUR TO MINUTE, CONSTRAINT code_title PRIMARY KEY(code,title) ); - </programlisting> +</programlisting> </para> <para> - Defines a PRIMARY KEY column constraint for table distributors. - PRIMARY KEY column constraints can only be defined on one column - of the table (the following two examples are equivalent): + Define a primary key constraint for table + <structname>distributors</>. The following two examples are + equivalent, the first using the table constraint syntax, the second + the column constraint notation. - <programlisting> +<programlisting> CREATE TABLE distributors ( - did DECIMAL(3), - name CHAR VARYING(40), + did DECIMAL(3), + name CHAR VARYING(40), PRIMARY KEY(did) ); - </programlisting> +</programlisting> - <programlisting> +<programlisting> CREATE TABLE distributors ( - did DECIMAL(3) PRIMARY KEY, - name VARCHAR(40) + did DECIMAL(3) PRIMARY KEY, + name VARCHAR(40) ); - </programlisting> +</programlisting> </para> - + + <para> + This assigns a literal constant default value for the column + <literal>name</literal>, and arranges for the default value of + column <literal>did</literal> to be generated by selecting the next + value of a sequence object. The default value of + <literal>modtime</literal> will be the time at which the row is + inserted. + +<programlisting> +CREATE TABLE distributors ( + name VARCHAR(40) DEFAULT 'luso films', + did INTEGER DEFAULT NEXTVAL('distributors_serial'), + modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP +); +</programlisting> + </para> + + <para> + Define two <literal>NOT NULL</> column constraints on the table + <classname>distributors</classname>, one of which is explicitly + given a name: + +<programlisting> +CREATE TABLE distributors ( + did DECIMAL(3) CONSTRAINT no_null NOT NULL, + name VARCHAR(40) NOT NULL +); +</programlisting> + </para> + + <para> + Define a unique constraint for the <literal>name</literal> column: + +<programlisting> +CREATE TABLE distributors ( + did DECIMAL(3), + name VARCHAR(40) UNIQUE +); +</programlisting> + + The above is equivalent to the following specified as a table constraint: + +<programlisting> +CREATE TABLE distributors ( + did DECIMAL(3), + name VARCHAR(40), + UNIQUE(name) +); +</programlisting> + </para> + </refsect1> - <refsect1 id="R1-SQL-CREATETABLE-3"> - <title> - Compatibility - </title> - - <refsect2 id="R2-SQL-CREATETABLE-4"> - <title> - SQL92 - </title> + <refsect1 id="SQL-CREATETABLE-compatibility"> + <title id="SQL-CREATETABLE-compatibility-title">Compatibility</title> + + <para> + The <command>CREATE TABLE</command> conforms to SQL92 Intermediate + and to a subset of SQL99, with exceptions listed below and in the + descriptions above. + </para> + + <refsect2> + <title>Temporary Tables</title> + <para> - In addition to the locally visible temporary table, SQL92 also defines a - CREATE GLOBAL TEMPORARY TABLE statement, and optionally an - ON COMMIT clause: - <synopsis> -CREATE GLOBAL TEMPORARY TABLE <replaceable class="parameter">table</replaceable> ( <replaceable class="parameter">column</replaceable> <replaceable class="parameter">type</replaceable> [ - DEFAULT <replaceable class="parameter">value</replaceable> ] [ CONSTRAINT <replaceable class="parameter">column_constraint</replaceable> ] [, ... ] ) - [ CONSTRAINT <replaceable class="parameter">table_constraint</replaceable> ] [ ON COMMIT { DELETE | PRESERVE } ROWS ] - </synopsis> + In addition to the local temporary table, SQL92 also defines a + <literal>CREATE GLOBAL TEMPORARY TABLE</literal> statement. + Global temporary tables are also visible to other sessions. </para> <para> - For temporary tables, the CREATE GLOBAL TEMPORARY TABLE statement - names a new table visible to other clients and defines the table's columns - and constraints. + For temporary tables, there is an optional <literal>ON COMMIT</literal> clause: +<synopsis> +CREATE { GLOBAL | LOCAL } TEMPORARY TABLE <replaceable class="parameter">table</replaceable> ( <replaceable class="parameter">...</replaceable> ) [ ON COMMIT { DELETE | PRESERVE } ROWS ] +</synopsis> + + The <literal>ON COMMIT</literal> clause specifies whether or not + the temporary table should be emptied of rows whenever + <command>COMMIT</command> is executed. If the <literal>ON + COMMIT</> clause is omitted, SQL92 specifies that the default is + <literal>ON COMMIT DELETE ROWS</>. However, the behavior of + <productname>PostgreSQL</productname> is always like <literal>ON + COMMIT PRESERVE ROWS</literal>. </para> + </refsect2> + + <refsect2> + <title><literal>NULL</literal> <quote>Constraint</quote></title> + <para> - The optional ON COMMIT clause of CREATE TEMPORARY TABLE specifies - whether or not the temporary table should be emptied of rows - whenever COMMIT is executed. If the ON COMMIT clause is omitted, SQL92 - specifies that the default is ON COMMIT DELETE ROWS. However, - <productname>Postgres</productname>' behavior is always like - ON COMMIT PRESERVE ROWS. + The <literal>NULL</> <quote>constraint</quote> (actually a + non-constraint) is a <productname>PostgreSQL</productname> + extension to SQL92 that is included for compatibility with some + other RDBMSes (and for symmetry with the <literal>NOT + NULL</literal> constraint). Since it is the default for any + column, its presence is simply noise. </para> + </refsect2> + + <refsect2> + <title>Assertions</title> - <refsect3 id="R3-SQL-UNIQUECLAUSE-1"> - <title> - UNIQUE clause - </title> - <para> - SQL92 specifies some additional capabilities for UNIQUE: - </para> - <para> - Table Constraint definition: - - <synopsis> -[ CONSTRAINT <replaceable>constraint_name</replaceable> ] UNIQUE ( <replaceable>column</replaceable> [, ... ] ) - [ { INITIALLY DEFERRED | INITIALLY IMMEDIATE } ] - [ [ NOT ] DEFERRABLE ] - </synopsis> - </para> + <para> + An assertion is a special type of integrity constraint and shares + the same namespace as other constraints. However, an assertion is + not necessarily dependent on one particular table as constraints + are, so SQL92 provides the <command>CREATE ASSERTION</command> + statement as an alternate method for defining a constraint: - <para> - Column Constraint definition: +<synopsis> +CREATE ASSERTION <replaceable>name</replaceable> CHECK ( <replaceable>condition</replaceable> ) +</synopsis> + </para> - <synopsis> -[ CONSTRAINT <replaceable>constraint_name</replaceable> ] UNIQUE - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] - </synopsis> - </para> - </refsect3> - - <refsect3 id="R3-SQL-NULL-1"> - <title> - NULL clause - </title> - <para> - The NULL <quote>constraint</quote> (actually a non-constraint) is a - <productname>Postgres</productname> extension to SQL92 that is - included for symmetry with the NOT NULL clause (and for compatibility - with some other RDBMSes). Since it is the - default for any column, its presence is simply noise. - <synopsis> -[ CONSTRAINT <replaceable>constraint_name</replaceable> ] NULL - </synopsis> - </para> - </refsect3> - - <refsect3 id="R3-SQL-NOTNULL-4"> - <title> - NOT NULL clause - </title> - <para> - - SQL92 specifies some additional capabilities for NOT NULL: - <synopsis> -[ CONSTRAINT <replaceable>constraint_name</replaceable> ] NOT NULL - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] - </synopsis> - </para> - </refsect3> - -<!-- -I can't figure out why DEFAULT clause is different from what we already have. -Perhaps because CURRENT_USER and CURRENT_DATE have specific types (currently -the <type>name</type> type), if you aren't careful then the types won't match up with -the column. Not our problem... -- Thomas 1998-08-16 - - <REFSECT3 ID="R3-SQL-DEFAULTCLAUSE-1"> - <TITLE> - DEFAULT clause - </TITLE> - <PARA> - SQL92 specifies some additional capabilities for the DEFAULT clause. - A DEFAULT clause is used to set the default value for a column - or a domain. - </para> - <synopsis> -DEFAULT niladic_user_function | niladic_datetime_function | NULL - </synopsis> - </refsect3> ---> + <para> + <productname>PostgreSQL</> does not implement assertions at present. + </para> + </refsect2> - <refsect3 id="R3-SQL-CONSTRAINT-3"> - <title> - CONSTRAINT clause - </title> - <para> - SQL92 specifies some additional capabilities for constraints, - and also defines assertions and domain constraints. - <note> - <para> - <productname>Postgres</productname> does not yet support - either domains or assertions. - </para> - </note> - </para> - <para> - An assertion is a special type of integrity constraint and shares - the same namespace as other constraints. However, an assertion is - not necessarily dependent on one particular table as - constraints are, so SQL-92 provides the CREATE ASSERTION statement - as an alternate method for defining a constraint: - </para> - <synopsis> -CREATE ASSERTION <replaceable>name</replaceable> CHECK ( <replaceable>condition</replaceable> ) - </synopsis> - +<!-- <para> Domain constraints are defined by CREATE DOMAIN or ALTER DOMAIN statements: @@ -1984,191 +830,38 @@ CREATE ASSERTION <replaceable>name</replaceable> CHECK ( <replaceable>condition< [ [ NOT ] DEFERRABLE ] </synopsis> </para> - - <para> - Table constraint definition: - - <synopsis> -[ CONSTRAINT <replaceable>constraint_name</replaceable> ] { PRIMARY KEY ( <replaceable class="parameter">column</replaceable>, ... ) | FOREIGN KEY <replaceable>constraint</replaceable> | UNIQUE <replaceable>constraint</replaceable> | CHECK <replaceable>constraint</replaceable> } - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] - </synopsis> - </para> - - <para> - Column constraint definition: - - <synopsis> -[ CONSTRAINT <replaceable>constraint_name</replaceable> ] { NOT NULL | PRIMARY KEY | FOREIGN KEY <replaceable>constraint</replaceable> | UNIQUE | CHECK <replaceable>constraint</replaceable> } - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] - </synopsis> - </para> - - <para> - A CONSTRAINT definition may contain one deferment attribute - clause and/or one initial constraint mode clause, in any order. - <variablelist> - <varlistentry> - <term>NOT DEFERRABLE</term> - <listitem> - <para> - The constraint must be checked at the end of each statement. - SET CONSTRAINTS ALL DEFERRED will have no effect on this type - of constraint. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>DEFERRABLE</term> - <listitem> - <para> - This controls whether the constraint can be deferred to the end - of the transaction. If SET CONSTRAINTS ALL DEFERRED is used or - the constraint is set to INITIALLY DEFERRED, this will cause - the foreign key to be checked only at the end of the - transaction. - </para> - <note> - <para> - <command>SET CONSTRAINTS</> changes the foreign key constraint mode - only for the current transaction. - </para> - </note> - </listitem> - </varlistentry> - <varlistentry> - <term>INITIALLY IMMEDIATE</term> - <listitem> - <para> - Check constraint after each statement. This is the default. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>INITIALLY DEFERRED</term> - <listitem> - <para> - Check constraint only at the end of the transaction. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect3> - - - <refsect3 id="R3-SQL-CHECK-4"> - <title> - CHECK clause - </title> - <para> - SQL92 specifies some additional capabilities for CHECK in either -table or column constraints. - </para> -<!-- -Constraints associated with domains do not need to be mentioned here, -even though it is the case that a domain constraint may possibly -affect a column or a table. -- Thomas 1998-08-16 - <para> - A CHECK constraint is either a table constraint, a column - constraint or a domain constraint. - </para> --> - <para> - table constraint definition: -<synopsis> -[ CONSTRAINT <replaceable>constraint_name</replaceable> ] - CHECK ( VALUE <replaceable>condition</replaceable> ) - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] -</synopsis> - </para> + + <refsect2> + <title>Inheritance</title> - <para> - column constraint definition: -<synopsis> -[ CONSTRAINT <replaceable>constraint_name</replaceable> ] - CHECK ( VALUE <replaceable>condition</replaceable> ) - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] -</synopsis> - </para> -<!-- <para> - domain constraint definition: - </para> - <synopsis> - [ CONSTRAINT name] - CHECK ( VALUE condition ) - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] - </synopsis> - <para> - CHECK domain constraints can be defined in either - a CREATE DOMAIN statement or an ALTER DOMAIN statement: - </para> - <programlisting> -CREATE DOMAIN duration AS SMALLINT - CONSTRAINT minutes CHECK (VALUE IN (90,120,180,240)); + Multiple inheritance via the <literal>INHERITS</literal> clause is + a <productname>PostgreSQL</productname> language extension. SQL99 + (but not SQL92) defines single inheritance using a different + syntax and different semantics. SQL99-style inheritance is not + yet supported by <productname>PostgreSQL</productname>. + </para> + </refsect2> -ALTER DOMAIN cities - ADD CONSTRAINT new_city CHECK (VALUE LIKE 'L%'); - </programlisting> ---> - </refsect3> + <refsect2> + <title>Object IDs</title> - <refsect3 id="R3-SQL-PRIMARYKEY-1"> - <title> - PRIMARY KEY clause - </title> - <para> - SQL92 specifies some additional capabilities for PRIMARY KEY: - </para> - <para> - Table Constraint definition: - <synopsis> -[ CONSTRAINT <replaceable>constraint_name</replaceable> ] PRIMARY KEY ( <replaceable>column</replaceable> [, ... ] ) - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] - </synopsis> - </para> - <para> - Column Constraint definition: - <synopsis> -[ CONSTRAINT <replaceable>constraint_name</replaceable> ] PRIMARY KEY - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] - </synopsis> - </para> - </refsect3> + <para> + The <productname>PostgreSQL</productname> concept of OIDs is not + standard. + </para> + </refsect2> + </refsect1> - <refsect3 id="R3-SQL-INHERITANCE-1"> - <title> - Inheritance - </title> - <para> - Multiple inheritance via the INHERITS clause is a - <productname>Postgres</productname> language extension. - SQL99 (but not SQL92) defines single inheritance using a different - syntax and different semantics. SQL99-style inheritance is not yet - supported by <productname>Postgres</productname>. - </para> - </refsect3> - <refsect3 id="R3-SQL-OBJECTIDS-1"> - <title> - Object IDs - </title> - <para> - The <productname>Postgres</productname> concept of OIDs is not - standard. SQL99 (but not SQL92) has a notion of object ID, but - the syntax and semantics are different --- SQL99 associates OIDs - with individual values, not with rows. - </para> - </refsect3> - </refsect2> + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-altertable"></member> + <member><xref linkend="sql-droptable"></member> + </simplelist> </refsect1> </refentry> diff --git a/doc/src/sgml/ref/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml index a0144ab6586..8ebc19c2a79 100644 --- a/doc/src/sgml/ref/create_table_as.sgml +++ b/doc/src/sgml/ref/create_table_as.sgml @@ -1,112 +1,26 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.7 2001/09/03 12:57:49 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.8 2001/10/22 18:14:47 petere Exp $ Postgres documentation --> <refentry id="SQL-CREATETABLEAS"> <refmeta> - <refentrytitle id="SQL-CREATETABLEAS-TITLE"> - CREATE TABLE AS - </refentrytitle> + <refentrytitle>CREATE TABLE AS</refentrytitle> <refmiscinfo>SQL - Language Statements</refmiscinfo> </refmeta> + <refnamediv> - <refname> - CREATE TABLE AS - </refname> - <refpurpose> - create a new table from the results of a query - </refpurpose> + <refname>CREATE TABLE AS</refname> + <refpurpose>create a new table from the results of a query</refpurpose> </refnamediv> + <refsynopsisdiv> - <refsynopsisdivinfo> - <date>2001-03-03</date> - </refsynopsisdivinfo> - <synopsis> -CREATE [ TEMPORARY | TEMP ] TABLE <replaceable>table</replaceable> [ (<replaceable>column</replaceable> [, ...] ) ] - AS <replaceable>select_clause</replaceable> +<synopsis> +CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ] + AS <replaceable>query</replaceable> </synopsis> - - <refsect2> - <refsect2info> - <date>1998-09-22</date> - </refsect2info> - <title> - Inputs - </title> - <para> - - <variablelist> - <varlistentry> - <term>TEMPORARY or TEMP</term> - <listitem> - <para> - If specified, the table is created only within this session, and is - automatically dropped on session exit. - Existing permanent tables with the same name are not visible - (in this session) while the temporary table exists. - Any indexes created on a temporary table are automatically - temporary as well. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable>table</replaceable></term> - <listitem> - <para> - The name of the new table to be created. - This table must not already exist. However, a temporary table - can be created that has the same name as an existing permanent - table. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable>column</replaceable></term> - <listitem> - <para> - The name of a column. Multiple column names can be specified using - a comma-delimited list of column names. If column names are not - provided, they are taken from the output column names of the - SELECT query. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable>select_clause</replaceable></term> - <listitem> - <para> - A valid query statement. Refer to - <xref linkend="sql-select" endterm="sql-select-title"> - for a description of the allowed syntax. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect2> - - <refsect2> - <refsect2info> - <date>1998-09-22</date> - </refsect2info> - <title> - Outputs - </title> - - <para> - Refer to - <xref linkend="sql-createtable" endterm="sql-createtable-title"> - and - <xref linkend="sql-select" endterm="sql-select-title"> - for a summary of possible output messages. - </para> - </refsect2> </refsynopsisdiv> - + <refsect1> <refsect1info> <date>2001-03-20</date> @@ -117,28 +31,135 @@ CREATE [ TEMPORARY | TEMP ] TABLE <replaceable>table</replaceable> [ (<replaceab <para> <command>CREATE TABLE AS</command> creates a table and fills it with data computed by a <command>SELECT</command> command. The - table columns have the names and datatypes associated with the + table columns have the names and data types associated with the output columns of the <command>SELECT</command> (except that you - can override the <command>SELECT</command> column names by giving - an explicit list of column names). + can override the column names by giving an explicit list of new + column names). + </para> + + <para> + <command>CREATE TABLE AS</command> bears some resemblance to + creating a view, but it is really quite different: it creates a new + table and evaluates the query just once to fill the new table + initially. The new table will not track subsequent changes to the + source tables of the query. In contrast, a view re-evaluates the + underlying <command>SELECT</command> statements whenever it is + queried. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><literal>[LOCAL] TEMPORARY</> or <literal>[LOCAL] TEMP</></term> + <listitem> + <para> + If specified, the table is created as a temporary table. + Temporary tables are automatically dropped at the end of a + session. Existing persistent tables with the same name are not + visible to the current session while the temporary table exists. + Any indexes created on a temporary table are automatically + temporary as well. + </para> + + <para> + The <literal>LOCAL</literal> word is optional. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable>table_name</replaceable></term> + <listitem> + <para> + The name of the new table to be created. This table must not + already exist. However, a temporary table can be created that + has the same name as an existing permanent table. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable>column_name</replaceable></term> + <listitem> + <para> + The name of a column in the new table. Multiple column names can + be specified using a comma-delimited list of column names. If + column names are not provided, they are taken from the output + column names of the query. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable>query</replaceable></term> + <listitem> + <para> + A query statement (that is, a <command>SELECT</command> + command). Refer to + <xref linkend="sql-select"> + for a description of the allowed syntax. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Diagnostics</title> + + <para> + Refer to <xref linkend="sql-createtable"> and + <xref linkend="sql-select"> + for a summary of possible output messages. + </para> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + This command is functionally equivalent to <xref + linkend="sql-selectinto">, but it is preferred since it is less + likely to be confused with other uses of the <command>SELECT + ... INTO</command> syntax. </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> <para> - <command>CREATE TABLE AS</command> bears some resemblance to creating - a view, but it is really quite different: it creates a new table and - evaluates the <command>SELECT</command> just once to fill the new table - initially. The new table will not track subsequent changes to - the source tables of the <command>SELECT</command>. In contrast, - a view re-evaluates the given <command>SELECT</command> whenever queried. + This command is modeled after an <productname>Oracle</productname> + feature. There is no command with equivalent functionality in + SQL92 or SQL99. However, a combination of <literal>CREATE + TABLE</literal> and <literal>INSERT ... SELECT</literal> can + accomplish the same thing with little more effort. </para> + </refsect1> + + <refsect1> + <title>History</title> <para> - This command is functionally equivalent to - <xref linkend="sql-selectinto" endterm="sql-selectinto-title">, - but it is preferred since it is less likely to be confused with - other uses of the <command>SELECT ... INTO</command> syntax. + The <command>CREATE TABLE AS</command> command has been available + since <productname>PostgreSQL</productname> 6.3. </para> </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-createtable"></member> + <member><xref linkend="sql-createview"></member> + <member><xref linkend="sql-select"></member> + <member><xref linkend="sql-selectinto"></member> + </simplelist> + </refsect1> + </refentry> <!-- Keep this comment at the end of the file diff --git a/doc/src/sgml/ref/select_into.sgml b/doc/src/sgml/ref/select_into.sgml index ab96185bb91..b9f1a3fe069 100644 --- a/doc/src/sgml/ref/select_into.sgml +++ b/doc/src/sgml/ref/select_into.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/select_into.sgml,v 1.12 2001/09/23 13:34:44 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/select_into.sgml,v 1.13 2001/10/22 18:14:47 petere Exp $ Postgres documentation --> @@ -89,7 +89,7 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be: <para> All other inputs are described in detail for - <xref linkend="sql-select" endterm="sql-select-title">. + <xref linkend="sql-select">. </para> </refsect2> @@ -103,9 +103,9 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be: <para> Refer to - <xref linkend="sql-createtable" endterm="sql-createtable-title"> + <xref linkend="sql-createtable"> and - <xref linkend="sql-select" endterm="sql-select-title"> + <xref linkend="sql-select"> for a summary of possible output messages. </para> </refsect2> @@ -128,7 +128,7 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be: <note> <para> - <xref linkend="sql-createtableas" endterm="sql-createtableas-title"> + <xref linkend="sql-createtableas"> is functionally equivalent to <command>SELECT INTO</command>. <command>CREATE TABLE AS</command> is the recommended syntax, since <command>SELECT INTO</command> is not standard. In fact, this form of diff --git a/doc/src/sgml/stylesheet.dsl b/doc/src/sgml/stylesheet.dsl index 1b2d55ce86a..80d9462d23e 100644 --- a/doc/src/sgml/stylesheet.dsl +++ b/doc/src/sgml/stylesheet.dsl @@ -1,4 +1,4 @@ -<!-- $Header: /cvsroot/pgsql/doc/src/sgml/stylesheet.dsl,v 1.14 2001/10/09 18:46:00 petere Exp $ --> +<!-- $Header: /cvsroot/pgsql/doc/src/sgml/stylesheet.dsl,v 1.15 2001/10/22 18:14:47 petere Exp $ --> <!DOCTYPE style-sheet PUBLIC "-//James Clark//DTD DSSSL Style Sheet//EN" [ <!-- must turn on one of these with -i on the jade command line --> @@ -123,6 +123,16 @@ ;;; XXX The above is very ugly. It might be better to run 'tidy' on ;;; the resulting *.html files. + +;; Format multiple terms in varlistentry vertically, instead +;; of comma-separated. +(element (varlistentry term) + (make sequence + (process-children-trim) + (if (not (last-sibling?)) + (make empty-element gi: "BR") + (empty-sosofo)))) + ]]> <!-- %output-html --> <![ %output-print; [ |