Using PostgreSQL SERIAL to Create Auto-increment Column

PostgreSQL Serial

Summary: in this tutorial,  you will learn about the PostgreSQL SERIAL pseudo-type and how to use the SERIAL pseudo-type to define auto-increment columns in tables.

Introduction to the PostgreSQL SERIAL pseudo-type

In PostgreSQL, a sequence is a special kind of database object that generates a sequence of integers. A sequence is often used as the primary key column in a table.

When creating a new table, the sequence can be created through the SERIAL pseudo-type as follows:

CREATE TABLE table_name(
    id SERIAL
);Code language: SQL (Structured Query Language) (sql)

By assigning the SERIAL pseudo-type to the id column, PostgreSQL performs the following:

  • First, create a sequence object and set the next value generated by the sequence as the default value for the column.
  • Second, add a NOT NULL constraint to the id column because a sequence always generates an integer, which is a non-null value.
  • Third, 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

Behind the scenes, the following statement:

CREATE TABLE table_name(
    id SERIAL
);Code language: SQL (Structured Query Language) (sql)

is equivalent to the following statements:

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;Code language: SQL (Structured Query Language) (sql)

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

NameStorage SizeRange
SMALLSERIAL2 bytes1 to 32,767
SERIAL4 bytes1 to 2,147,483,647
BIGSERIAL8 bytes1 to 9,223,372,036,854,775,807

PostgreSQL SERIAL examples

Let’s take some examples of using the SERIAL columns.

1) Basic PostgreSQL SERIAL example

It is important to note that the SERIAL does not implicitly create an index on the column or make the column the primary key column. However, this can be done easily by specifying the PRIMARY KEY constraint for the SERIAL column.

The following statement creates the fruits table with the id column as the SERIAL column:

CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);Code language: SQL (Structured Query Language) (sql)

To assign a default value to a serial column when inserting a row into the table, you ignore the column name or use the DEFAULT keyword in the INSERT statement. For example:

INSERT INTO fruits(name) 
VALUES('Orange');Code language: SQL (Structured Query Language) (sql)

Or

INSERT INTO fruits(id,name) 
VALUES(DEFAULT,'Apple');Code language: SQL (Structured Query Language) (sql)

PostgreSQL inserted two rows into the fruits table with the values for the id column are 1 and 2.

SELECT * FROM fruits;Code language: SQL (Structured Query Language) (sql)
 id |  name
----+--------
  1 | Apple
  2 | Orange
(2 rows)Code language: SQL (Structured Query Language) (sql)

2) Getting the sequence name

To get the sequence name of a SERIAL column in a table, you use the pg_get_serial_sequence() function as follows:

pg_get_serial_sequence('table_name','column_name')Code language: SQL (Structured Query Language) (sql)

You can pass a sequence name to the  currval() function to get the recent value generated by the sequence. For example, the following statement returns the recent value generated by the fruits_id_seq object:

SELECT currval(pg_get_serial_sequence('fruits', 'id'));Code language: SQL (Structured Query Language) (sql)
currval
---------
2
(1 row)Code language: SQL (Structured Query Language) (sql)

3) Retrieving the generated value

If you want to get the value generated by the sequence when you insert a new row into the table, you use the RETURNING id clause in the INSERT statement.

The following statement inserts a new row into the fruits table and returns the value generated for the id column.

INSERT INTO fruits(name) 
VALUES('Banana')
RETURNING id;Code language: SQL (Structured Query Language) (sql)
id
----
3
(1 row)Code language: SQL (Structured Query Language) (sql)

The sequence generator operation is not transaction-safe. It means that if two concurrent database connections attempt to get the next value from a sequence, each client will get a different value.

If one client rolls back the transaction, the sequence number of that client will be unused, creating a gap in the sequence.

4) Adding a serial column to an existing table

First, create a new table called baskets without a primary key column:

CREATE TABLE baskets(
    name VARCHAR(255) NOT NULL
);Code language: PHP (php)

Second, add a SERIAL column to the baskets table:

ALTER TABLE baskets
ADD COLUMN id SERIAL PRIMARY KEY;

Third, describe the table baskets to verify the change:

\d baskets

Output:

                                    Table "public.baskets"
 Column |          Type          | Collation | Nullable |               Default
--------+------------------------+-----------+----------+-------------------------------------
 name   | character varying(255) |           | not null |
 id     | integer                |           | not null | nextval('baskets_id_seq'::regclass)
Indexes:
    "baskets_pkey" PRIMARY KEY, btree (id)Code language: PHP (php)

Summary

  • Use the PostgreSQL pseudo-type SERIAL to create an auto-increment column for a table.
Was this tutorial helpful ?