IDENTITY Columns - SQL Server to PostgreSQL Migration

The IDENTITY property allows you to automatically assign incremental integer values to a column. In PostgreSQL you can use the GENERATED AS IDENTITY property:

SQL Server:

  -- Table with identity value starting at 7 and step 3
  CREATE TABLE colors 
  (
     id INT IDENTITY(7, 3),
     name VARCHAR(10)
  );
 
  -- Insert a few sample rows
  INSERT INTO colors (name) VALUES ('blue');
  INSERT INTO colors (name) VALUES ('white');
  INSERT INTO colors (name) VALUES ('green');
 
  -- id was automatically assigned
  SELECT * FROM colors;  
  #  7     blue
  #  10   white
  #  13   green

Above you can see that the values to id column were automatically assigned.

PostgreSQL:

  -- Table with identity value starting at 7 and step 3
  CREATE TABLE colors 
  (
     id INT GENERATED ALWAYS AS IDENTITY (INCREMENT BY 3 START WITH 7),
     name VARCHAR(10)
  );
 
  -- Insert a few sample rows
  INSERT INTO colors (name) VALUES ('blue');
  INSERT INTO colors (name) VALUES ('white');
  INSERT INTO colors (name) VALUES ('green');
 
  -- id was automatically assigned
  SELECT * FROM colors;  
  #  7     blue
  #  10   white
  #  13   green

GENERATED ALWAYS AS IDENTITY clause is available since PostgreSQL 10. Additionally you can use CREATE SEQUENCE statement and DEFAULT NEXTVAL as follows:

PostgreSQL:

  DROP TABLE colors;
 
  -- Creating sequence starting at 7 and step 3
  CREATE SEQUENCE colors_seq INCREMENT BY 3 START WITH 7;
 
  -- Use the sequence in DEFAULT
  CREATE TABLE colors 
  (
     id INT DEFAULT NEXTVAL('colors_seq'),
     name VARCHAR(10)
  );
 
  -- Insert a few sample rows
  INSERT INTO colors (name) VALUES ('blue');
  INSERT INTO colors (name) VALUES ('white');
  INSERT INTO colors (name) VALUES ('green');
 
  -- id was automatically assigned
  SELECT * FROM colors;  
  #  7     blue
  #  10   white
  #  13   green

For more information, see SQL Server to PostgreSQL Migration.