From 3217327053638085d24dd4d276e7c1f7ac2c4c6b Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 6 Apr 2017 08:33:16 -0400 Subject: 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 --- doc/src/sgml/catalogs.sgml | 11 ++++++ doc/src/sgml/information_schema.sgml | 11 ++++-- doc/src/sgml/ref/alter_table.sgml | 47 +++++++++++++++++++++++--- doc/src/sgml/ref/copy.sgml | 7 ++++ doc/src/sgml/ref/create_table.sgml | 65 +++++++++++++++++++++++++++++++++--- doc/src/sgml/ref/insert.sgml | 41 +++++++++++++++++++++++ 6 files changed, 172 insertions(+), 10 deletions(-) (limited to 'doc/src') 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 @@ -1129,6 +1129,17 @@ + + attidentity + char + + + If a zero byte (''), then not an identity column. + Otherwise, a = generated + always, d = generated by default. + + + attisdropped bool 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 @@ is_identity yes_or_no - Applies to a feature not available in PostgreSQL + + If the column is an identity column, then YES, + else NO. + identity_generation character_data - Applies to a feature not available in PostgreSQL + + If the column is an identity column, then ALWAYS + or BY DEFAULT, reflecting the definition of the + column. + 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 ] name ALTER [ COLUMN ] column_name SET DEFAULT expression ALTER [ COLUMN ] column_name DROP DEFAULT ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL + ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] + ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...] + ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ] ALTER [ COLUMN ] column_name SET STATISTICS integer ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] ) ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] ) @@ -187,6 +190,38 @@ ALTER TABLE [ IF EXISTS ] name + + ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY + SET GENERATED { ALWAYS | BY DEFAULT } + DROP IDENTITY [ IF EXISTS ] + + + These forms change whether a column is an identity column or change the + generation attribute of an existing identity column. + See for details. + + + + If DROP IDENTITY IF EXISTS is specified and the + column is not an identity column, no error is thrown. In this case a + notice is issued instead. + + + + + + SET sequence_option + RESTART + + + These forms alter the sequence that underlies an existing identity + column. sequence_option is an option + supported by such + as INCREMENT BY. + + + + SET STATISTICS @@ -1160,8 +1195,11 @@ ALTER TABLE [ IF EXISTS ] name - The TRIGGER, CLUSTER, OWNER, - and TABLESPACE actions never recurse to descendant tables; + The actions for identity columns (ADD + GENERATED, SET etc., DROP + IDENTITY), as well as the actions + TRIGGER, CLUSTER, OWNER, + and TABLESPACE never recurse to descendant tables; that is, they always act as though ONLY were specified. Adding a constraint recurses only for CHECK constraints that are not marked NO INHERIT. @@ -1371,8 +1409,9 @@ ALTER TABLE cities The forms ADD (without USING INDEX), - DROP, SET DEFAULT, - and SET DATA TYPE (without USING) + DROP [COLUMN], DROP IDENTITY, RESTART, + SET DEFAULT, SET DATA TYPE (without USING), + SET GENERATED, and SET sequence_option conform with the SQL standard. The other forms are PostgreSQL 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 @@ -479,6 +479,13 @@ COPY count constraints on the destination table. However, it will not invoke rules. + + For identity columns, the COPY FROM command will always + write the column values provided in the input data, like + the INPUT option OVERRIDING SYSTEM + VALUE. + + COPY input and output is affected by DateStyle. To ensure portability to other 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 ( expression ) [ NO INHERIT ] | DEFAULT default_expr | + GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] | UNIQUE index_parameters | PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] @@ -81,7 +82,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI and like_option is: -{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } +{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES | STORAGE | COMMENTS | ALL } and partition_bound_spec is: @@ -412,6 +413,11 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI Column STORAGE settings are also copied from parent tables. + + 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. + @@ -480,6 +486,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI such as nextval, may create a functional linkage between the original and new tables. + + Any identity specifications of copied column definitions will only be + copied if INCLUDING IDENTITY is specified. A new + sequence is created for each identity column of the new table, separate + from the sequences associated with the old table. + Not-null constraints are always copied to the new table. CHECK constraints will be copied only if @@ -512,7 +524,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI INCLUDING ALL is an abbreviated form of - INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS. + INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS. Note that unlike INHERITS, columns and @@ -626,6 +638,37 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI + + GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] + + + This clause creates the column as an identity + column. 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. + + + + The clauses ALWAYS and BY DEFAULT + determine how the sequence value is given precedence over a + user-specified value in an INSERT statement. + If ALWAYS is specified, a user-specified value is + only accepted if the INSERT statement + specifies OVERRIDING SYSTEM VALUE. If BY + DEFAULT is specified, then the user-specified value takes + precedence. See for details. (In + the COPY command, user-specified values are always + used regardless of this setting.) + + + + The optional sequence_options clause can be + used to override the options of the sequence. + See for details. + + + + UNIQUE (column constraint) UNIQUE ( column_name [, ... ] ) (table constraint) @@ -1263,7 +1306,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI Using OIDs in new applications is not recommended: where - possible, using a SERIAL 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 <> '') ); @@ -1737,6 +1780,20 @@ CREATE TABLE cities_ab_10000_to_100000 + + Multiple Identity Columns + + + PostgreSQL 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 INSERT command supports only one override clause + that applies to the entire statement, so having multiple identity columns + with different behaviors is not well supported. + + + <literal>LIKE</> Clause 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 [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] + [ OVERRIDING { SYSTEM | USER} VALUE ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] @@ -201,11 +202,44 @@ INSERT INTO table_name [ AS + + OVERRIDING SYSTEM VALUE + + + Without this clause, it is an error to specify an explicit value + (other than DEFAULT) for an identity column defined + as GENERATED ALWAYS. This clause overrides that + restriction. + + + + + + OVERRIDING USER VALUE + + + If this clause is specified, then any values supplied for identity + columns defined as GENERATED BY DEFAULT are ignored + and the default sequence-generated values are applied. + + + + This clause is useful for example when copying values between tables. + Writing INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM + tbl1 will copy from tbl1 all columns that + are not identity columns in tbl2 but will continue + the sequence counters for any identity columns. + + + + DEFAULT VALUES All columns will be filled with their default values. + (An OVERRIDING clause is not permitted in this + form.) @@ -710,6 +744,13 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') is disallowed by the standard. + + The SQL standard specifies that OVERRIDING SYSTEM VALUE + 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. + + Possible limitations of the query clause are documented under -- cgit v1.2.3