‘CREATE SEQUENCE’ statement define a sequence generator. Once a sequence is defined, you can use the functions nextval, currval, and setval to operate on the sequence.
Example 1: Sequence of numbers from 1 to 5.
[1, 2, 3, 4, 5]
Example 2: Sequence of numbers from 2 to 10 with an increment of 2.
[2, 4, 6, 8, 10]
Example 3: Descending sequence of numbers from 10 with an increment of -2
[10, 8, 6, 4, 2, 0, -2, -4…..]
Synopsis
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name
[ AS data_type ]
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]
Clauses or parameters specified in [] are optional.Below table summarizes the different parameters of CREATE SEQUENCE statement.
|
Parameter |
Description |
|
TEMPORARY | TEMP |
With this the sequence object is created only for this session. This sequence object is deleted on the session exit. |
|
IF NOT EXISTS |
Create a sequence only if the sequence with given name is not exists. |
|
name |
Sequence name |
|
data_type |
Specify sequence data type. bigint is used as default, apart from bigint, you can use smallint and integer. |
|
increment |
Specify the value to be added to existing sequence to get new value. A positive increment generate an ascending sequence, where as a negative increment generate a descending sequence. |
|
minvalue |
Specifies the minimum value a sequence generate. a. Default for ascending sequence is 1. b. Default for descending sequence is minimum value of data_type |
|
maxvalue |
Specifies the maximum value a sequence generate
a. Default for ascending sequence is maximum value of data_type b. Default for descending sequence is -1. |
|
start |
Specify the starting number of sequence. The default starting value is minvalue for ascending sequences and maxvalue for descending ones. |
|
cache |
Specifies how many sequence numbers are to be preallocated and stored in memory for faster access. |
|
CYCLE, NO CYCLE |
The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively.
If NO CYCLE is specified, any calls to nextval after the sequence has reached its maximum value will return an error. If neither CYCLE or NO CYCLE are specified, NO CYCLE is the default. |
|
OWNED BY table_name.column_name
OWNED BY NONE |
The OWNED BY option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well. |
Let’s create a sequence and experiment with it ‘test’ database.
Login to test database
sh-3.2$ psql -U postgres -h localhost --dbname=test
Password for user postgres:
psql (14.4)
Type "help" for help.
Create a sequence to generate even positive numbers
CREATE SEQUENCE even_number_sequence INCREMENT 2 START 2;
test=# CREATE SEQUENCE even_number_sequence INCREMENT 2 START 2;
CREATE SEQUENCE
Let’s query existing relationships (tables, views, sequences etc.,) using \d or \d+ command.
test=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+----------+----------
public | even_number_sequence | sequence | postgres
(1 row)
test=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------------------+----------+----------+-------------+---------------+------------+-------------
public | even_number_sequence | sequence | postgres | permanent | | 8192 bytes |
(1 row)
Query the sequence table
test=# SELECT * FROM even_number_sequence;
last_value | log_cnt | is_called
------------+---------+-----------
2 | 0 | f
(1 row)
Whether this sequence is called or not maintained by the column ‘is_called’.
Get the next value from sequence table using nextval function
test=# SELECT nextval('even_number_sequence');
nextval
---------
2
(1 row)
test=#
test=# SELECT nextval('even_number_sequence');
nextval
---------
4
(1 row)
Query 'even_number_sequence' and confirm that the last_value is set to 4 now.
test=# SELECT * FROM even_number_sequence;
last_value | log_cnt | is_called
------------+---------+-----------
4 | 31 | t
(1 row)
Get the current value of sequence using currval function
test=# SELECT currval('even_number_sequence');
currval
---------
4
(1 row)
Set the next value using setval function
SELECT setval('even_number_sequence', 18);
Next nextval will return 20 for the increment of 2
SELECT setval('even_number_sequence', 18, true);
Same as above
test=# SELECT setval('even_number_sequence', 18);
setval
--------
18
(1 row)
test=#
test=# SELECT * FROM even_number_sequence;
last_value | log_cnt | is_called
------------+---------+-----------
18 | 0 | t
(1 row)
test=#
test=# SELECT nextval('even_number_sequence');
nextval
---------
20
(1 row)
test=#
test=# SELECT * FROM even_number_sequence;
last_value | log_cnt | is_called
------------+---------+-----------
20 | 32 | t
(1 row)
SELECT setval('even_number_sequence', 20, false);
Next nextval will return 20.
test=# SELECT setval('even_number_sequence', 20, false);
setval
--------
20
(1 row)
test=#
test=# SELECT * FROM even_number_sequence;
last_value | log_cnt | is_called
------------+---------+-----------
20 | 0 | f
(1 row)
test=#
test=# SELECT nextval('even_number_sequence');
nextval
---------
20
(1 row)
test=#
test=# SELECT * FROM even_number_sequence;
last_value | log_cnt | is_called
------------+---------+-----------
20 | 32 | t
(1 row)
Get all the sequences in a database?
Query 'information_schema.sequences' to get all the sequences defined in given database.
test=# SELECT * FROM information_schema.sequences;
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
------------------+-----------------+----------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
test | public | even_number_sequence | bigint | 64 | 2 | 0 | 2 | 1 | 9223372036854775807 | 2 | NO
(1 row)
Drop a sequence
‘DROP SEQUENCE {sequence_name}’ is used to drop the given sequence.
test=# DROP SEQUENCE even_number_sequence;
DROP SEQUENCE
test=# ^
test=# SELECT * FROM information_schema.sequences;
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
------------------+-----------------+---------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------+-----------+--------------
When to use sequences?
Since sequences are used to produce unique values, we can use them in primary key auto generation process of tables. ORM frameworks like Hibernate use sequences to generate a primary of table. Without a database support for sequences it is very hard to generate unique incrementing numbers.
References
https://www.postgresql.org/docs/current/sql-createsequence.html
https://www.postgresql.org/docs/current/functions-sequence.html
No comments:
Post a Comment