PostgreSQL CREATE SEQUENCE: Introduction, Syntax & Examples

By /
Create Sequence In Postgresql

In this tutorial, we will learn to create a sequence in PostgreSQL. This topic might seem a little bit confusing but trust me, it is as simple as creating a table. So, without getting distracted from the topic, let’s dive straight into it.

Introduction to PostgreSQL Sequence

A sequence is simply an object that generates unique numbers in sequence. It is exactly the same as the SERIAL data type. However, you can have more control over the sequence of numbers that are being generated.

For example, you can start the sequence from 0 or a negative number and increment each number by 2. It can have a maximum number after which you can restart from the first number if you want. These are some of the best things about the sequences in PostgreSQL.

Note that, a sequence can be ascending and descending as well.

I hope you guys have got an idea now of what a sequence is. Let’s see its syntax.

PostgreSQL CREATE SEQUENCE Syntax

In PostgreSQL, a sequence is created using the command CREATE SEQUENCE. Following is the syntax for creating a sequence in PostgreSQL.

Syntax:

CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] 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 } ]
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here,

  • TEMPORARY or TEMP – It creates the sequence only for the current session. The sequence gets deleted automatically when the session ends.
  • UNLOGGED – Changes in an unlogged sequence don’t get written to the write-ahead log.
  • AS data_type – You can specify the data type for the sequence. The valid data types are SMALLINT, INT, and BIGINT where the BIGINT is the default.
  • INCREMENT – It calculates the new value in the sequence by adding the specified value. In the case of descending sequence, it will subtract.
  • MINVALUE, MAXVALUE – These are the minimum and maximum values your sequence can have. If no MINVALUE or MAXVALUE is specified, the default value will be used.
  • START WITH – You can specify the starting value using this keyword. If no START value is specified, the MINVALUE and MAXVALUE will be used as a starting value in ascending sequence and descending sequence respectively.
  • CACHE – It specifies how many sequence numbers are preallocated and stored in the memory for faster access later.
  • CYCLE or NO CYCLE – After the MINVALUE OR MAXVALUE limit is reached, this option restarts according to ascending or descending sequence.
  • OWNED BY – When specified the table name, the sequence gets associated with that table. So, if the table is dropped, the sequence gets dropped too. The table and sequence must have the same owner and must be in the same schema. If specified NONE, there is no association to any table.

From the above available options, we hardly use 3-4 on a practical basis. Therefore, no need to fear to remember all those options.

Let’s now head towards some examples and see how sequences are used in real-life problems.

PostgreSQL CREATE SEQUENCE Examples

Let’s create a simple sequence first and see what operations we can perform on it.

Create an Ascending Sequence

CREATE SEQUENCE startFrom101Sequence START 101
INCREMENT BY 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created a sequence that starts from 101 and has the BIGINT data type by default. Also, the sequence would generate a new number by +2 every time as we have specified the increment by 2.

You can check the sequence details using the \d tableName command.

Create Simple Sequence
Create Simple Sequence

As you can see in the output above, PostgreSQL has displayed all the details of the sequence that we created recently.

Note that, we have not associated this sequence with any table. Therefore, any table can use it.

PostgreSQL provides us with a function called ‘NEXTVAL(sequenceName)’ which generates a new value in the sequence every time you execute it. Let’s see it in action.

SELECT NEXTVAL('startFrom101Sequence');
SELECT NEXTVAL('startFrom101Sequence');
SELECT NEXTVAL('startFrom101Sequence');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have executed the same statement three times, therefore we should get the sequence of numbers as – 101, 103, 105. Let’s see the output.

Nextval() Function
Nextval() Function

Note that, all the values generated in the sequence using the NEXTVAL function are preserved. Therefore, even if you use multiple sessions or restart the current session, the function will continue from the last value.

Create a Descending Sequence

Let’s create a descending sequence now.

CREATE SEQUENCE descSequence
START 5
INCREMENT BY -1
MAXVALUE 5
MINVALUE 0
CYCLE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created a sequence that starts from 5, increments by -1 (descending) and restarts from the maximum value when the sequence reaches the minimum value specified.

Let’s see by executing the NEXTVAL function more than 5 times.

Create Descending Sequence
Create Descending Sequence

Here, we have already executed the NEXTVAL function 4 times and the output above shows the result when the function is executed for 5th, 6th and 7th times. After it reached 0, the sequence restarted from 5.

Use Sequence in the Table

Let’s create a new table along with the new sequence and use that sequence in the table.

CREATE SEQUENCE generatePRN
START 1000
INCREMENT BY 1 
NO CYCLE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created a sequence that will start from the number 1000 and has a max value of big int’s limit. As we don’t want duplicate sequence numbers, we choose the NO CYCLE option.

Let’s check the sequence information once again.

GeneratePRN Sequence
GeneratePRN Sequence

Perfect! Let’s create a table now and use this sequence.

CREATE TABLE students(
id SERIAL,
PRN BIGINT DEFAULT NEXTVAL('generatePRN') UNIQUE,
firstName VARCHAR(100),
lastName VARCHAR(100)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created a table having the PRN column to which we have set the default value. Note that, every time a new record gets inserted into the table, the default value automatically generates the new sequence number and inserts it.

Let’s check it by inserting three records.

INSERT INTO students (firstName, lastName) VALUES ('John', 'Doe');
INSERT INTO students (firstName, lastName) VALUES ( 'Jane', 'Smith');
INSERT INTO students (firstName, lastName) VALUES ( 'Alice', 'Johnson');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Use Sequence In Table
Use the Sequence in Table

As you can see, NEXTVAL has generated the sequence numbers and inserted them with the records.

Note that, if you want to associate the sequence with the table, you need to create the table before the sequence. So that you can use the OWNED BY option while creating the sequence.

Also, in such a case, you can not use the DEFAULT option because, at the time of table creation, the sequence doesn’t exist, so you would end up getting an error from PostgreSQL.

Display All Sequences

To list all sequences from the database, we use the following command.

SELECT relname sequence_name
FROM pg_class 
WHERE relkind = 'S';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Display All Sequencees
Display All Sequences

As you can see, PostgreSQL has displayed all our sequences.

Delete Sequence

To delete the sequence, we use the DROP command exactly the same way as the DROP table. Let’s see its syntax.

Syntax:

DROP SEQUENCE [IF EXISTS] sequenceName [, ...] 
[CASCADE | RESTRICT]; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here,

  • You can specify multiple sequence names after DROP SEQUENCE separated by a comma.
  • CASCADE | RESTRICT – If you want to delete all the objects which depend on the specified sequence, use the CASCADE option. The RESTRICT option is by default and it does the opposite of the CASCADE.

Let’s now drop the sequences that we have created.

DROP SEQUENCE IF EXISTS
descsequence, startfrom101sequence;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Delete Sequences
Delete Sequences

As you can see, the sequences that we tried to delete have been successfully removed from the database.

Conclusion

In this tutorial, we learned about the sequences in PostgreSQL. I hope you have learned so many new things about the sequences such as descending sequence, cycle, using sequence on the table, etc. We highly recommend going through the official documentation as well to learn some more details if you are interested in learning hardly-used things.

Reference

PostgreSQL official documentation