In PostgreSQL, version 10, a new constraint named “GENERATED AS IDENTITY” was introduced. The stated constraint enables the automatic assignment of unique numbers to a column. In addition to this, the “GENERATED AS IDENTITY” constraint allows us to specify the increment and start values for the IDENTITY column.

This blog post illustrates how to specify the starting and incrementing values for an identity column in PostgreSQL.

How to Specify the Start Value and the Increment Value for an IDENTITY Column in PostgreSQL?

Use the sequence option to specify the start value and the increment value for an IDENTITY column in PostgreSQL. Follow the below syntax to specify the start value and the increment value for an IDENTITY column of a table:

col_name TYPE GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ (START WITH start_val INCREMENT BY increment_val) ]

Example: Specify the Start Value and the Increment Value for an IDENTITY Column While Table Creation

In the below example, the starting value and increment value are specified at the time of table creation:

CREATE TABLE emp_example (
   emp_id INT GENERATED BY DEFAULT AS IDENTITY 
   (START WITH 5 INCREMENT BY 5),
   emp_name TEXT
);

Let’s insert a couple of records into the “emp_example” table by executing the following INSERT statement:

INSERT INTO emp_example (emp_name)
 VALUES ('john'),
 ('alex')
 RETURNING *;

Let’s insert some more records into the “emp_example” table. For this purpose, run the INSERT statement as follows:

INSERT INTO emp_example (emp_name)
 VALUES ('joseph'),
 ('alexa')
 RETURNING *;

The following screenshot depicts that the value of the IDENTITY column is modified according to the specified start and increment values:

That’s all about enabling or setting the sequence of options in PostgreSQL.

Conclusion

Use the sequence option to specify the start value and the increment value for an IDENTITY column in PostgreSQL. For instance, the “col_name TYPE GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ (START WITH start_val INCREMENT BY increment_val) ]” syntax is used in PostgreSQL to define an IDENTITY column with the start and increment values. This write-up has illustrated a thorough process for specifying the start and increment values for an IDENTITY column in PostgreSQL.