What Is Serial Data Type in Postgres?


Scott Campbell

What Is Serial Data Type in Postgres?

When working with databases, it is common to have a need for automatically generated unique identifiers for each row in a table. In PostgreSQL, the serial data type provides a convenient way to achieve this.

Understanding the Serial Data Type

The serial data type is not a true data type in PostgreSQL, but rather a shorthand notation or syntax shortcut for creating an auto-incrementing integer column. It is equivalent to defining an integer column and using a sequence to generate unique values automatically.

By using the serial data type, you can simplify your table definitions and let PostgreSQL handle the generation of unique identifiers for you.

The Smallserial and Bigserial Variants

In addition to the serial data type, PostgreSQL also provides two other variants: smallserial and bigserial. These variants behave similarly to the serial data type but use smaller or larger integer ranges respectively.

The smallserial variant uses a range of 1 to 32,767 while the bigserial variant uses a range of 1 to 9223372036854775807. This allows you to choose an appropriate size based on your specific needs and expected number of rows in the table.

Using Serial Columns

To use the serial data type in PostgreSQL, you simply declare a column with the desired name and specify its data type as “serial”. For example:

    id serial PRIMARY KEY,
    username varchar(50) NOT NULL,
    email varchar(255) NOT NULL

In this example, we have created a table called “users” with an “id” column of serial data type. The “id” column will automatically generate a unique integer value for each new row inserted into the table.

When inserting data into a table with a serial column, you do not need to provide a value for that column. PostgreSQL will automatically assign the next available value from the associated sequence.

Retrieving the Generated Value

If you need to retrieve the generated value for a serial column after inserting a new row, you can use the RETURNING clause in your INSERT statement. For example:

INSERT INTO users (username, email)
VALUES ('john_doe', 'john_doe@example.com')

This query will insert a new row into the “users” table with the specified username and email, and then return the generated id value for that row.

Resetting Serial Columns

Sometimes, you may need to reset the serial value of a column back to its initial state. This can be useful when recreating tables or during testing scenarios.

To reset a serial column, you can use the ALTER SEQUENCE command along with the RESTART option. For example:


This command resets the sequence associated with the “id” column of the “users” table back to its initial value of 1.


The serial data type in PostgreSQL provides a convenient way to create auto-incrementing integer columns without having to manually manage sequences. By using this data type, you can simplify your table definitions and let PostgreSQL handle the generation of unique identifiers for you.

Discord Server - Web Server - Private Server - DNS Server - Object-Oriented Programming - Scripting - Data Types - Data Structures

Privacy Policy