EDUCBA

Home Data Science Data Science Tutorials PostgreSQL Tutorial Sequence in PostgreSQL

Sequence in PostgreSQL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 17, 2023

Sequence in PostgreSQL

 

 

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:
  1. Nextval
  2. Currval
  3. Setval
  4. 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:

Sequence in PostgreSQL output 1

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:

Sequence in PostgreSQL output 2

INSERT INTO Employee_Test ( emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('ABC', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

Sequence in PostgreSQL output 3

INSERT INTO Employee_Test ( emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('PQR', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

Sequence in PostgreSQL output 4

INSERT INTO Employee_Test ( emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');

Output:

output 5

select * from Employee_Test;

Output:

output 6

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:

output 7

  • Drop sequence
DROP SEQUENCE employee_test_seq;

Output:

output 8

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.

  1. PostgreSQL Triggers
  2. Indexes in PostgreSQL
  3. Guide to PostgreSQL FETCH
  4. Complete Guide to Oracle Constraints