PostgreSQL: Creating Auto-Incrementing Columns with SERIAL

Create auto-incrementing columns in PostgreSQL using the SERIAL pseudo-type. This guide explains how SERIAL simplifies primary key generation.

Creating Auto-Incrementing Columns in PostgreSQL with `SERIAL`

Understanding PostgreSQL `SERIAL`

In PostgreSQL, `SERIAL` is a pseudo-type used to create auto-incrementing integer columns in tables. Auto-incrementing columns automatically generate unique integer values whenever a new row is added. This is commonly used for primary keys, providing a simple way to ensure that each row in the table has a unique identifier.

How `SERIAL` Works

When you define a column as `SERIAL`, PostgreSQL does the following:

1. Creates a sequence object (a database object that generates a sequence of numbers).
2. Sets the column's default value to the next value generated by the sequence.
3. Automatically adds a `NOT NULL` constraint (because a sequence always generates a non-null value).
4. The sequence object is automatically dropped when you drop the table or the column itself.

You can also explicitly create a sequence and then link it to your column. This approach gives you more control over the sequence's behavior. The two approaches (implicit `SERIAL` and explicit sequence creation) produce identical results.

Types of `SERIAL`

PostgreSQL offers three types of `SERIAL`:

`SERIAL` Syntax

The syntax for creating a `SERIAL` column is:

column_name SERIAL;

It's common to also add a `PRIMARY KEY` constraint to the `SERIAL` column.

Examples: Using `SERIAL`

These examples demonstrate using `SERIAL` to create auto-incrementing columns. The examples show creating tables with `SERIAL` columns, inserting data (omitting the `SERIAL` column or using `DEFAULT`), and retrieving data. Note that the `SERIAL` column automatically increments.

Example 1: Creating the `Cars` Table

CREATE TABLE Cars (
    Car_id SERIAL PRIMARY KEY,
    Car_name VARCHAR(255) NOT NULL,
    Car_model VARCHAR(255) NOT NULL
);

Example 2: Inserting Data into the `Cars` Table

INSERT INTO Cars (Car_name, Car_model) VALUES ('Porsche', '911 Carrera');
INSERT INTO Cars (Car_id, Car_name, Car_model) VALUES (DEFAULT, 'Audi', 'A8');

Retrieving the Sequence Name

The `pg_get_serial_sequence()` function retrieves a `SERIAL` column's associated sequence name.

SELECT pg_get_serial_sequence('Cars', 'car_id');

Retrieving the Current Sequence Value

The `currval()` function gets the last value generated by a sequence.

SELECT currval(pg_get_serial_sequence('Cars', 'car_id'));

Returning Generated ID with `RETURNING`

The `RETURNING` clause in an `INSERT` statement retrieves the newly generated ID.

INSERT INTO Cars (Car_name, Car_model) VALUES ('Jaguar', 'XK') RETURNING car_id;

Important Considerations

Sequences are not transaction-safe; concurrent access might lead to gaps in the sequence. A rollback can also leave gaps in the generated sequence. The same applies for other `SERIAL` types (`SMALLSERIAL`, `BIGSERIAL`).

Conclusion

PostgreSQL's `SERIAL` pseudo-type simplifies the creation of auto-incrementing columns, which is a valuable feature for managing primary keys and other unique identifiers in your tables. Understanding how `SERIAL` works and its variations enables efficient database design.