diff options
| -rw-r--r-- | doc/src/sgml/datatype.sgml | 2 | ||||
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 110 |
2 files changed, 111 insertions, 1 deletions
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index b3a92b9aabc..66510ee0315 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -955,7 +955,7 @@ FROM generate_series(-3.5, 3.5, 1) as x; <para> This section describes a PostgreSQL-specific way to create an autoincrementing column. Another way is to use the SQL-standard - identity column feature, described at <xref linkend="sql-createtable"/>. + identity column feature, described at <xref linkend="ddl-identity-columns"/>. </para> </note> diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 77790f2e4b2..4044f0908f0 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -233,6 +233,116 @@ CREATE TABLE products ( </para> </sect1> + <sect1 id="ddl-identity-columns"> + <title>Identity Columns</title> + + <indexterm zone="ddl-identity-columns"> + <primary>identity column</primary> + </indexterm> + + <para> + An identity column is a special column that is generated automatically from + an implicit sequence. It can be used to generate key values. + </para> + + <para> + To create an identity column, use the <literal>GENERATED ... + AS IDENTITY</literal> clause in <command>CREATE TABLE</command>, for example: +<programlisting> +CREATE TABLE people ( + id bigint <emphasis>GENERATED ALWAYS AS IDENTITY</emphasis>, + ..., +); +</programlisting> + or alternatively +<programlisting> +CREATE TABLE people ( + id bigint <emphasis>GENERATED BY DEFAULT IDENTITY</emphasis>, + ..., +); +</programlisting> + See <xref linkend="sql-createtable"/> for more details. + </para> + + <para> + If an <command>INSERT</command> command is executed on the table with the + identity column and no value is explicitly specified for the identity + column, then a value generated by the implicit sequence is inserted. For + example, with the above definitions and assuming additional appropriate + columns, writing +<programlisting> +INSERT INTO people (name, address) VALUE ('A', 'foo'); +INSERT INTO people (name, address) VALUE ('B', 'bar'); +</programlisting> + would generate values for the <literal>id</literal> column starting at 1 + and result in the following table data: +<screen> + id | name | address +----+------+--------- + 1 | A | foo + 2 | B | bar +</screen> + Alternatively, the keyword <literal>DEFAULT</literal> can be specified in + place of a value to explicitly request the sequence-generated value, like +<programlisting> +INSERT INTO people (id, name, address) VALUE (<emphasis>DEFAULT</emphasis>, 'C', 'baz'); +</programlisting> + Similarly, the keyword <literal>DEFAULT</literal> can be used in + <command>UPDATE</command> commands. + </para> + + <para> + Thus, in many ways, an identity column behaves like a column with a default + value. + </para> + + <para> + The clauses <literal>ALWAYS</literal> and <literal>BY DEFAULT</literal> in + the column definition determine how explicitly user-specified values are + handled in <command>INSERT</command> and <command>UPDATE</command> + commands. In an <command>INSERT</command> command, if + <literal>ALWAYS</literal> is selected, a user-specified value is only + accepted if the <command>INSERT</command> statement specifies + <literal>OVERRIDING SYSTEM VALUE</literal>. If <literal>BY + DEFAULT</literal> is selected, then the user-specified value takes + precedence. Thus, using <literal>BY DEFAULT</literal> results in a + behavior more similar to default values, where the default value can be + overridden by an explicit value, whereas <literal>ALWAYS</literal> provides + some more protection against accidentally inserting an explicit value. + </para> + + <para> + The data type of an identity column must be one of the data types supported + by sequences. (See <xref linkend="sql-createsequence"/>.) The properties + of the associated sequence may be specified when creating an identity + column (see <xref linkend="sql-createtable"/>) or changed afterwards (see + <xref linkend="sql-altertable"/>). + </para> + + <para> + An identity column is automatically marked as <literal>NOT NULL</literal>. + An identity column, however, does not guarantee uniqueness. (A sequence + normally returns unique values, but a sequence could be reset, or values + could be inserted manually into the identity column, as discussed above.) + Uniqueness would need to be enforced using a <literal>PRIMARY KEY</literal> + or <literal>UNIQUE</literal> constraint. + </para> + + <para> + In table inheritance hierarchies, identity columns and their properties in + a child table are independent of those in its parent tables. A child table + does not inherit identity columns or their properties automatically from + the parent. During <command>INSERT</command> or <command>UPDATE</command>, + a column is treated as an identity column if that column is an identity + column in the table named in the statement, and the corresponding identity + properties are applied. + </para> + + <para> + Identity columns are currently not supported for partitioned tables. + </para> + </sect1> + <sect1 id="ddl-generated-columns"> <title>Generated Columns</title> |