Learn with Yasir

Share Your Feedback

Student Notes: PostgreSQL Sequences and SERIAL / IDENTITY Columns


Student Notes: PostgreSQL Sequences and SERIAL / IDENTITY Columns

1. Introduction

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.

2. What Is a Sequence?

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.

3. Creating a Sequence

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.

4. Generating Numbers Using NEXTVAL

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.

5. Understanding CURRVAL

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.

6. Understanding SETVAL

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

7. Creating a Sequence with Starting and Increment Values

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
...

8. Important Sequence Options

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.

9. Using a Sequence with a Table

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.

10. What Is SERIAL in PostgreSQL?

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.
  • PostgreSQL creates an associated sequence.
  • The sequence generates values automatically.
  • PRIMARY KEY ensures unique, non-null IDs.

11. Inserting Records into a SERIAL Table

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.

12. Types of SERIAL

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.

13. What Is IDENTITY in PostgreSQL?

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.

14. GENERATED ALWAYS AS IDENTITY

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');

15. GENERATED BY DEFAULT AS IDENTITY

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.

16. Difference Between ALWAYS and BY DEFAULT

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

17. SERIAL vs IDENTITY

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.

18. Finding the Sequence Associated with a Column

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.

19. Viewing Sequence Information

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.

20. Resetting a Sequence

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;

21. Practical Example: University Student Table

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.

22. Common Mistakes

  1. Trying to insert a manual ID into a GENERATED ALWAYS identity column without the appropriate override.
  2. Assuming sequences never have gaps.
  3. Resetting a sequence without checking existing table IDs.
  4. Using a small integer sequence for a table that may need millions of records.
  5. Assuming SERIAL and IDENTITY are identical.
  6. Assuming a sequence automatically guarantees unique values in a table. A 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.

23. Practice Exercises

Beginner Level

  1. Create a sequence named student_seq.
  2. Generate five numbers using NEXTVAL().
  3. Display the current value using CURRVAL().
  4. Set the sequence value to 50 using SETVAL().
  5. Create a sequence starting from 100.

Intermediate Level

  1. Create a table named employees using SERIAL.
  2. Insert five employee records without specifying IDs.
  3. Create a table using BIGSERIAL.
  4. Create a table using GENERATED ALWAYS AS IDENTITY.
  5. Create another table using GENERATED BY DEFAULT AS IDENTITY.
  6. Insert automatic and manual IDs into the appropriate tables.
  7. Find the sequence associated with a table column.

Advanced Level

  1. Create a sequence with a minimum value of 1 and maximum value of 500.
  2. Create a sequence that increments by 10.
  3. Reset a sequence to start from 100.
  4. Compare SERIAL and IDENTITY by creating two tables.
  5. Explain why sequence values may contain gaps.
  6. Create a university student table using an identity primary key and insert ten records.

24. Review Questions

  1. What is a sequence in PostgreSQL?
  2. Which function generates the next sequence value?
  3. What does CURRVAL() return?
  4. What is the purpose of SETVAL()?
  5. What is SERIAL?
  6. What is the difference between SERIAL and BIGSERIAL?
  7. What is an identity column?
  8. What is the difference between GENERATED ALWAYS and GENERATED BY DEFAULT?
  9. Which approach is recommended for new table designs?
  10. Why can sequence values have gaps?
  11. Does a sequence automatically create a primary key?
  12. How can we find the sequence associated with a table column?

25. Quick Revision

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.