[ START [ WITH ] start_value ]
[ INCREMENT [ BY ] increment_value ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ size ] } | { NO CACHE } ];
[ OWNED BY { table_name.column_name | NONE } ]
After CREATE SEQUENCE you can specify the optional IF NOT EXISTS clause, which will create a sequence only if it does not exist. If a sequence already exists, Postgres will only give a warning instead of an error and skip creating a new sequence.
The sequence_name is the unique name of a sequence that you want to create.
Integer_type: A sequence is defined with any of the integer types as INT, SMALLINT, BIGINT, and NUMERIC data type.
start_value: The first value in the sequence.
increment_value: This is the interval between two consecutive sequence values. If the increment value is negative, then the sequence is a decreasing sequence else it is ascending. The default increment value is 1. The Increment cannot be 0.
MINVALUE: This specifies the lower bound for a sequence. If not specified, it defaults to the minimum value of the data type of the sequence.
MAXVALUE: Specify the upper bound for the sequence. It defaults to the maximum value of the data type of the sequence.
CYCLE: Specifies whether the sequence object should restart from the minimum value (maximum value for descending sequence) or raise an exception when the minimum (or maximum) value is reached. NO CYCLE is the default value. Cycling will restart the sequence from the minimum or maximum value and not from the start value.
CACHE [ size ]: Improves performance for applications using sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers. PostgreSQL pre-allocates the number of sequence numbers specified by the CACHE.
OWNED BY: Allows you to associate the column of a table with a sequence, so that when you drop the column or table, PostgreSQL will drop the associated sequence automatically.
Sets the sequence's current value. The first parameter is the sequence name, the second parameter is the value to be set as the current value, and the third parameter is a Boolean value TRUE or FALSE. If FALSE then
nextval()
will return the specified value, if TRUE then
nextval()
will increment the specified value and return it.
SELECT currval('myfirstsequence') – error; cannot use currval() if nextval() never called
SELECT nextval('myfirstsequence') –- returns 1
SELECT currval('myfirstsequence') -- returns 1
SELECT nextval('myfirstsequence') –- returns 2
SELECT lastval() – returns 2
SELECT setval('myfirstsequence', 10, false)
SELECT nextval('myfirstsequence') -- returns 10
SELECT setval('myfirstsequence', 10, TRUE)
SELECT nextval('myfirstsequence') –- returns 11
SELECT lastval() -- returns 11
SELECT nextval('descsequence'); --returns 5
SELECT nextval('descsequence'); --returns 4
SELECT nextval('descsequence'); --returns 3
SELECT nextval('descsequence'); --returns 2
SELECT nextval('descsequence'); --returns 1
SELECT nextval('descsequence'); --returns 5
SELECT nextval('descsequence'); --returns 4
INSERT INTO employee(EMP_ID, FIRST_NAME, LAST_NAME, EMAIL)
VALUES
(nextval('employee_seq'),'Annie','Smith','[email protected]'),
(nextval('employee_seq'), 'Susan', 'Klassen', '[email protected]'),
(nextval('employee_seq'), 'May', 'Kaasman', '[email protected]');
TutorialsTeacher.com is optimized for learning web technologies step by step.
Examples might be simplified to improve reading and basic understanding.
While using this site, you agree to have read and accepted our terms
of use and privacy policy.
