Drew C King

Lately I’ve been forcing myself to learn PostgreSQL as a replacement for MySQL. In my experimentation, one of the first things I had to figure out how to do was simulate auto-incrementing. In MySQL it’s as simple as sticking the word AUTO_INCREMENT in the column definition, but PostgreSQL doesn’t have that. What it has instead is a totally separate database object called a SEQUENCE, which is essentially just a single-row table used especially to provide for this type of functionality.

For example, in order to create a simple table to store user records, you’d first create the sequence that will be used to generate the unique user IDs:

CREATE SEQUENCE user_seq

Then create the user table and tell its user_id column to use the sequence as the default value:

CREATE TABLE user
(
  user_id INTEGER DEFAULT NEXTVAL('user_seq'),
  email CHARACTER VARYING(50),
  password CHARACTER(40)
)

After this, the first user record that is inserted will get a value of 1, and successive insertions will be incremented appropriately, just like AUTO_INCREMENT.

Because this is such a common practice, PostgreSQL lets you take a little shortcut, via the SERIAL column type (thought technically speaking, it’s not actually a true data type). Using this, you can simply skip the sequence creation, and just specify your table like so:

CREATE TABLE user
(
  user_id SERIAL,
  email CHARACTER VARYING(50),
  password CHARACTER(40)
)

This will create a sequence called user_user_id_seq behind the scenes (i.e., table_column_seq)