Autoincrement in Postgres using SERIAL

Introduction

When you’re creating and managing tables in PostgreSQL, there may be times when you need unique values created on demand for a column. This is especially common for id columns that serve as the primary key for a table. Fortunately, the SERIAL pseudo-type makes it easy to create an auto-incrementing sequence of integers. In this article, we’ll show you how to autoincrement in Postgres using the SERIAL pseudo-type.

Prerequisite

Before proceeding with the examples in this tutorial, make sure that Postgres is installed and configured on your machine.

What is the SERIAL pseudo-type in Postgres?

In Postgres, we can use a sequence to create a series of integers can be used as our table’s primary key column. A PostgreSQL sequence generates a series of unique integers that makes it ideal for use as a primary key.

Here’s the syntax we’d use to create a table that generates a sequence using the SERIAL pseudo-type:

1
2
3
CREATE TABLE <table_name> (
    id SERIAL
);

Let’s take a closer look at this CREATE TABLE statement:

  • First, Postgres creates a sequence object. It generates the next value for the sequence and assigns that as the default value of the column.
  • Since a sequence generates integer values, Postgres adds the implicit constraint NOT NULL to the id column.
  • The id column will be assigned as the owner of the sequence. This allows the sequence to be dropped if the id column or the table itself is deleted.

Shown below is an equivalent statement that runs in the background whenever we use the above command:

1
2
3
4
5
6
7
8
CREATE SEQUENCE TableNameIdSeq;
 
CREATE TABLE <table_name> (
    id INTEGER NOT NULL DEFAULT NEXTVAL('TableNameIdSeq')
);
 
ALTER SEQUENCE TableNameIdSeq
OWNED BY <table_name>.id;

3 Postgres Pseudo-Types

The table shown below provides an overview of the different pseudo-types available in PostgreSQL. In this section, we’ll be focusing on the SERIAL pseudo-type.

NameRangeStorage
SMALL SERIAL1 – 32,7672 bytes
SERIAL1 – 2,147,483,6474 bytes
BIGSERIAL1 – 9,223,372,036,854,775,8078 bytes

SERIAL Example

Keep in mind that when you assign the SERIAL pseudo-type to a column, it doesn’t automatically create an index or designate that column to be the primary key. You’ll still need to specify that the column in question is the PRIMARY KEY in your table definition.

The following statement will create a table named ‘product’ with an id column as the SERIAL column:

1
2
3
4
CREATE TABLE product(
   id SERIAL PRIMARY KEY,
   product_name VARCHAR NOT NULL
);

If you want to assign the default value for your SERIAL column when you insert records, just omit that column name from your INSERT statement or use the DEFAULT keyword in the statement.

Here’s an example where we insert a record while ignoring the id column:

1
2
INSERT INTO product(product_name)
VALUES ('Clover');

Shown below is the alternative version using the DEFAULT keyword:

1
2
INSERT INTO product(id,product_name)
VALUES (DEFAULT, 'KitKat');

These two commands allowed us to insert two records:

1
2
3
4
5
 id | product_name
----+--------------
  1 | Clover
  2 | KitKat
(2 rows)

Postgres allows us to retrieve the SERIAL column’s sequence name in a table. To do this, we use the function pg_get_serial_sequence():

1
pg_get_serial_sequence('<table_name>','<column_name>');

The following example makes use of this function:

1
SELECT currval(pg_get_serial_sequence('product','id'));

Notice that our currval() function allows us to retrieve the most recent value that was generated by the sequence.

The output should look something like the following:

1
2
3
4
 currval
---------
       2
(1 row)

Postgres also allows us to retrieve the current value generated by the sequence when we insert a new row into a target table. We can get this information using the RETURNING clause in our INSERT statement.

Let’s look at an example that includes the RETURNING clause:

1
2
3
INSERT INTO product(product_name)
VALUES ('Kisses')
RETURNING id;

The output will look something like this:

1
2
3
4
5
6
7
 id
----
  3
(1 row)


INSERT 0 1

Conclusion

When you’re assigning a column as the primary key of a PostgreSQL table, you’ll need to make sure that the column will have a unique series of values. In Postgres, it’s easy to generate an auto-incrementing series of integers through the use of a sequence. This article showed you how to auto-increment in Postgres using the SERIAL pseudo-type. With our examples to guide you, you’ll be able to add SERIAL column to your own table definitions.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.