PostgreSQL SERIAL (original) (raw)

Last Updated : 15 Jul, 2025

When working with **PostgreSQL, we need to create tables with unique primary keys. PostgreSQL offers a powerful feature known as the **SERIAL pseudo-type which simplifies generating auto-incrementing sequences for columns.

In this article, we’ll learn about the **PostgreSQL SERIAL pseudo-type by explain how it works and provide practical examples with outputs.

What is PostgreSQL SERIAL?

In **PostgreSQL, the **SERIAL pseudo-type allows you to create an **auto-incrementing integer column, typically used for primary keys.

It automatically generates a sequence of numbers that increase by one for each new row. This feature simplifies the process of creating unique identifiers for each row in a table.

Key Points of PostgreSQL SERIAL:

• The **PostgreSQL SERIAL type creates a sequence and sets it as the default value for the column.
• It adds a **NOT NULL constraint because the sequence values are always non-null.
• When a table or column is dropped, the associated sequence is automatically removed.
• **SERIAL does **not create an index on the column by default, so you may need to explicitly define a **PRIMARY KEY or **UNIQUE constraint.

Types of SERIAL in PostgreSQL

PostgreSQL offers three variations of the **SERIAL pseudo-type, depending on the storage size and value range:

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

SERIAL Types Comparison Table

How to Use SERIAL in PostgreSQL

Syntax for Defining a SERIAL Column

To define a **SERIAL column in PostgreSQL, you simply specify the type as **SERIAL when creating the table. Here's the basic syntax:

CREATE TABLE table_name (
column_name SERIAL
);

Example 1: Creating an Auto-Incremented Column Using SERIAL

Let’s create a table employees with an auto-incrementing emp_id column using the **PostgreSQL SERIAL pseudo-type.

CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
emp_name TEXT NOT NULL,
emp_email VARCHAR(100) NOT NULL,
emp_age SMALLINT
);

In this example:

• The emp_id column is defined as **SERIAL, which automatically generates unique integer values for each new row.
• The **PRIMARY KEY constraint ensures that emp_id remains unique.

Inserting Data into the Table

Now, let’s insert some data into the employees table, omitting the emp_id column since it will be auto-generated.

INSERT INTO employees (emp_name, emp_email, emp_age)
VALUES
('Alice', 'alice@example.com', 30),
('Bob', 'bob@example.com', 35),
('Charlie', 'charlie@example.com', 28);

**Output:

As shown, the **PostgreSQL serial column example automatically assigns sequential values (1, 2, 3, …) to the emp_id column.

Using the DEFAULT Keyword with SERIAL

You can explicitly insert a value into the **SERIAL column using the **DEFAULT keyword. This is useful if you want to ensure that the next available sequence value is used.

Example 2: Inserting Values Using DEFAULT

INSERT INTO employees (emp_id, emp_name, emp_email, emp_age)
VALUES
(DEFAULT, 'David', 'david@example.com', 32);

**Output:

The **DEFAULT keyword inserts the next value in the sequence for emp_id.

Using the RETURNING Clause with SERIAL

The **RETURNING clause is a handy feature that allows you to retrieve the value of the **SERIAL column immediately after inserting a row.

Example 3: Inserting Data and Returning the SERIAL Value

INSERT INTO employees (emp_name, emp_email, emp_age)
VALUES ('Emma', 'emma@example.com', 29)
RETURNING emp_id;

**Output:

The **RETURNING clause retrieves the newly inserted emp_id value (5 in this case).

Getting the Sequence Name of a SERIAL Column

If you want to retrieve the sequence name associated with a **SERIAL column, you can use the **pg_get_serial_sequence() function.

Example 4: Retrieving the Sequence Name

SELECT pg_get_serial_sequence('employees', 'emp_id');

**Output:

This shows that the sequence name for the emp_id column is employees_emp_id_seq.

Getting the Current Value of the SERIAL Sequence

To get the current value generated by the **SERIAL sequence, use the **currval() function.

Example 5: Retrieving the Current Sequence Value

SELECT currval(pg_get_serial_sequence('employees', 'emp_id'));

**Output:

This confirms that the last value generated by the sequence is 5.

SERIAL vs BIGSERIAL in PostgreSQL

Understanding the difference between **SERIAL and **BIGSERIAL is crucial when designing your database schema. **SERIAL is suitable for most applications, but if we need a larger range of values then consider using **BIGSERIAL.

• **SERIAL uses 4 bytes, allowing for a maximum value of 2,147,483,647.
• **BIGSERIAL uses 8 bytes, allowing for a maximum value of 9,223,372,036,854,775,807.

Example 6: Creating a BIGSERIAL Column

CREATE TABLE large_numbers (
big_id BIGSERIAL PRIMARY KEY,
description TEXT
);

This table uses **BIGSERIAL to ensure it can accommodate larger values.

Important Points about SERIAL Type in PostgreSQL

• **SERIAL**in **PostgreSQL automatically generates unique, sequential integers for primary keys.
• It includes SMALLSERIAL, SERIAL, and **BIGSERIAL**with varying sizes and ranges.
• Easily integrates into table creation for streamlined primary key management.
• Enhances database efficiency by automating ID generation and supporting efficient querying and indexing.

Conclusion

The **PostgreSQL SERIAL pseudo-type is an efficient way to handle auto-incrementing columns, especially for primary keys. By using the **SERIAL, **BIGSERIAL, or **SMALLSERIAL types, you can simplify your table designs while ensuring that unique, sequential values are automatically generated for each new row. The examples provided in this article should give you a clear understanding of how to use **SERIAL in PostgreSQL.