IDENTITY

Description

The IDENTITY function is a system function that operates on a specified column of a table to determine the initial value for the identity. If the initial value is not available, it defaults to the value provided in the function. This will be translation to a Sequence in Snowflake.

Grammar Syntax

"identity"(oid_id, oid_table_id, default)

This function is no longer supported in Redshift. It uses the default value to define the identity and behaves like a standard identity column.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE IF NOT EXISTS table_test
(
    id integer,
    inventory_combo BIGINT  DEFAULT "identity"(850178, 0, '5,3'::text)
);

INSERT INTO table_test (id) VALUES
    (1),
    (2),
    (3),
    (4);

SELECT * FROM table_test;

Results

id	inventory_combo
1	5
2	8
3	11
3	14

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE IF NOT EXISTS table_test
(
    id integer,
    inventory_combo BIGINT IDENTITY(5,3) ORDER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/13/2024",  "domain": "test" }}';

INSERT INTO table_test (id) VALUES
    (1),
    (2),
    (3),
    (4);

SELECT * FROM
    table_test;

Results

id	inventory_combo
1	5
2	8
3	11
3	14

Related EWIs

There are no known issues.