PostgreSQL – Create Auto-increment Column using SERIAL

In PostgreSQL, the SERIAL data type is a convenient way to create auto-increment columns, commonly used for primary keys. This feature simplifies the process of generating unique identifiers automatically without requiring additional manual input. By using SERIAL, BIGSERIAL, or SMALLSERIAL, PostgreSQL assigns sequential values to our table, ensuring data integrity and easy referencing.

In this article we will explain how sequences function, the usage of the SERIAL pseudo-type, and practical examples to illustrate how PostgreSQL handles sequences effectively.

What is a Sequence in PostgreSQL?

A sequence is a database object specifically designed to generate a series of unique integers. These integers are particularly useful for automatically populating primary key columns in tables. When defining a primary key column, PostgreSQL provides a convenient shortcut through the SERIAL pseudo-type, simplifying both sequence creation and management.

How Sequences Work

When a sequence is created, it can automatically generate the next integer whenever a new row is inserted into a table. This feature is important to ensure that each row has a unique identifier, which prevents potential conflicts or duplications.

What is the SERIAL Pseudo-Type?

The SERIAL pseudo-type is a convenient way to create auto-incrementing primary keys. PostgreSQL offers three variants of the SERIAL type:

• SMALLSERIAL: Uses 2 bytes and stores values from 1 to 32,767.
• SERIAL: Uses 4 bytes and stores values from 1 to 2,147,483,647.
• BIGSERIAL: Uses 8 bytes and stores values from 1 to 9,223,372,036,854,775,807.

When using the SERIAL type, PostgreSQL automatically handles sequence creation, setting the next integer as the default value for the column.

Syntax:

CREATE TABLE table_name(
    id SERIAL
);

How PostgreSQL Manages SERIAL Columns Internally

In the above syntax by setting the SERIAL pseudo-type to the id column, PostgreSQL performs the following:

• Creates a Sequence Object: A sequence object is created to manage the integer generation.
• Sets Default Value: The next value generated by the sequence is set as the default value for the column.
• Adds NOT NULL Constraint: Add a NOT NULL constraint to the id column because a sequence always generates an integer, which is a non-null value. .
• Sequence Ownership: Assign the owner of the sequence to the id column; as a result, the sequence object is deleted when the id column or table is dropped.

The above syntax is equivalent to the below statement:

CREATE SEQUENCE table_name_id_seq;
CREATE TABLE table_name (
    id integer NOT NULL DEFAULT nextval('table_name_id_seq')
);
ALTER SEQUENCE table_name_id_seq
OWNED BY table_name.id;

PostgreSQL provides three serial pseudo-types SMALLSERIAL, SERIAL, and BIGSERIAL with the following characteristics:

Create Auto-increment Column using SERIAL Examples

Let us look at some of the examples to better understand the concept of Creating Auto increment Column Using SERIAL in PostgreSQL.

Example 1: Creating a Table with SERIAL

In this example, we create a table named animals with an id column defined as a SERIAL type, which serves as the primary key. The SERIAL type automatically generates a unique integer value for each row added to the table. After creating the table, we insert two rows into the animals table with the names ‘Dog’ and ‘Cat’.

Query:

CREATE TABLE animals(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

INSERT INTO animals(name) 
VALUES('Dog');
INSERT INTO animals(name) 
VALUES('Cat');
SELECT * FROM animals;

Output

SERIAL Example1

Example 2: Inserting Data and Returning the Generated ID

In this example, we insert a new row into the animals table with the name ‘Tiger’. By using the RETURNING clause, we can retrieve the automatically generated id value for this new entry. This feature is useful for confirming the unique identifier assigned to the newly inserted row.

Query:

INSERT INTO animals(name) 
VALUES('Tiger')
RETURNING id; 

Output

SERIAL Example2

Best Practices When Using SERIAL in PostgreSQL

• While sequences are unique, they should not be used as meaningful data. Gaps and resets can occur.
• If our system requires unique IDs across multiple databases or instances, using UUIDs may be a better option than relying on SERIAL.
• For extremely large datasets, the choice between SERIAL and BIGSERIAL can impact performance. Always benchmark according to our workload.

Conclusion

PostgreSQL’s SERIAL pseudo-type simplifies the process of creating auto-incrementing primary keys for database tables. By Using sequences, developers can efficiently generate unique identifiers that enhance data integrity and streamline database management. Whether we are creating new tables or modifying existing ones, understanding how to use SERIAL can significantly improve our PostgreSQL experience.

FAQs

How to set auto increment in PostgreSQL?

In PostgreSQL, you can set auto-increment using the SERIAL or BIGSERIAL data type. These types automatically increment a column for each new row inserted.

How to auto increment primary key serial in PostgreSQL?

Define the primary key as SERIAL during table creation. For example:
id SERIAL PRIMARY KEY

How to generate auto increment serial number in SQL?

In PostgreSQL, use the SERIAL or BIGSERIAL data type to auto-generate sequential numbers for a column. It automatically increments with each new record inserted.

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• postgreSQL-managing-table