Each row in a data table, should be identified, in most cases, using a numeric identifier is the easiest way to achieve it.

TL;DR: generated always as identity

Creating an Identity Column

When you say auto incrementing identitiy column, every DBA think about same, “sequences”. In PostgreSQL you can define a sequence and mark it to be used by a numeric column.

PostgreSQL also offers another way in table definition. You can use generate always as identity statement to define a auto increment.

You can also use serial keyword, but in newer versions, PostgreSQL implemented generate always as identity statement for SQL complience. You can take serial keywork as an old and discouraged usage.

Creating Auto Increment With Sequences

To define auto incrementing identity column you need to have a defined sequence. You can assing id column of you table, DEFAULT nextval(‘sequence_name’), then alter the sequence to be owned by id field of the table.

Let’s see it works:

postgres=# CREATE SEQUENCE testseq;
CREATE SEQUENCE
postgres=# CREATE TABLE users (id INT NOT NULL DEFAULT nextval('testseq'), name VARCHAR(10));
CREATE TABLE
postgres=# ALTER SEQUENCE testseq OWNED BY users.id;
ALTER SEQUENCE
postgres=# INSERT INTO users (name) VALUES ('name1');
INSERT 0 1
postgres=# INSERT INTO users (name) VALUES ('name2');
INSERT 0 1
postgres=# INSERT INTO users (name) VALUES ('name3');
INSERT 0 1
postgres=# SELECT * FROM users;
 id | name
----+-------
  1 | name1
  2 | name2
  3 | name3
(3 rows)

As you can see, you need to match sequence and table.

Creating Auto Increment with Serial

Usage of serial is an old way therefore i don’t suggest you to use serial with your new database designs.

postgres=# CREATE TABLE users (id SERIAL, name VARCHAR(10));
CREATE TABLE
postgres=# INSERT INTO users (name) VALUES ('name1');
INSERT 0 1
postgres=# INSERT INTO users (name) VALUES ('name2');
INSERT 0 1
postgres=# INSERT INTO users (name) VALUES ('name3');
INSERT 0 1
postgres=# SELECT * FROM users;
 id | name
----+-------
  1 | name1
  2 | name2
  3 | name3
(3 rows)

This is how you can implement auto increment using serial in PostgreSQL.

Auto Generating as Identity

This is a shortcut for defining sequences. Actually, this is a new method for serials, more SQL syntax compliant.

postgres=# CREATE TABLE users (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name VARCHAR(10));
CREATE TABLE
postgres=# INSERT INTO users (name) VALUES ('name1');
INSERT 0 1
postgres=# INSERT INTO users (name) VALUES ('name2');
INSERT 0 1
postgres=# INSERT INTO users (name) VALUES ('name3');
INSERT 0 1
postgres=# SELECT * FROM users;
 id | name
----+-------
  1 | name1
  2 | name2
  3 | name3
(3 rows)

You can use this method by default, because of it’s simple nature.

If you a question, or something to add, please drop a comment below, i’ll try my best to get back to you soon.