A Sequence is a database object in PostgreSQL that generates a series of numeric values automatically.
Sequences are commonly used to generate unique identification numbers for table records.
For example, consider a university database containing student records.
| student_id | student_name |
|---|---|
| 1 | Ali |
| 2 | Ahmed |
| 3 | Sara |
| 4 | Ayesha |
| 5 | Usman |
Instead of manually entering a student ID for every new student, we can use a Sequence to generate these numbers automatically.
Sequences are useful when:
PostgreSQL sequences are independent database objects. They can be used by multiple tables or columns if required.
Use the CREATE SEQUENCE command.
Syntax:
CREATE SEQUENCE sequence_name;
Example:
CREATE SEQUENCE student_id_seq;
This creates a sequence named student_id_seq.
By default, a sequence starts at 1 and increases by 1.
The NEXTVAL() function generates the next value from a sequence.
Example:
SELECT NEXTVAL('student_id_seq');
Expected result:
nextval
---------
1
Execute the same command again:
SELECT NEXTVAL('student_id_seq');
Expected result:
nextval
---------
2
Execute it again:
SELECT NEXTVAL('student_id_seq');
Expected result:
nextval
---------
3
Each call generates a new sequence value.
Remember: NEXTVAL() advances the sequence. It does not simply read the current value.
The CURRVAL() function returns the most recently generated value of a sequence in the current database session.
Example:
SELECT CURRVAL('student_id_seq');
If the last value generated in the current session was 3, the result will be:
currval
---------
3
Important: You must call NEXTVAL() at least once for that sequence in the current session before using CURRVAL().
We can specify the starting value and increment when creating a sequence.
Syntax:
CREATE SEQUENCE sequence_name
START WITH starting_value
INCREMENT BY increment_value;
Example:
CREATE SEQUENCE student_id_seq2
START WITH 100
INCREMENT BY 5;
Generate values:
SELECT NEXTVAL('student_id_seq2');
Expected values from successive calls:
| Call | Generated Value |
|---|---|
| 1 | 100 |
| 2 | 105 |
| 3 | 110 |
| 4 | 115 |
| 5 | 120 |
We can also specify the minimum and maximum values.
CREATE SEQUENCE invoice_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000;
This sequence generates numbers from 1 to 1000.
After reaching the maximum value, PostgreSQL reports an error when another value is requested, because cycling is disabled by default.
Now let’s create a student table and use a sequence to generate student IDs.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100),
email VARCHAR(100)
);
CREATE SEQUENCE students_id_seq
START WITH 1
INCREMENT BY 1;
INSERT INTO students (
student_id,
student_name,
email
)
VALUES (
NEXTVAL('students_id_seq'),
'Ali',
'ali@example.com'
);
Insert another record:
INSERT INTO students (
student_id,
student_name,
email
)
VALUES (
NEXTVAL('students_id_seq'),
'Ahmed',
'ahmed@example.com'
);
SELECT *
FROM students;
Expected result:
| student_id | student_name | |
|---|---|---|
| 1 | Ali | ali\@example.com |
| 2 | Ahmed | ahmed\@example.com |
The sequence automatically generates a new ID for each inserted record.
PostgreSQL provides the SERIAL data type as a convenient way to create an integer column that uses an automatically generated sequence.
Instead of creating a sequence separately, we can write:
CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(100)
);
Now insert records without specifying department_id.
INSERT INTO departments (department_name)
VALUES ('Computer Science');
Insert another record:
INSERT INTO departments (department_name)
VALUES ('Information Technology');
Retrieve the records:
SELECT *
FROM departments;
PostgreSQL automatically generates the department IDs.
Important: SERIAL is PostgreSQL-specific shorthand. It creates an integer column with a sequence-backed default; it is not a true SQL data type.
PostgreSQL also supports identity columns, which provide a standard SQL approach to automatic number generation.
There are two identity options.
CREATE TABLE teachers (
teacher_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
teacher_name VARCHAR(100)
);
Insert records:
INSERT INTO teachers (teacher_name)
VALUES ('Muhammad Ali');
PostgreSQL automatically generates the teacher_id.
With GENERATED ALWAYS, PostgreSQL normally does not allow manually supplying an ID unless OVERRIDING SYSTEM VALUE is specified.
CREATE TABLE courses (
course_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
course_name VARCHAR(100)
);
Insert a record without specifying the ID:
INSERT INTO courses (course_name)
VALUES ('Database Systems');
We can also manually supply an ID:
INSERT INTO courses (course_id, course_name)
VALUES (100, 'Programming Fundamentals');
Difference:
| ALWAYS | BY DEFAULT |
|---|---|
| Automatically generates values | Automatically generates values |
| Normally rejects manually supplied IDs | Allows manually supplied IDs |
| Can override using special SQL syntax | Manual value takes precedence |
| Feature | SEQUENCE | SERIAL | IDENTITY |
|---|---|---|---|
| Type | Independent database object | Column shorthand | Column property |
| Automatic value generation | Requires configuration | Yes | Yes |
| Can be used with tables | Yes | Yes | Yes |
| Requires a separate sequence declaration | Yes, if created manually | No | No |
| Standard SQL approach | No | No | Yes |
Teaching tip: For beginners, first explain Sequence, then demonstrate SERIAL, and finally introduce IDENTITY.
We can modify an existing sequence using ALTER SEQUENCE.
For example:
ALTER SEQUENCE student_id_seq
INCREMENT BY 2;
Now the sequence increases by 2 instead of 1.
We can also restart a sequence:
ALTER SEQUENCE student_id_seq
RESTART WITH 100;
The next call to NEXTVAL() will generate 100.
Caution: Restarting a sequence that is being used by an existing table can generate duplicate IDs if the new values overlap with existing records.
Use DROP SEQUENCE to delete a sequence.
DROP SEQUENCE student_id_seq;
To avoid an error if the sequence does not exist:
DROP SEQUENCE IF EXISTS student_id_seq;
| Function | Purpose |
|---|---|
NEXTVAL() |
Generates the next sequence value |
CURRVAL() |
Returns the last generated value in the current session |
SETVAL() |
Sets the current sequence value |
Example of SETVAL():
SELECT SETVAL('student_id_seq', 100);
The next call to NEXTVAL() normally returns 101.
To make the next call return 100:
SELECT SETVAL('student_id_seq', 100, false);
PRIMARY KEY or UNIQUE constraint.Complete the following exercises in PostgreSQL.
Exercise 1: Create a sequence named employee_id_seq.
Exercise 2: Generate five values using NEXTVAL().
Exercise 3: Use CURRVAL() to retrieve the last generated value.
Exercise 4: Create a sequence starting from 500 and increasing by 10.
Exercise 5: Create a table named employees and use a sequence to generate employee IDs.
Exercise 6: Create a table named students_serial using the SERIAL data type.
Exercise 7: Create a table named students_identity using GENERATED ALWAYS AS IDENTITY.
Exercise 8: Create a table using GENERATED BY DEFAULT AS IDENTITY.
Exercise 9: Change the increment value of an existing sequence.
Exercise 10: Restart a sequence from 1000.
NEXTVAL()?NEXTVAL() and CURRVAL()?SETVAL()?SERIAL and IDENTITY?GENERATED ALWAYS and GENERATED BY DEFAULT?| Task | SQL Command |
|---|---|
| Create sequence | CREATE SEQUENCE |
| Generate next value | NEXTVAL() |
| Get current value | CURRVAL() |
| Set sequence value | SETVAL() |
| Modify sequence | ALTER SEQUENCE |
| Restart sequence | RESTART WITH |
| Delete sequence | DROP SEQUENCE |
| Automatic integer ID | SERIAL |
| Identity column | GENERATED ALWAYS AS IDENTITY |