Sequence in PostgreSQL
Overview
In PostgreSQL, a Sequence is a special type of object used to generate unique numeric values automatically. It's commonly used for creating primary keys in database tables. Sequences ensure that each value generated is distinct and follows a specific order. It's kind of like a special counter that gives out unique numbers for things in a database and it is a little bit similar to the auto-increment in MySQL.
When you insert a new row into a table that uses a sequence, PostgreSQL automatically generates the next sequential value for the designated column. This helps maintain data integrity and prevents duplicate or conflicting values. Sequences are easy to set up and provide a reliable way to manage unique identifiers for database records.
Sequence in PostgreSQL
In PostgreSQL, a Sequence is a tool that generates a series of unique numeric values. It serves as a mechanism to create a controlled sequence of integers that can be used for various purposes within a database. You can consider it as a specialised counter that produces numbers in a specific order.
Sequences are flexible and customizable. You can define the starting point, the increment between values, and other parameters to suit your requirements. They're commonly used to generate primary key values for database tables, ensuring that each row has a distinct identifier.
Unlike a typical counter, the sequence's order is important. If we change the order of sequence values then it will change the outcomes also. For example, {1, 2, 3} and {3, 2, 1} represent different sequences.
Sequences are schema-bound which means that they are associated with a specific schema in the database. As sequences are not directly linked to any of the particular tables, they can be used to populate the columns that require or need unique identifiers which can enhance the data and integrity of the organisation.
CREATE Sequence in PostgreSQL
The PostgreSQL CREATE SEQUENCE command sets up a sequence generator, which generates unique numeric values which follow specific rules. It creates a supporting table. Creating sequences in PostgreSQL is important for managing sequences, used for automatic unique value generation, especially for primary keys in tables, and maintaining data integrity and uniqueness. Users can define initial values, increments, ranges, and cycling behaviour. It's a fundamental feature for organised and reliable database management.
Syntax
Now, let’s understand the Syntax of how to create a sequence in PostgreSQL:
Below are the parameters and their description that are used in the above syntax to create a sequence in PostgreSQL:
| Parameter | Description |
|---|---|
| sequence_name | Specifies a distinct name for the sequence being created. This name will be used to reference the sequence. |
| INCREMENT BY | Specifies the gap or step between each sequential value generated by the sequence. |
| MINVALUE | It defines the minimum value for the sequence. If it is not set, it defaults to the smallest value of the sequence's data type. |
| MAXVALUE | It sets the maximum value for the sequence. If it is not set, it defaults to the largest value of the sequence's data type. |
| START WTIH | Enhances performance by pre-allocating and storing multiple sequence values in memory. |
| CACHE | Associates the sequence with a specific table column, establishing a link between sequence and column values. |
| OWNED BY | It associates the sequence with a specific table column or disassociates it using NONE. |
| CYCLE | It enables cycling of sequence values (restart from MINVALUE after reaching MAXVALUE). |
| RESTART WITH | It sets a specific value to restart the sequence with. |
| AS | It defines the data type of the sequence; commonly INTEGER or BIGINT. |
Examples
Let’s look at the examples to better understand how the PostgreSQL create sequence works:
Generate a Sequence in Ascending Order
In the below example, we will provide the command which will create a sequence command to generate a new ascending sequence which will start from 1001 with an increment of 1.
Now , let’s see the next value from the sequence with the help of nextval() function.
The above output shows the predefined value which we have set in the create sequence command.
To see the next value we have to use the nextval() function again.
You must be wondering that the output should be 1002 but it is 1011. This is because we have used the cache keyword in the create sequence command. This cache keyword pre-allocates a certain number of sequence values to improve performance. It also stores the pre-allocated values in the memory.
Generate a Sequence in Descending Order
In the below example, we will provide the command which will create a sequence in PostgreSQL to generate a new descending sequence which will start from 1000 with a decrement of 1.
Here the sequence is successfully created.
Now , let’s see the next value from the sequence with the help of nextval() function.
The above output shows the predefined value which we have set in the create sequence command.
To see the next value we have to use the nextval() function again.
Creating Sequence Related to a Table Column
Let's break down the process of creating a sequence related to a table column step by step:
Creating a New Table
Let’s create a table as employees with the create command and we will then insert some values in the table using the insert command.
The employee's table will contain various columns, such as employee_id, first_name, last_name in the database where employee_id is the primary key.
Creating a New Sequence
Now, create a new sequence that will generate unique values for the table's primary key column. Specify the sequence's properties such as the starting value, increment, and caching, if needed. For instance:
Data Insertion
Now insert some sample data in the table. To insert the data into the table, you can use the nextval() function to automatically generate values from the sequence for the primary key column. This ensures that each new row gets a unique employee ID. For example:
Data Retrieving
To retrieve data, you can use regular SQL queries. Now we will use the select command so that we can retrieve the data from the employee's table.
Output:
Now let’s list all the sequences in the database. To do so we have to list all the sequences present in the existing database.
Output:
Removing PostgreSQL Sequence
The PostgreSQL sequence will be automatically removed when the table’s column is deleted or when we drop the table.
Syntax
The syntax for removing a sequence in PostgreSQL is as follows:
Let’s learn about the parameter used in the above syntax in the below table.
| Parameter | Description |
|---|---|
| sequence_name | Specifies the name of the sequence that you intend to remove from the database schema. |
| IF EXISTS | It is an optional clause which prevents an error if there is no match in the specified sequence. |
| CASCADE | This parameter triggers the removal of dependent objects, such as table columns using the sequence as a default. It does so recursively, ensuring all linked objects are eliminated alongside the sequence. |
| RESTRICT | When this parameter is used, it prevents the removal of the sequence if other objects still depend on it. It acts as a safeguard, ensuring that the sequence deletion is only executed when it won't cause issues. |
PostgreSQL Drop Sequence Command
To drop the sequence in the employees table, we will use the DROP Sequence command. However, it's important to note that the employees_id_seq sequence is associated with the employee_id of the employees table. So if we remove the employees table or drop the employees table then it will also drop its associated sequences.
Example
We will use the above-created employee's table to show the drop sequence in Postgresql with the help of the below command:
FAQs
Q. What is a Sequence in PostgreSQL?
A. A sequence in PostgreSQL is a schema-bound object that generates a sequence of unique numeric values, often used for auto-generating primary key values in tables.
Q. How can we create a sequence in PostgreSQL?
A. You can create a sequence in PostgreSQL using the CREATE SEQUENCE command, specifying properties like starting value, increment, and caching.
Q. Can we remove a sequence in PostgreSQL?
A. Yes, you can remove a sequence in PostgreSQL using the DROP SEQUENCE command, effectively deleting the sequence from the database schema.
Conclusion
- A sequence is a special type of object used to generate unique numeric values automatically. It's commonly used for creating primary keys in database tables.
- When you insert a new row into a table that uses a sequence, PostgreSQL automatically generates the next sequential value for the designated column.
- It maintains data integrity and prevents duplicate or conflicting values.
- Sequences are flexible and customizable. You can define the starting point, the increment between values, and other parameters to suit your requirements.
- Users can define initial values, increments, ranges, and cycling behaviour while creating sequences in PostgreSQL.
- Cache keyword pre-allocates a certain number of sequence values to improve the performance. It also stores the pre-allocated values in the memory.