Home
PostgreSQL

PostgreSQL Sequences

PostgreSQL sequences are powerful tools for generating unique identifiers in databases. Understanding how to create, use, and manage sequences is ...

PostgreSQL, an open-source relational database management system, offers a robust feature known as sequences. Sequences play a crucial role in generating unique identifiers for primary keys in database tables. 

PostgreSQL Sequences

In this article, we'll delve into the intricacies of PostgreSQL sequences, their usage, advantages, and best practices.

Introduction to PostgreSQL Sequences

Sequences in PostgreSQL are special database objects that generate unique numeric values. They are often used to generate primary key values automatically, ensuring each row in a table has a unique identifier. Unlike auto-increment columns in some other database systems, sequences are independent objects that can be shared across multiple tables.

Syntax:

The syntax of the PostgreSQL CREATE SEQUENCE statement is as follows:

CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name
    [ AS { SMALLINT | INT | BIGINT } ]  -- Specifies the data type of the sequence.
    [ INCREMENT [ BY ] increment ]      -- Sets the increment value by which the sequence advances. Default is 1.
    [ MINVALUE minvalue | NO MINVALUE ] -- Sets the minimum value for the sequence or specifies no minimum value.
    [ MAXVALUE maxvalue | NO MAXVALUE ] -- Sets the maximum value for the sequence or specifies no maximum value.
    [ START [ WITH ] start ]            -- Sets the initial value of the sequence. Default is the minimum value.
    [ CACHE cache ]                     -- Specifies how many sequence values are preallocated and stored in memory for faster access.
    [ [ NO ] CYCLE ]                    -- Specifies whether the sequence should cycle back to the minimum value after reaching the maximum value.
    [ OWNED BY { table_name.column_name | NONE } ]  -- Specifies the table and column owning the sequence, or NONE if not owned by any column.

Explanation of each parameter:

  • IF NOT EXISTS: Optional clause to prevent an error if the sequence already exists.
  • sequence_name: Name of the sequence to be created.
  • AS { SMALLINT | INT | BIGINT }: Optional parameter to specify the data type of the sequence. Default is INT.
  • INCREMENT [ BY ] increment: Optional parameter to set the increment value by which the sequence advances. Default is 1.
  • MINVALUE minvalue | NO MINVALUE: Optional parameter to set the minimum value for the sequence or specify no minimum value.
  • MAXVALUE maxvalue | NO MAXVALUE: Optional parameter to set the maximum value for the sequence or specify no maximum value.
  • START [ WITH ] start: Optional parameter to set the initial value of the sequence. Default is the minimum value.
  • CACHE cache: Optional parameter to specify how many sequence values are preallocated and stored in memory for faster access.
  • [ NO ] CYCLE: Optional parameter to specify whether the sequence should cycle back to the minimum value after reaching the maximum value.
  • OWNED BY { table_name.column_name | NONE }: Optional parameter to specify the table and column owning the sequence, or NONE if not owned by any column.

PostgreSQL CREATE SEQUENCE examples

Here are a few examples of using the CREATE SEQUENCE statement in PostgreSQL:

Creating an ascending sequence

-- Create a sequence
CREATE SEQUENCE sequence_example
    START WITH 1
    INCREMENT BY 1;

-- Create a table and use the sequence to populate a column
CREATE TABLE sample_data (
    id SERIAL PRIMARY KEY,
    sequence_number INTEGER
);

-- Insert data into the table using the sequence
INSERT INTO sample_data (sequence_number)
SELECT nextval('sequence_example')
FROM generate_series(1, 10); -- Change 10 to the number of rows you want

-- Query the data to see the ascending sequence
SELECT * FROM sample_data;

Output:

 id | sequence_number 
----+-----------------
  1 |               1
  2 |               2
  3 |               3
  4 |               4
  5 |               5
  6 |               6
  7 |               7
  8 |               8
  9 |               9
 10 |              10

This code will create a sequence starting from 1 and incrementing by 1. Then, it creates a table sample_data with a primary key id and another column sequence_number to store the sequence. It inserts 10 rows into the table using the sequence, and finally, it queries the table to see the data.

Creating a descending sequence

To create a descending sequence in PostgreSQL, you can use a negative increment value in the CREATE SEQUENCE statement.

-- Create a descending sequence
CREATE SEQUENCE descending_sequence_example
    START WITH 10
    INCREMENT BY -1;

-- Create a table and use the sequence to populate a column
CREATE TABLE sample_data_desc (
    id SERIAL PRIMARY KEY,
    sequence_number INTEGER
);

-- Insert data into the table using the sequence
INSERT INTO sample_data_desc (sequence_number)
SELECT nextval('descending_sequence_example')
FROM generate_series(1, 10); -- Change 10 to the number of rows you want

-- Query the data to see the descending sequence
SELECT * FROM sample_data_desc;

Output:

 id | sequence_number
----+-----------------
  1 |              10
  2 |               9
  3 |               8
  4 |               7
  5 |               6
  6 |               5
  7 |               4
  8 |               3
  9 |               2
 10 |               1

In this example, the sequence descending_sequence_example starts from 10 and decrements by 1 for each next value. The rest of the process is similar to the ascending sequence example. Adjust the number of rows as needed.

Creating a sequence associated with a table column

To create a sequence associated with a table column in PostgreSQL, you can use the DEFAULT constraint along with the nextval() function of the sequence.

-- Create a sequence
CREATE SEQUENCE example_sequence
    START WITH 1
    INCREMENT BY 1;

-- Create a table with a column associated with the sequence
CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    sequence_column INTEGER DEFAULT nextval('example_sequence'),
    other_column VARCHAR(50)
);

-- Insert data into the table (you can omit the sequence_column)
INSERT INTO example_table (other_column) VALUES ('Data 1'), ('Data 2'), ('Data 3');

-- Query the table to see the data
SELECT * FROM example_table;

Output:

 id | sequence_column | other_column 
----+-----------------+--------------
  1 |               1 | Data 1
  2 |               2 | Data 2
  3 |               3 | Data 3

In this example, we create a sequence named example_sequence. Then, we create a table example_table with a column sequence_column that has a default value assigned using nextval('example_sequence'), which means it will automatically get the next value from the sequence when a new row is inserted if no value is provided for that column. The rest of the process is similar to regular table creation and data insertion.

Deleting sequences

To delete a sequence in PostgreSQL, you can use the DROP SEQUENCE statement followed by the name of the sequence you want to delete.

Syntax:

DROP SEQUENCE [ IF EXISTS ] sequence_name [, ...]   
[ CASCADE | RESTRICT ];

Here's the syntax breakdown:

  • DROP SEQUENCE: This is the command to delete a sequence.
  • IF EXISTS: This is an optional clause that prevents an error from occurring if the sequence does not exist. If the sequence exists, it will be dropped; otherwise, nothing happens.
  • sequence_name [, ...]: This specifies one or more sequence names that you want to delete. Separate multiple sequence names by commas.
  • CASCADE | RESTRICT: These are optional keywords that determine the behavior when dropping sequences that are referenced by other database objects.
  • CASCADE: Automatically drops objects that depend on the sequence and then drops the sequence itself.
  • RESTRICT: Raises an error if any objects depend on the sequence, preventing it from being dropped. This is the default behavior if neither CASCADE nor RESTRICT is specified.

Let's walk through an example using the DROP SEQUENCE statement with various options and examine the output.

-- Create a sequence
CREATE SEQUENCE my_sequence;

-- Attempt to drop a sequence that exists
DROP SEQUENCE IF EXISTS my_sequence;

-- Create a new sequence
CREATE SEQUENCE my_sequence;

-- Create a table and use the sequence to populate a column
CREATE TABLE sample_data (
    id SERIAL PRIMARY KEY,
    value INTEGER DEFAULT nextval('my_sequence')
);

-- Insert data into the table using the sequence
INSERT INTO sample_data (value) VALUES (DEFAULT), (DEFAULT), (DEFAULT);

-- Query the data to see the output
SELECT * FROM sample_data;

After running the code above, the output will be:

 id | value
----+-------
  1 |     1
  2 |     2
  3 |     3

Now, let's delete the sequence using the DROP SEQUENCE statement:

-- Drop the sequence
  DROP SEQUENCE my_sequence CASCADE;

After executing this command, the output will be:

NOTICE:  drop cascades to default for table sample_data column value

This notice indicates that dropping the sequence cascaded to the default value for the column value in the sample_data table. The sequence my_sequence has been successfully deleted. Any further attempts to insert data into the sample_data table using the value column will result in an error since the associated sequence no longer exists.

Advantages of PostgreSQL Sequences

  1. Efficiency: Sequences provide an efficient way to generate unique identifiers without the need for locking or transactions.
  2. Concurrency: Since sequences are independent objects, multiple transactions can generate unique values simultaneously without contention.
  3. Flexibility: Sequences offer flexibility in defining increment values, start and end points, and cycling behavior, allowing customization based on specific requirements.

Best Practices

  1. Use Meaningful Names: Choose descriptive names for sequences to improve code readability and maintainability.
  2. Set Increment Carefully: Ensure the increment value is appropriate for your application's needs to avoid running out of sequence values prematurely.
  3. Monitor Sequence Usage: Regularly monitor sequence usage to identify potential bottlenecks or issues.
  4. Backup Sequences: Include sequences in your database backup and recovery procedures to avoid data loss.

Conclusion

PostgreSQL sequences are powerful tools for generating unique identifiers in databases. Understanding how to create, use, and manage sequences is essential for efficient database design and development. By following best practices and leveraging the flexibility of sequences, you can ensure the integrity and scalability of your PostgreSQL databases.