When creating database tables, we often need a unique ID for every record.
For example, consider a university students table:
| student_id | student_name |
|---|---|
| 1 | Ali |
| 2 | Ahmed |
| 3 | Sara |
| 4 | Usman |
Notice that the student_id increases automatically whenever a new student is added.
PostgreSQL provides Sequences, SERIAL, and IDENTITY columns to generate these numbers automatically.
In this lesson, we will learn how to generate automatic IDs and manage sequences.
A sequence is a special database object that generates a series of numbers.
For example:
1
2
3
4
5
6
7
8
...
A sequence can generate numbers automatically whenever required.
Sequences are commonly used to generate primary key values.
We can create a sequence using the CREATE SEQUENCE command.
Syntax:
CREATE SEQUENCE sequence_name;
Example:
CREATE SEQUENCE student_id_seq;
This creates a sequence named student_id_seq.
The NEXTVAL() function generates the next number from a sequence.
Example:
SELECT NEXTVAL('student_id_seq');
Output:
nextval
---------
1
Execute the command again:
SELECT NEXTVAL('student_id_seq');
Output:
nextval
---------
2
The sequence generates the next number each time NEXTVAL() is called.
Remember: Calling NEXTVAL() advances the sequence, even if the generated number is not inserted into a table.
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 this session was 2, the result will be 2.
Important: CURRVAL() cannot be used in a session until NEXTVAL() has been called for that sequence in the same session.
The SETVAL() function allows us to set a sequence value.
Example:
SELECT SETVAL('student_id_seq', 10);
This sets the sequence’s current value to 10.
The next call to NEXTVAL() normally returns 11.
There is another form:
SELECT SETVAL('student_id_seq', 10, false);
In this case, the next call to NEXTVAL() returns 10.
Difference:
| Command | Next generated value |
|---|---|
SETVAL('student_id_seq', 10) |
11 |
SETVAL('student_id_seq', 10, false) |
10 |
We can customize a sequence by specifying its starting number and increment.
Example:
CREATE SEQUENCE employee_id_seq
START WITH 100
INCREMENT BY 1;
This sequence generates:
100
101
102
103
104
...
We can also increase the sequence by 5:
CREATE SEQUENCE order_id_seq
START WITH 1
INCREMENT BY 5;
Generated values:
1
6
11
16
21
...
| Option | Purpose |
|---|---|
START WITH |
Starting value |
INCREMENT BY |
Difference between generated values |
MINVALUE |
Minimum allowed value |
MAXVALUE |
Maximum allowed value |
CYCLE |
Restart after reaching the limit |
NO CYCLE |
Do not restart after reaching the limit |
CACHE |
Number of sequence values preallocated |
Example:
CREATE SEQUENCE invoice_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
NO CYCLE;
This sequence generates numbers from 1 to 1000 and does not restart automatically.
We can use a sequence to generate primary key values in a table.
First, create a sequence:
CREATE SEQUENCE students_id_seq;
Now create a table:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100)
);
Insert a record using NEXTVAL():
INSERT INTO students
(student_id, student_name)
VALUES
(NEXTVAL('students_id_seq'), 'Ali');
Insert another record:
INSERT INTO students
(student_id, student_name)
VALUES
(NEXTVAL('students_id_seq'), 'Ahmed');
View the records:
SELECT *
FROM students;
The IDs are generated by the sequence.
SERIAL is a convenient PostgreSQL data type shorthand for creating an integer column with an associated sequence and a default value that uses NEXTVAL().
It is commonly used for automatically generated IDs.
Example:
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_name VARCHAR(100)
);
Here:
student_id is an integer column.PRIMARY KEY ensures unique, non-null IDs.When using SERIAL, we do not need to provide the ID manually.
Example:
INSERT INTO students (student_name)
VALUES ('Ali');
Insert more records:
INSERT INTO students (student_name)
VALUES
('Ahmed'),
('Sara'),
('Usman');
Display the records:
SELECT *
FROM students;
Example output:
| student_id | student_name |
|---|---|
| 1 | Ali |
| 2 | Ahmed |
| 3 | Sara |
| 4 | Usman |
The IDs are generated automatically.
PostgreSQL provides three SERIAL types.
| Type | Underlying integer type | Storage size |
|---|---|---|
SMALLSERIAL |
SMALLINT | 2 bytes |
SERIAL |
INTEGER | 4 bytes |
BIGSERIAL |
BIGINT | 8 bytes |
Their approximate positive ranges are:
| Type | Range |
|---|---|
SMALLSERIAL |
1 to 32,767 |
SERIAL |
1 to 2,147,483,647 |
BIGSERIAL |
1 to 9,223,372,036,854,775,807 |
Use BIGSERIAL when a table may require a very large number of IDs.
IDENTITY is the SQL-standard way to define automatically generated values for a column.
It is supported in PostgreSQL 10 and later.
Example:
CREATE TABLE students (
student_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
student_name VARCHAR(100)
);
PostgreSQL automatically generates the student_id value when a new record is inserted.
GENERATED ALWAYS AS IDENTITY tells PostgreSQL to always generate the ID automatically.
Example:
CREATE TABLE employees (
employee_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
employee_name VARCHAR(100)
);
Insert a record:
INSERT INTO employees (employee_name)
VALUES ('Ali');
PostgreSQL generates the employee ID.
If we try to provide an ID manually:
INSERT INTO employees
(employee_id, employee_name)
VALUES
(100, 'Ahmed');
PostgreSQL normally rejects this operation.
To explicitly override the generated value, we can use OVERRIDING SYSTEM VALUE:
INSERT INTO employees
(employee_id, employee_name)
OVERRIDING SYSTEM VALUE
VALUES
(100, 'Ahmed');
GENERATED BY DEFAULT AS IDENTITY generates IDs automatically when we do not provide one.
However, it allows us to provide an ID manually.
Example:
CREATE TABLE teachers (
teacher_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
teacher_name VARCHAR(100)
);
Automatic ID:
INSERT INTO teachers (teacher_name)
VALUES ('Usman');
Manual ID:
INSERT INTO teachers
(teacher_id, teacher_name)
VALUES
(100, 'Ahmed');
Both operations are allowed, provided the supplied ID satisfies the column constraints.
| Feature | ALWAYS | BY DEFAULT |
|---|---|---|
| Automatic ID generation | Yes | Yes |
| Manual ID insertion | Normally not allowed | Allowed |
| Explicit override | Possible using override syntax | Not normally required |
| Typical use | Enforce automatic ID generation | Allow manual IDs when needed |
Both can generate automatic IDs, but they are not exactly the same.
| Feature | SERIAL | IDENTITY |
|---|---|---|
| Standard SQL feature | No | Yes |
| Automatic ID generation | Yes | Yes |
| Uses a sequence | Yes | Yes |
| Manual values | Allowed | Depends on generation mode |
| Recommended for new designs | IDENTITY | IDENTITY |
Recommendation: Prefer GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY when designing new PostgreSQL tables.
PostgreSQL provides a function named pg_get_serial_sequence().
Example:
SELECT pg_get_serial_sequence(
'public.students',
'student_id'
);
This returns the name of the sequence associated with the student_id column, if one exists.
It works with both SERIAL and identity columns.
We can use the following query to view sequence information in the current database:
SELECT
sequence_schema,
sequence_name,
data_type,
start_value,
minimum_value,
maximum_value,
increment
FROM information_schema.sequences
ORDER BY sequence_schema, sequence_name;
This displays sequence metadata available to the current user.
Sometimes we need to restart a sequence.
We can use ALTER SEQUENCE.
Example:
ALTER SEQUENCE students_id_seq
RESTART WITH 1;
The next generated value will be 1.
Important: Resetting a sequence does not change existing table records. If the next generated ID already exists in the table, an insertion may fail due to a primary key conflict.
For an identity column, use:
ALTER TABLE students
ALTER COLUMN student_id
RESTART WITH 1;
Let’s create a student table using an identity column.
Step 1: Create the table
CREATE TABLE university_students (
student_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
student_name VARCHAR(100),
department VARCHAR(100)
);
Step 2: Insert records
INSERT INTO university_students
(student_name, department)
VALUES
('Ali', 'Computer Science'),
('Ahmed', 'Information Technology'),
('Sara', 'Mathematics'),
('Usman', 'Physics');
Step 3: Display records
SELECT *
FROM university_students;
Step 4: Insert another student
INSERT INTO university_students
(student_name, department)
VALUES
('Ayesha', 'Chemistry');
PostgreSQL generates the next available identity value according to the sequence.
GENERATED ALWAYS identity column without the appropriate override.SERIAL and IDENTITY are identical.PRIMARY KEY or UNIQUE constraint is still needed.Why can sequence numbers have gaps?
Sequence values are not rolled back when a transaction fails or is rolled back. Values may also be skipped due to caching or unused generated values.
Therefore, sequences are useful for generating IDs, but they do not guarantee gap-free numbering.
student_seq.NEXTVAL().CURRVAL().SETVAL().employees using SERIAL.BIGSERIAL.GENERATED ALWAYS AS IDENTITY.GENERATED BY DEFAULT AS IDENTITY.SERIAL and IDENTITY by creating two tables.CURRVAL() return?SETVAL()?SERIAL?SERIAL and BIGSERIAL?GENERATED ALWAYS and GENERATED BY DEFAULT?| Command / Feature | Purpose |
|---|---|
CREATE SEQUENCE |
Create a sequence |
NEXTVAL() |
Generate next value |
CURRVAL() |
Get last generated value in the session |
SETVAL() |
Set sequence value |
ALTER SEQUENCE |
Modify a sequence |
SERIAL |
Automatically generated integer ID |
BIGSERIAL |
Automatically generated large integer ID |
SMALLSERIAL |
Automatically generated small integer ID |
IDENTITY |
SQL-standard automatic ID generation |
pg_get_serial_sequence() |
Find an associated sequence |
Next Lesson: PostgreSQL Constraints in Depth
Students will learn practical applications of PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, and DEFAULT constraints, including how to add, modify, and remove constraints using ALTER TABLE.