# Sequence vs Serial in PostgreSQL

In the last chapter we learned that in order to get an auto-incrementing value in PostgreSQL, we can no longer use INTEGER PRIMARY KEY. We need to use SERIAL instead.

In SQLite, INTEGER PRIMARY KEY was an alias for a unique Row ID that every row has assigned. Therefore, it's not so much that using INTEGER PRIMARY KEY was generating an auto-incrementing number, but that the Row ID automatically increments and we were using that.

In PostgreSQL, using the SERIAL data type (with our without PRIMARY KEY) will give us an auto-incrementing value.

# What is SEQUENCE?

The SERIAL data type is basically a shortcut.

What is does is it creates a SEQUENCE behind the scenes, and sets the column's default value to the next value of the sequence.

A SEQUENCE is a sequence number generator[1]. Essentially, a table with a single row (an integer, with the current value of the sequence starting at 1 by default).

PostgreSQL comes with some functions that, when called on this table, change it and increase the row value.

So you can create a sequence yourself, like this:

CREATE SEQUENCE my_sequence;

Then you can get the current value of the sequence:

SELECT currval('my_sequence');

That'll give you the starting value:

my_sequence
1

You can then change the sequence value using the functions provided:

  • nextval('my_sequence') will generate the next value on the sequence;
  • currval('my_sequence') will give you the current value;
  • lastval() will give you the latest value generated out of all sequences in your database;

More information here: 9.16. Sequence Manipulation Functions (opens new window)

You don't have to know too much about SEQUENCE in PostgreSQL, but it's interesting to know it exists and a little bit about how it works.

You can look at the official documentation to understand the possibilities of SEQUENCE and how you may use it later on in your programming journey. At the moment however, we won't be using SEQUENCE for more than the SERIAL data type.


  1. CREATE SEQUENCE (Official Documentation) (opens new window) ↩ī¸Ž