Sequence Defaults
The default value for a column can be the next value in a sequence.
The Sequence-operator can be in either form NEXT VALUE FOR sequence or sequence.NEXTVAL, where sequence is a sequence name, optionally specified as owner.sequence.
For example:
CREATE SEQUENCE lineitemsequence;
CREATE TABLE lineitem
(itemid INTEGER NOT NULL,
itemseq INTEGER NOT NULL WITH DEFAULT NEXT VALUE FOR lineitemsequence);
INSERT INTO lineitem (itemid) VALUES (4);
INSERT INTO lineitem VALUES (8, NEXT VALUE FOR lineitemsequence);
INSERT INTO lineitem VALUES (15, lineitemsequence.nextval);
INSERT INTO lineitem VALUES (16, 23);
INSERT INTO lineitem (itemid) VALUES (42);
Note: If the schema of the sequence providing the default value is not specified, then it defaults to the schema (owner) of the table. The user that inserts a row into the table that uses the sequence must have been
granted (see
GRANT (privilege)) the NEXT privilege to be able to retrieve the value from the sequence.
Sequence defaults are allowed on numeric columns only (integer, bigint, float, decimal). The column data type need not match the sequence data type exactly; sequence values are coerced to column values, if necessary.
If a row is inserted with some auto-incrementing columns defaulted, all relevant sequences are incremented once before the row is inserted, and the new sequence values are used whenever referenced in the row. This means that if two columns reference the same sequence for their default, they receive the same value, not two successive values.
Sequence defaulting is allowed in all contexts where column defaulting is allowed: INSERT, UPDATE (using SET COLUMN=DEFAULT), and COPY (including bulk copy).
Columns added or altered with the ALTER TABLE statement cannot use sequence defaults.
Defining a sequence default does not prevent you from explicitly assigning a value to the column upon insert or update. An explicitly assigned value may collide with a defaulted value from the sequence.