SCOPE_IDENTITY vs RETURNING: Getting ID After INSERT in Migration

SCOPE_IDENTITY returns the last identity inserted in the current scope — safer than @@IDENTITY when triggers exist. PostgreSQL exposes the inserted row via RETURNING in the same statement.

RETURNING

INSERT INTO orders (customer_id, total)
VALUES (42, 199.90)
RETURNING id AS new_id;