Introduction to the PostgreSQL SERIAL pseudo-type

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

Whencreating a new table, the sequence can be created through theSERIAL pseudo-type as follows:

CREATE TABLE table_name(    id SERIAL);

By assigning theSERIAL pseudo-type to theid 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 aNOT NULL constraint to theid column because a sequence always generates an integer, which is a non-null value.
• Third, assign the owner of the sequence to theid column; as a result, the sequence object is deleted when theid column or table is dropped

Behind the scenes, the following statement:

CREATE TABLE table_name(    idSERIAL);

is equivalent to the following statements:

CREATE SEQUENCE table_name_id_seq;CREATE TABLE table_name (    idinteger NOT NULL DEFAULT nextval('table_name_id_seq'));ALTER SEQUENCE table_name_id_seqOWNEDBY table_name.id;

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

PostgreSQL SERIAL examples

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

1) Basic PostgreSQL SERIAL example

It is important to note that theSERIAL does not implicitlycreate an index on the column or make the column theprimary key column. However, this can be done easily by specifying thePRIMARY KEY constraint for theSERIAL column.

The following statement creates thefruits table with theid column as theSERIAL column:

CREATE TABLE fruits(   idSERIAL PRIMARY KEY,   name VARCHAR NOT NULL);

To assign a default value to a serial column wheninserting a row into the table, you ignore the column name or use theDEFAULT keyword in theINSERT statement. For example:

INSERT INTO fruits(name)VALUES('Orange');

Or

INSERT INTO fruits(id,name)VALUES(DEFAULT,'Apple');

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

SELECT * FROM fruits;

id |  name----+--------  1 | Apple  2 | Orange(2 rows)

2) Getting the sequence name

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

pg_get_serial_sequence('table_name','column_name')

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 thefruits_id_seq object:

SELECT currval(pg_get_serial_sequence('fruits','id'));

currval---------2(1 row)

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 theRETURNING id clause in theINSERT statement.

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

INSERT INTO fruits(name)VALUES('Banana')RETURNING id;

id----3(1 row)

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 calledbaskets without a primary key column:

CREATE TABLE baskets(    name VARCHAR(255)NOT NULL);

Second, add aSERIAL column to thebaskets table:

ALTER TABLE basketsADD COLUMN id SERIAL PRIMARY KEY;

Third, describe the tablebaskets 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)

Summary

• Use the PostgreSQL pseudo-typeSERIAL to create an auto-increment column for a table.

Last updated onFebruary 1, 2024

On this page

• Introduction to the PostgreSQL SERIAL pseudo-type
• PostgreSQL SERIAL examples
• Summary