See How SERIAL Works in PostgreSQL for more information and examples of how this works. We can alternatively use the smallserial and bigserial types, depending on the range we expect the column will need to hold. That said, prior to PostgreSQL 7.3, specifying serial did automatically imply UNIQUE, but this is not the case with subsequent versions of Postgres. If we don’t specify UNIQUE or PRIMARY KEY, the column doesn’t automatically take on any of these characteristics. I also specified that the column is a primary key column.

Here’s an example of creating a table with a serial column: CREATE TABLE Idiots ( Option 2: SERIAL ColumnĪnother option is to use the serial type to create an auto increment column. See Create an IDENTITY Column in PostgreSQL for more info and examples of how this works. It’s also possible to specify various options for the sequence, such as its starting value, its increment, etc. This creates a sequence object that will be used whenever a new value needs to be generated. IdiotId int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, Here’s an example of creating a table with an identity column: CREATE TABLE Idiots ( This option was added in PostgreSQL 10 in order to provide a SQL standard compliant method for adding an auto-incrementing column. Our first option is to make the column an identity column. Below are three options for creating an AUTO_INCREMENT style column in Postgres. When it comes to PostgreSQL, there are a few ways to create an auto-incrementing column. Some of those DBMSs also allow us to create sequence objects, which provide us with more options for creating an auto-increment type column. And in SQL Server we can use the IDENTITY property. In SQLite, we’d use the AUTOINCREMENT keyword.

In MySQL and MariaDB we can use the AUTO_INCREMENT keyword to create an automatically incrementing column in a table.

Postgresql create table auto increment