Identity columns

An identity column is a special column that's generated automatically from an implicit sequence. It can be used to generate key values. To create an identity column, use the GENERATED ... AS IDENTITY clause in CREATE TABLE, for example:

CREATE TABLE people (
    id bigint GENERATED ALWAYS AS IDENTITY (CACHE 70000),
    ...
);

or alternatively:

CREATE TABLE people (
    id bigint GENERATED BY DEFAULT AS IDENTITY (CACHE 70000),
    ...
);

See CREATE TABLE for more details.

If an INSERT 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 preceding 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 the id column starting at 1 and result in the following table data:

 id | name | address
----+------+---------
  1 | A    | foo
  2 | B    | bar

Alternatively, the keyword DEFAULT can be specified in place of a value to explicitly request the sequence-generated value:

INSERT INTO people (id, name, address) VALUES (DEFAULT, 'C', 'baz');

Similarly, the keyword DEFAULT can be used in UPDATE commands.

Thus, in many ways, an identity column behaves like a column with a default value.

The clauses ALWAYS and BY DEFAULT in the column definition determine how explicitly user-specified values are handled in INSERT and UPDATE commands. In an INSERT command, if ALWAYS is selected, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is selected, then the user-specified value takes precedence. Thus, using BY DEFAULT results in a behavior more similar to default values, where the default value can be overridden by an explicit value, whereas ALWAYS 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. (See CREATE SEQUENCE.) The properties of the associated sequence might be specified when creating an identity column (see CREATE TABLE) or changed afterwards (see ALTER TABLE).

An identity column is automatically marked as NOT NULL. An identity column, however, doesn't 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 previously.) Uniqueness would need to be enforced using a PRIMARY KEY or UNIQUE constraint.