Updated May 17, 2023
Introduction to Sequence in PostgreSQL
A sequence in PostgreSQL generates unique number identifiers in the database; it is similar but not identical to auto increment in MySQL. We have used create sequence statement to create a new sequence in the PostgreSQL database; it will create a new sequence. If we specify the schema name at the time of sequence creation, then the sequence will be created with the specified schema; otherwise, it is created in the current schema. The temporary sequence is present in a special schema, so we do not need to give the schema name when creating a PostgreSQL temporary sequence.
Syntax
Below is the syntax to create the sequence as follows.
CREATE [TEMPORARY | TEMP] SEQUENCE [IF NOT EXISTS] name (name of sequence) [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}]Below is the parameter description of the above syntax as follows.
- Create – You use a create statement to create a new sequence in PostgreSQL.
- Temporary or temp – You use the ‘temporary’ keyword when creating a temporary sequence in PostgreSQL
- Sequence – You use the ‘sequence’ keyword when creating a sequence in PostgreSQL.
- If it does not exist – After using this keyword, it will not throw an error that a sequence with the same name already exists.
- Name – The name of the sequence to be created.
- Increment – Optionally, you can use this clause when creating a sequence in PostgreSQL. A positive value makes an ascending sequence, and a negative value makes a descending sequence. The default value is 1.
- Min value – It will generate a min value of the sequence. You can use this clause optionally when creating a sequence.
- Max value – It will generate a max value of the sequence. You can optionally use this clause during sequence creation.
- Start – It is an optional clause of sequence. The default starting value of this parameter is the minvalue of ascending sequence and the max value of the descending sequence.
- Cache – You use this operator to determine how many sequence numbers should be pre-allocated and stored in memory for faster access. This is the optional clause of sequence.
- Cycle – This option allows to wrap around when an ascending and descending sequence reaches max value and min value.
- Owned by – This parameter is associated with the specific table and column names.
- Column name – Column name on which we have created a sequence.
How Does Sequence Work in Postgre SQL?
- PostgreSQL provides several functions specifically designed for use with sequences. Below are the most commonly used functions:
- Nextval
- Currval
- Setval
- Lastval
- Nextval function will increment the value of the specified sequence and return the new value as an integer type.
- Currval will return the last returned value from Nextval functions. If we have not used Nextval, it will not return any value.
- Setval in the PostgreSQL sequence will set the current value of sequences to N value.
- The lastval function in PostgreSQL will return the most recently obtained sequence with the next value.
- To create a new sequence generator in PostgreSQL, use the “create sequence” command.
- The sequence name must be distinct from any other name of the sequence, table, view, or foreign table in PostgreSQL.
- If we have given a schema name at the time of sequence creation, then the sequence will be created with the specified schema. Otherwise, it will be created in the current schema.
- After sequence creation, we have used a function like Nextval, Lastval, Currval, and Setval to operate on PostgreSQL sequences.
- Sequences in PostgreSQL are essential to generate a unique number identifies in the database.
- It is an object that will use to generate a sequence number automatically.
- We can create the number of sequences as we like, but we need to define each sequence-unique name at the time of creation.
- PostgreSQL generates numeric identifiers using a unique object known as a sequence. This object is mainly used to generate artificial primary keys within PostgreSQL.
- The sequence in PostgreSQL is similar but not identical to auto increment in MySQL.
- The sequence in PostgreSQL is most commonly used with the serial pseudotype. The serial is a special data type in PostgreSQL that encodes information as follows.
- It utilizes the term “serial” to generate a value for a column using a sequence.
- Serial in PostgreSQL will create a new sequence object and set the column’s default value to the next value produced by the sequences.
- The sequence always produces a non-null value; it will add the not null constraints to the column.
- After analyzing the data, we concluded that the sequence is exclusively used to generate new values in a serial column table.
- The sequence will automatically be dropped if you drop the serial column in the table.
Examples of implementing Sequence in PostgreSQL
Below is an example of create a sequence and how it works while inserting data into the table.
1. Create a sequence name as employee_test_seq.
CREATE SEQUENCE employee_test_seq;Output:
2. Create a table and use the employee_test_seq sequence while inserting data into the table.
CREATE TABLE Employee_Test ( emp_id INT DEFAULT NEXTVAL('employee_test_seq'), emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL );Output:
INSERT INTO Employee_Test ( emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('ABC', 'Pune', '1234567890', 20000, '01-01-2020');Output:
INSERT INTO Employee_Test ( emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('PQR', 'Pune', '1234567890', 20000, '01-01-2020');Output:
INSERT INTO Employee_Test ( emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');Output:
select * from Employee_Test;Output:
Drop Sequence in Postgre SQL
The below query shows how to delete sequences.
- Alter the table column to delete the sequence.
ALTER TABLE Employee_Test ALTER COLUMN emp_id SET DEFAULT NULL;
\d+ Employee_Test;Output:
- Drop sequence
DROP SEQUENCE employee_test_seq;Output:
Conclusion- Sequence in PostgreSQL
Sequences are most important to generate a unique identifier number for the database. It has several functions like Nextval, Setval, Lastval, and Currval, designed for sequences. We also used create sequence statements to create a new sequence in the database.
Recommended Articles
We hope that this EDUCBA information on “Sequence in PostgreSQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
