Postgres Serial Primary Key

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In the relational database model, the concept of a primary key is an important one to understand. A primary key is a special field in a database table that contains unique values. The PostgreSQL SERIAL pseudo-type can be used to define auto-incremented columns in tables. This pseudo-type is used frequently in the primary key column of a table. In this article, we’ll take a closer look at the Postgres SERIAL primary key and look at some examples of its use.

Prerequisites

Be sure the following prerequisites are in place before you begin this tutorial:

  • You’ll need to have PostgreSQL installed on your computer in order to follow along with the examples in the article.
  • You should also have some basic knowledge of PostgreSQL.

PostgreSQL SERIAL Pseudo-type

There’s a special type of data available in PostgreSQL called a sequence. This type is used to generate unique numeric identifiers in a Postgres database column. It’s most often used in the primary key column of a table.

You can use the following syntax to create a table with a column that contains a sequence:

1
CREATE TABLE TABLE_NAME ( id SERIAL );

Another way to generate a sequence is by using the CREATE SEQUENCE command, which creates a new sequence number generator:

1
CREATE SEQUENCE table_name_id;

NOTE: A sequence can be thought of as a generator that can create IDs for your records. Postgres sequences are similar to UUIDs, which are also commonly used for primary keys.

Let’s try using that new_table_id sequence created earlier in a CREATE TABLE statement:

1
2
3
CREATE TABLE new_table (
    id INT NOT NULL DEFAULT NEXTVAL('new_table_id')
);

Next, let’s reset the sequence for pg_get_serial_sequence by assigning ownership to the table’s id column:

1
ALTER SEQUENCE new_table_id OWNED BY new_table.id;

It’s also possible to drop or delete a sequence– just use the DROP SEQUENCE SQL keyword followed by the sequence name.

PostgreSQL SERIAL pseudo-types

There are three SERIAL pseudo-types available in PostgreSQL:

  • The SMALLSERIAL pseudo-type is a small auto-incremented integer with a storage size of two bytes and a range of one to 32,767.
  • The SERIAL pseudo-type is an auto-incremented integer with a storage size of four bytes and a range of one to 2,147,483,647.
  • The BIGSERIAL pseudo-type is a large auto-incremented integer with a storage size of eight bytes and a range of one to 9,223,372,036,854,775,807.

PostgreSQL SERIAL example

Let’s look at a simple example of a Postgres SERIAL primary key. The following statement creates the alphabet table with the id column as the SERIAL column:

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

When you insert values into this table, you can just ignore the SERIAL column or use the DEFAULT keyword.

For example, the following statement will insert a value into the letter column. A value will be automatically generated in the id column:

1
2
INSERT INTO alphabet (letter)
VALUES ('A');

This INSERT statement works in the same way:

1
2
INSERT INTO alphabet (id, letter)
VALUES (DEFAULT , 'A');

Now that we’ve inserted a record into our table, let’s take a look at the table contents. We’ll execute a SELECT SQL statement to retrieve the record from the table:

1
SELECT * FROM alphabet;

Whether you explicitly specify the id primary key column or not, Postgres will automatically give that initial record an ID of 1:

Screenshot of a Postgres serial primary key example by inserting a record

1
2
3
4
 id | letter
----+--------
 1 | A
(1 ROW)

We can see that the id column does indeed have a value of 1.

In some cases, you might want to get the auto-incremented sequence value automatically when you insert a new record to the table. To do this, use the RETURNING clause in your INSERT statement. The following statement shows how this clause is used:

1
2
3
INSERT INTO alphabet (letter)
    VALUES('b')
    RETURNING id;
1
2
3
4
5
6
 id
----
 2
(1 ROW)

INSERT 0 1

Next, we’ll use the pg_get_serial_sequence() function and pass the sequence name to the currval() function. This will give us the most recent value of the id column. The following statement shows how this works:

1
2
SELECT currval(pg_get_serial_sequence('alphabet', 'id'))
AS id_recent_value;

This statement should return the following result, which contains the most recently inserted record:

1
2
3
4
 id_recent_value
-----------------
        2
(1 ROW)

Conclusion

When you’re working with data in PostgreSQL, you’ll need to know how to create and use primary keys in your tables. Because a primary key column needs to contain unique values, an auto-incremented sequence generated by the SERIAL pseudo-type is a common choice for this type of column. In this article, we provided an overview of the Postgres SERIAL primary key and looked at some examples of how to create and use this type of primary key. Using our examples as a guide, you should be able to implement Postgres SERIAL primary keys in your own database environment.

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.