SCOPE_IDENTITY Function - SQL Server to PostgreSQL Migration

In SQL Server, the SCOPE_IDENTITY function returns the last inserted identity in the current scope (current stored procedure, trigger, function or batch).

In PostgreSQL, you can use the RETURNING clause of the INSERT statement.

SQL Server:

  -- Sample table
  CREATE TABLE colors (
    id INT IDENTITY(5,1),
    name VARCHAR(30)
  );
 
  DECLARE @last_id INT;
 
  INSERT INTO colors (name) VALUES ('White');
 
  -- Get the inserted ID
  SET @last_id = SCOPE_IDENTITY();  
  PRINT @last_id;
  # 5

PostgreSQL:

  -- Sample table
  CREATE TABLE colors (
    id INT GENERATED ALWAYS AS IDENTITY(START WITH 5 INCREMENT BY 1),
    name VARCHAR(30)
  );
 
  DO $$
  DECLARE 
    last_id INT;
  BEGIN
    -- Insert a row and get the inserted ID
    INSERT INTO colors (name) VALUES ('White') RETURNING id INTO last_id;      
    RAISE NOTICE '%', last_id;
  END $$;
  # 5

For more information, see SQL Server to PostgreSQL Migration.