Identity columns

This is the SQL standard-conforming variant of PostgreSQL's serial
columns.  It fixes a few usability issues that serial columns have:

- CREATE TABLE / LIKE copies default but refers to same sequence
- cannot add/drop serialness with ALTER TABLE
- dropping default does not drop sequence
- need to grant separate privileges to sequence
- other slight weirdnesses because serial is some kind of special macro

Reviewed-by: Vitaly Burovoy <vitaly.burovoy@gmail.com>

6 files changed, 172 insertions, 10 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 5c1930c7450..5883673448c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1130,6 +1130,17 @@
      </row>
 
      <row>
+      <entry><structfield>attidentity</structfield></entry>
+      <entry><type>char</type></entry>
+      <entry></entry>
+      <entry>
+       If a zero byte (<literal>''</literal>), then not an identity column.
+       Otherwise, <literal>a</literal> = generated
+       always, <literal>d</literal> = generated by default.
+      </entry>
+     </row>
+
+     <row>
       <entry><structfield>attisdropped</structfield></entry>
       <entry><type>bool</type></entry>
       <entry></entry>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index a3a19ce8ce2..02f79274363 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -1583,13 +1583,20 @@
      <row>
       <entry><literal>is_identity</literal></entry>
       <entry><type>yes_or_no</type></entry>
-      <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+      <entry>
+       If the column is an identity column, then <literal>YES</literal>,
+       else <literal>NO</literal>.
+      </entry>
      </row>
 
      <row>
       <entry><literal>identity_generation</literal></entry>
       <entry><type>character_data</type></entry>
-      <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+      <entry>
+       If the column is an identity column, then <literal>ALWAYS</literal>
+       or <literal>BY DEFAULT</literal>, reflecting the definition of the
+       column.
+      </entry>
      </row>
 
      <row>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 7829f378bba..56ea830d413 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -46,6 +46,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
     ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
     ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> DROP DEFAULT
     ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> { SET | DROP } NOT NULL
+    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]
+    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...]
+    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> DROP IDENTITY [ IF EXISTS ]
     ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
     ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
     ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
@@ -188,6 +191,38 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
    </varlistentry>
 
    <varlistentry>
+    <term><literal>ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY</literal></term>
+    <term><literal>SET GENERATED { ALWAYS | BY DEFAULT }</literal></term>
+    <term><literal>DROP IDENTITY [ IF EXISTS ]</literal></term>
+    <listitem>
+     <para>
+      These forms change whether a column is an identity column or change the
+      generation attribute of an existing identity column.
+      See <xref linkend="sql-createtable"> for details.
+     </para>
+
+     <para>
+      If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the
+      column is not an identity column, no error is thrown.  In this case a
+      notice is issued instead.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>SET <replaceable>sequence_option</replaceable></literal></term>
+    <term><literal>RESTART</literal></term>
+    <listitem>
+     <para>
+      These forms alter the sequence that underlies an existing identity
+      column.  <replaceable>sequence_option</replaceable> is an option
+      supported by <xref linkend="sql-altersequence"> such
+      as <literal>INCREMENT BY</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><literal>SET STATISTICS</literal></term>
     <listitem>
      <para>
@@ -1160,8 +1195,11 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
    </para>
 
    <para>
-    The <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,
-    and <literal>TABLESPACE</> actions never recurse to descendant tables;
+    The actions for identity columns (<literal>ADD
+    GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP
+    IDENTITY</literal>), as well as the actions
+    <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,
+    and <literal>TABLESPACE</> never recurse to descendant tables;
     that is, they always act as though <literal>ONLY</> were specified.
     Adding a constraint recurses only for <literal>CHECK</> constraints
     that are not marked <literal>NO INHERIT</>.
@@ -1371,8 +1409,9 @@ ALTER TABLE cities
 
   <para>
    The forms <literal>ADD</literal> (without <literal>USING INDEX</literal>),
-   <literal>DROP</>, <literal>SET DEFAULT</>,
-   and <literal>SET DATA TYPE</literal> (without <literal>USING</literal>)
+   <literal>DROP [COLUMN]</>, <literal>DROP IDENTITY</literal>, <literal>RESTART</literal>,
+   <literal>SET DEFAULT</>, <literal>SET DATA TYPE</literal> (without <literal>USING</literal>),
+   <literal>SET GENERATED</literal>, and <literal>SET <replaceable>sequence_option</replaceable></literal>
    conform with the SQL standard.  The other forms are
    <productname>PostgreSQL</productname> extensions of the SQL standard.
    Also, the ability to specify more than one manipulation in a single
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 7ff62f2a821..215efcd69d7 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -480,6 +480,13 @@ COPY <replaceable class="parameter">count</replaceable>
    </para>
 
    <para>
+    For identity columns, the <command>COPY FROM</command> command will always
+    write the column values provided in the input data, like
+    the <command>INPUT</command> option <literal>OVERRIDING SYSTEM
+    VALUE</literal>.
+   </para>
+
+   <para>
     <command>COPY</command> input and output is affected by
     <varname>DateStyle</varname>. To ensure portability to other
     <productname>PostgreSQL</productname> installations that might use
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 121418b6ca2..1881d9257af 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -62,6 +62,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
   NULL |
   CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |
   DEFAULT <replaceable>default_expr</replaceable> |
+  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
   UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> |
   PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |
   REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
@@ -81,7 +82,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
 
 <phrase>and <replaceable class="PARAMETER">like_option</replaceable> is:</phrase>
 
-{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }
+{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES | STORAGE | COMMENTS | ALL }
 
 <phrase>and <replaceable class="PARAMETER">partition_bound_spec</replaceable> is:</phrase>
 
@@ -412,6 +413,11 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
       Column <literal>STORAGE</> settings are also copied from parent tables.
      </para>
 
+     <para>
+      If a column in the parent table is an identity column, that property is
+      not inherited.  A column in the child table can be declared identity
+      column if desired.
+     </para>
     </listitem>
    </varlistentry>
 
@@ -481,6 +487,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
       the original and new tables.
      </para>
      <para>
+      Any identity specifications of copied column definitions will only be
+      copied if <literal>INCLUDING IDENTITY</literal> is specified.  A new
+      sequence is created for each identity column of the new table, separate
+      from the sequences associated with the old table.
+     </para>
+     <para>
       Not-null constraints are always copied to the new table.
       <literal>CHECK</literal> constraints will be copied only if
       <literal>INCLUDING CONSTRAINTS</literal> is specified.
@@ -512,7 +524,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
      </para>
      <para>
       <literal>INCLUDING ALL</literal> is an abbreviated form of
-      <literal>INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS</literal>.
+      <literal>INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS</literal>.
      </para>
      <para>
       Note that unlike <literal>INHERITS</literal>, columns and
@@ -627,6 +639,37 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
    </varlistentry>
 
    <varlistentry>
+    <term><literal>GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]</literal></term>
+    <listitem>
+     <para>
+      This clause creates the column as an <firstterm>identity
+      column</firstterm>.  It will have an implicit sequence attached to it
+      and the column in new rows will automatically have values from the
+      sequence assigned to it.
+     </para>
+
+     <para>
+      The clauses <literal>ALWAYS</literal> and <literal>BY DEFAULT</literal>
+      determine how the sequence value is given precedence over a
+      user-specified value in an <command>INSERT</command> statement.
+      If <literal>ALWAYS</literal> is specified, 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 specified, then the user-specified value takes
+      precedence.  See <xref linkend="sql-insert"> for details.  (In
+      the <command>COPY</command> command, user-specified values are always
+      used regardless of this setting.)
+     </para>
+
+     <para>
+      The optional <replaceable>sequence_options</replaceable> clause can be
+      used to override the options of the sequence.
+      See <xref linkend="sql-createsequence"> for details.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><literal>UNIQUE</> (column constraint)</term>
     <term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
 
@@ -1263,7 +1306,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
 
     <para>
      Using OIDs in new applications is not recommended: where
-     possible, using a <literal>SERIAL</literal> or other sequence
+     possible, using an identity column or other sequence
      generator as the table's primary key is preferred. However, if
      your application does make use of OIDs to identify specific
      rows of a table, it is recommended to create a unique constraint
@@ -1323,7 +1366,7 @@ CREATE TABLE films (
 );
 
 CREATE TABLE distributors (
-     did    integer PRIMARY KEY DEFAULT nextval('serial'),
+     did    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
      name   varchar(40) NOT NULL CHECK (name &lt;&gt; '')
 );
 </programlisting>
@@ -1738,6 +1781,20 @@ CREATE TABLE cities_ab_10000_to_100000
   </refsect2>
 
   <refsect2>
+   <title>Multiple Identity Columns</title>
+
+   <para>
+    <productname>PostgreSQL</productname> allows a table to have more than one
+    identity column.  The standard specifies that a table can have at most one
+    identity column.  This is relaxed mainly to give more flexibility for
+    doing schema changes or migrations.  Note that
+    the <command>INSERT</command> command supports only one override clause
+    that applies to the entire statement, so having multiple identity columns
+    with different behaviors is not well supported.
+   </para>
+  </refsect2>
+
+  <refsect2>
    <title><literal>LIKE</> Clause</title>
 
    <para>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 521216b5d52..95aa77b907f 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -23,6 +23,7 @@ PostgreSQL documentation
 <synopsis>
 [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
 INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ] [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
+    [ OVERRIDING { SYSTEM | USER} VALUE ]
     { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
     [ ON CONFLICT [ <replaceable class="parameter">conflict_target</replaceable> ] <replaceable class="parameter">conflict_action</replaceable> ]
     [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
@@ -202,10 +203,43 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
      </varlistentry>
 
      <varlistentry>
+      <term><literal>OVERRIDING SYSTEM VALUE</literal></term>
+      <listitem>
+       <para>
+        Without this clause, it is an error to specify an explicit value
+        (other than <literal>DEFAULT</literal>) for an identity column defined
+        as <literal>GENERATED ALWAYS</literal>.  This clause overrides that
+        restriction.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><literal>OVERRIDING USER VALUE</literal></term>
+      <listitem>
+       <para>
+        If this clause is specified, then any values supplied for identity
+        columns defined as <literal>GENERATED BY DEFAULT</literal> are ignored
+        and the default sequence-generated values are applied.
+       </para>
+
+       <para>
+        This clause is useful for example when copying values between tables.
+        Writing <literal>INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM
+        tbl1</literal> will copy from <literal>tbl1</literal> all columns that
+        are not identity columns in <literal>tbl2</literal> but will continue
+        the sequence counters for any identity columns.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><literal>DEFAULT VALUES</literal></term>
       <listitem>
        <para>
         All columns will be filled with their default values.
+        (An <literal>OVERRIDING</literal> clause is not permitted in this
+        form.)
        </para>
       </listitem>
      </varlistentry>
@@ -711,6 +745,13 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
   </para>
 
   <para>
+   The SQL standard specifies that <literal>OVERRIDING SYSTEM VALUE</literal>
+   can only be specified if an identity column that is generated always
+   exists.  PostgreSQL allows the clause in any case and ignores it if it is
+   not applicable.
+  </para>
+
+  <para>
    Possible limitations of the <replaceable
    class="PARAMETER">query</replaceable> clause are documented under
    <xref linkend="sql-select">.