PostgreSQL - SERIAL

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', '[email protected]', 30),
    ('Bob', '[email protected]', 35),
    ('Charlie', '[email protected]', 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', '[email protected]', 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', '[email protected]', 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.