5.3. Identity Columns#
An identity column is a special column that is generated automatically from an implicit sequence. It can be used to generate key values.
To create an identity column, use theGENERATED ... AS IDENTITY clause inCREATE TABLE, for example:
CREATE TABLE people ( id bigintGENERATED ALWAYS AS IDENTITY, ...,);or alternatively
CREATE TABLE people ( id bigintGENERATED BY DEFAULT AS IDENTITY, ...,);SeeCREATE TABLE for more details.
If anINSERT 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
INSERT INTO people (name, address) VALUES ('A', 'foo');INSERT INTO people (name, address) VALUES ('B', 'bar'); would generate values for theid column starting at 1 and result in the following table data:
id | name | address----+------+--------- 1 | A | foo 2 | B | bar
Alternatively, the keywordDEFAULT can be specified in place of a value to explicitly request the sequence-generated value, like
INSERT INTO people (id, name, address) VALUES (DEFAULT, 'C', 'baz'); Similarly, the keywordDEFAULT can be used inUPDATE commands.
Thus, in many ways, an identity column behaves like a column with a default value.
The clausesALWAYS andBY DEFAULT in the column definition determine how explicitly user-specified values are handled inINSERT andUPDATE commands. In anINSERT command, ifALWAYS is selected, a user-specified value is only accepted if theINSERT statement specifiesOVERRIDING SYSTEM VALUE. IfBY DEFAULT is selected, then the user-specified value takes precedence. Thus, usingBY DEFAULT results in a behavior more similar to default values, where the default value can be overridden by an explicit value, whereasALWAYS provides some more protection against accidentally inserting an explicit value.
The data type of an identity column must be one of the data types supported by sequences. (SeeCREATE SEQUENCE.) The properties of the associated sequence may be specified when creating an identity column (seeCREATE TABLE) or changed afterwards (seeALTER TABLE).
An identity column is automatically marked asNOT NULL. 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 aPRIMARY KEY orUNIQUE constraint.
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. DuringINSERT orUPDATE, 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.
Partitions inherit identity columns from the partitioned table. They cannot have their own identity columns. The properties of a given identity column are consistent across all the partitions in the partition hierarchy.