How to use PostgreSQL Sequences

Introduction

In this article we will learn how to use Sequences in PostgreSQL. First let’s go over some prerequisites.

Prerequisites

• Ensure that PostgreSQL server is properly installed, configured and running on the background.

For Linux and Windows systems you can download PostgreSQL here

• To start PostgreSQL server use a LINUX machine use the following command.

• To verify if the service is running use the following command.

• The result should look something like the following:

• To start, stop and restart PostgreSQL server in a Windows machine do the following instruction.

1. Open Control Panel
2. Open Administrative Tools
3. Open Services
4. Find the PostgreSQL Server service
5. Start, Stop or Restart the service

What is PostgreSQL Sequences

PostgreSQL Sequence objects (additionally called sequence generator or just sequences) are single-row tables created thru a command from the command line: CREATE SEQUENCE. The PostgreSQL are most customarily used for the creation of specific identifiers among the rows in the table. The ‘sequence’ functions allows a simple and secure multi-user technique for extracting sequence values from sequence objects.

The below syntax shows how a CREATE SEQUENCE affirmation:

Let’s explain the above statement part by part.

1. The SEQUENCE NAME indicates the sequence name right after CREATE SEQUENCE. Then IF NOT EXISTS creates new sequence once it doesn’t exist. The name of the sequence must be unique from other tables, sequences, and foreign tables especially if they belong in the same structure.

2. [ AS { SMALLINT | INT | BIGINT } ] is where we declare the sequence’s data type. The valid and accepted data types are INT, SMALLINT, and BIGINT which is the default data type.

3. The [ INCREMENT [ BY ] increment ] specifies values that will be combined to present sequence to be able to create a new value. Positive number makes an the sequence ascending while descending sequence is a non-positive number. One (1) is increment value by default.

4. [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] Defines both the sequence’s minimum and maximum values. To use the default value we can use both NO MAXVALUE and NO MINVALUE. In the ascending sequence, one (1) is the minimum value by default while sequence’s data type maximum value will be the maximum value. On the other hand descending sequence, negative one (-1) is the maximum default value and the sequence’s data type minimum value will be the minimum value.

5. [ START [ WITH ] start ] will be specifying the starting sequence’s value. The minvalue will be the starting value by default for the ascending sequence while the maxvalue is for the descending sequence.

6. The CACHE will determine the number of the pre-allocated sequence numbers and preserved in an internal storage such as memory for prompt access. Sequence will be generated one value after another by default.

7. The CYCLE will permits you to refresh again the value once the threshold is reached. Succeeding numeric value will be ascending sequence’s minimum value and descending sequence maximum value. NO CYCLE will generate an error if will try to attempt to proceed to the succeeding value when the threshold is reached. Also NO CYCLE will be the default even if bot NO CYCLE or CYCLE were explicitly specified.

8. OWNED BY will allow you to partner the column of the table with a particular sequence in order for whilst you able to drop the table or column, the related sequence will automatically be dropped by PostgreSQL.

Using CREATE SEQUENCE in examples

Now that we have an overview understanding about the component of the syntax, let’s take that knowledge using several examples.

How to Create PostgreSQL Ascending Sequence.

The below statement will be using CREATE SEQUENCE that will be creating a fresh ascending sequence that starts from one hundred (100) that increment by ten (10):

Then we run the following statement by calling the sample_sequence, using the following command.

The result should look something like the following.

If we are going to execute the above statement again, we will be able to retrieve the succeeding value from the current sequence:

How to Create a PostgreSQL Descending Sequence.

In the previous section we created an ascending sequence, now in this section we will be creating a descending sequence.

To do this use the below statement that will create the descending sequence that starts from ten (10) going down to one (1) using CYCLE option:

We can execute the above statement using the following syntax.

Whenever the statement will be executed in multiple, we will be able observe that the number will start from ten (10) down to one (1) and once it reached it’s limit it will go back again from the start because of the CYCLE option.

How to Delete Sequences

Once a column of a particular table or the entirety of the table was dropped, the associated sequence will automatically be dropped. We can also manually discard the sequence with the help of the statement DROP SEQUENCE.

We can use the following syntax.

We have to specify the sequence’s name that we want to discard, then we use IF EXISTS preference to conditionally discard the sequence should it exists. Names of sequence can be used to drop a number of sequences using comma-separated format. The CASCADE will drop objects that have a dependency on a particular sequence also the series of objects that have dependency on those other objects.

The below example will be using DROP SEQUENCE to drop the two sequences namely sample_sequence and ten_down:

PostgreSQL notify us by giving us a result of DROP SEQUENCE

Conclusion

In this article we showed you how to use the SEQUENCE command in PostgreSQL. We hope you can apply this knowledge to your specific application.