PostgreSQL SequencesMany MySQL DBAs have expressed interest in learning about PostgreSQL and this series is a guided tour through the basics. One of the ‘Wow! That is Different!’ things for MySQL DBAs looking at PostgreSQL are sequences.  Sequences can be used as a rough equivalent to MySQL’s AUTO_INCREMENT but that is only part of the story. Sequences are much more than just for auto-incrementing columns.  And the video can be found here.


First, PostgreSQL has a SERIAL data type.  They are defined as SMALLSERIAL, SERIAL, and BIGSERIAL that consume two, four, or eight bytes of memory. And respectively they count up to 32,767 or  2,147,483,647, or 9,223,372,036,854,775,807. 

MySQL has a keyword named SERIAL that is shorthand for BIGINT NOT NULL AUTO_INCREMENT UNIQUE.  

For the first example, we can create a smile table.

Column x gets a default value from a sequence named x_x_seq using the NEXTVAL() function.

Then we can add some simple data.

Note that we did not enter any values for column x and that the server supplied the value from the x_x_seq sequence.  Use \d to see that the table and the sequence were created together.

Sequences by themselves

But sequences can be declared without a table.  In the following example, a sequence is created that will start at 1,001.  To fetch the next value from the sequence and increment that sequence, use the NEXTVAL() function.

There are two ways to check the current count. The first is to query the sequence directly:

Ignore all the columns except for the last_value, at least for now.  Or use the CUIRRVAL() function.


Sequences are not series

A Series, as the name implies, is a series of numbers.  But they can not be referenced like a sequence can to the automatic valuation of a column. But they are very handy for generating data.

Wrap-around sequences

Sequences can start or end at numbers you desire and can also wrap around to start again if you use the CYCLE qualifier.  Here we create a repeating sequence of one or two that wraps around.

The details

Information on the status of a sequence can be had by using \d.

Next episode — Explaining EXPLAIN

Stay tuned!

The past videos for PostgreSQL for MySQL Database Administrators (DBA) can be found here: episode oneepisode two, episode three, and episode four.

Notify of

Inline Feedbacks
View all comments