In MySQL and MariaDB we can use the AUTO_INCREMENT keyword to create an automatically incrementing column in a table. In SQLite, we’d use the AUTOINCREMENT keyword. And in SQL Server we can use the IDENTITY property. Some of those DBMSs also allow us to create sequence objects, which provide us with more options for creating an auto-increment type column.

When it comes to PostgreSQL, there are a few ways to create an auto-incrementing column. Below are three options for creating an AUTO_INCREMENT style column in Postgres.

Option 1: IDENTITY Column

Our first option is to make the column an identity column. This option was added in PostgreSQL 10 in order to provide a SQL standard compliant method for adding an auto-incrementing column.

Here’s an example of creating a table with an identity column:

CREATE TABLE Idiots (
    IdiotId int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    IdiotName VARCHAR
);

This creates a sequence object that will be used whenever a new value needs to be generated.

It’s also possible to specify various options for the sequence, such as its starting value, its increment, etc.

See Create an IDENTITY Column in PostgreSQL for more info and examples of how this works.

Option 2: SERIAL Column

Another option is to use the serial type to create an auto increment column.

Here’s an example of creating a table with a serial column:

CREATE TABLE Idiots (
    IdiotId serial PRIMARY KEY,
    IdiotName varchar
);

I also specified that the column is a primary key column. This is not automatic. If we don’t specify UNIQUE or PRIMARY KEY, the column doesn’t automatically take on any of these characteristics. That said, prior to PostgreSQL 7.3, specifying serial did automatically imply UNIQUE, but this is not the case with subsequent versions of Postgres.

We can alternatively use the smallserial and bigserial types, depending on the range we expect the column will need to hold.

See How SERIAL Works in PostgreSQL for more information and examples of how this works.

Option 3: Create a Sequence

Another option is to create our own sequence and use it to generate incrementing values for our column.

Example:

CREATE SEQUENCE idiots_idiotid_seq AS integer;
CREATE TABLE idiots (
    IdiotId integer NOT NULL DEFAULT nextval('idiots_idiotid_seq'),
    IdiotName VARCHAR
);
ALTER SEQUENCE idiots_idiotid_seq OWNED BY Idiots.IdiotId;

That code is the equivalent of the serial option. Simply specifying serial does the job, but if we want to explicitly create our own sequence for whatever reason, this is how we can do it. The nextval() function generates the next value from the sequence.

By specifying OWNED BY as the table and column, we can ensure that the sequence is dropped if the column or table is dropped.