Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql-create-auto-increment-column-using-serial/ below:

PostgreSQL - Create Auto-increment Column using SERIAL

Last Updated : 15 Jul, 2025

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.

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.

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.

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

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:

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

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

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

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

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4