How to create a sequence in Postgres
The sequences in Postgres are created by following the syntax of CREATE SEQUENCE command provided below:
The <name-of-sequence> denotes the name that you want to set for a sequence and the <options> contains the extended properties for that specific sequence. The options supported by CREATE SEQUENCE are mentioned in the following section:
Options supported by CREATE SEQUENCE command
It is possible to create a sequence that has user-defined parameters using the CREATE SEQUENCE command. The following options are supported by this command:
[ INCREMENT [ BY] <value> ]: This option creates the sequence with an increment of a specific numeric number. The default value is 1. Moreover, if the increment value is positive then the order will be ascending whereas the descending order may be obtained by passing the negative value.
[AS { SMALLINT | INT | BIGINT } ]: The data type parameter allows you to define the pattern of sequence. By default, the BIGINT is used as a datatype.
[ MINVALUE <value> ] or [ NO MINVALUE ]: The minimum value of the sequence can be specified by using this option. If this option is left blank, then the default maximum value is set according to the specified data type.
[ MAXVALUE <value> ] or [ NO MINVALUE ]: This option is reciprocal to the above-mentioned, where you can set the max value for your sequence. Moreover, if not set, the default value of the data type is considered.
[ START [WITH] start ]: Indicates the value of the beginning of the sequence.
[CYCLE] or [NO CYCLE]: If this option is defined, the sequence value is resumed after reaching the maximum limit. The default value, in this case, is NO CYCLE and it returns an error after reaching the specified limit of the sequence.
[OWNED BY <table-name.column-name>]: This option is used to associate the sequence with a specific column of a table. Resultantly, when the column is deleted, the sequence is also deleted automatically.
The upcoming sections will better clarify the concept of a sequence in Postgres.
How to create a sequence in Postgres
This section presents several examples to create a sequence from multiple perspectives. Each example refers to a different kind of sequence.
Example 1 : This command creates a sequence called “linuxhint” with the starting value of 5 and the increment value of 2:
Example 2 : Moreover, if the increment is set to any negative (-) value then the sequence starts from the maximum value and descends to the specified minimum value. For instance, the command provided below will create a sequence with the following properties:
– “linux” as sequence name
– “-2” as increment value which means the sequence will descend with a difference of -2.
– “10” as starting value, since the increment is a negative value the maximum value would also be 10.
– Lastly, the cycle parameter is also used
How to get sequences of a database
Each sequence is associated with a database. For instance, we are logged into the linuxhint database and we have retrieved all sequences with the help of the command provided below:
In the below-stated command, the relname and sequence_name are obtained from PG_CLASS. The PG_CLASS in Postgres contains the information of tables about the database(s).
You would see two categories in the output; one refers to the sequences associated with the primary key of tables and the other category indicates the user-defined sequences that are created separately (as in the above section).
How to get the next/current value of a sequence
There are several functions that can be used to get the current and next value of a sequence in Postgres. The NEXTVAL function prints the next value in a sequence by using the syntax given below:
The command written below will print the next value of the “linuxhint” sequence. If you keep on running the NEXTVAL command then you would get the next value as per the increment condition of the sequence. As the “linuxhint” sequence is incremented by 2, so each next value would be printed after incremented by 2.
Once the next value is obtained, you can get the current value of the sequence by using the CURRVAL function. The CURRVAL will print the output that is obtained by the NEXTVAL function. For instance, the last NEXTVAL function has shown output 7, so the CURRVAL result must be 7. Similarly, whatever value is obtained using the recent NEXTVAL function, must be printed by CURRVAL. The CURRVAL function operates on the following syntax:
Let’s get the current value of the “linuxhint” sequence with the help of the command mentioned above:
Moreover, the CURRVAL function depends on the NEXTVAL function. If the NEXTVAL function is not yet applied to any sequence, you cannot use the CURRVAL function to get the current value. For instance, if we apply the CURRVAL function on the “linux” sequence on which we have not applied the NEXTVAL function then the following error will be displayed.
This error can be solved by executing the NEXTVAL function on the sequence as we experienced it on the “linux” sequence, and the error is resolved.
# SELECT CURRVAL('linux');
How to delete a sequence
You can drop any sequence from your database by using the DROP SEQUENCE statement of Postgres. In the event of dropping the table, a sequence owned by any column would be deleted. The command provided here drops the “linux” sequence.
Conclusion
Generally, what comes to your mind after getting the sequence word? An ordered list of numbers. Yes, the concept is the same in Postgres as well. This post explains the concept of the sequence and demonstrates its basic functionality in Postgres. We have studied sequence creation in multiple ways in this guide. Moreover, the next and current value functions of sequences are also discussed.
