Amazon Redshift Database Error: seed value for an identity out of range
While migrating database tables from SQL Server to Amazon Redshift database, during table creation on Redshift database I came across with following interesting error regarding identity column seed value.
The error message is as follos: [Amazon](500310) Invalid operation: seed value for an identity out of range; [SQL State=22023, DB Errorcode=500310]
I used AWS Schema Conversion Tool (SCT) to prepare create table command SQL scripts to migrate SQL Server database table objects to Amazon Redshift database.
In one of those tables, the source Create Table SQL command on SQL Server and the converted DDL command for Amazon Redshift database is as follows:
-- from source schema on SQL Server database
CREATE TABLE [OFSA].[ServiceOrderNumber](
[ID_ServiceOrderNumber] int IDENTITY(-2147483648, 1) NOT NULL,
[ServiceOrderNumber] bigint NULL
)
ON [PRIMARY];
-- and for target schema on Amazon Redshift database
CREATE TABLE IF NOT EXISTS ofsa.serviceordernumber
(
id_serviceordernumber INTEGER IDENTITY(-2147483648, 1) NOT NULL,
serviceordernumber BIGINT
)
DISTSTYLE KEY
DISTKEY
(
id_serviceordernumber
)
SORTKEY
(
serviceordernumber
);
Of course as SQL developers can guess, executing the resultant SQL script after conversion by SCT failed.
The execution of the above SQL script on Amazon Redshift database will throw below exception
[Amazon](500310) Invalid operation: seed value for an identity out of range; [SQL State=22023, DB Errorcode=500310]
1 statement failed.
To correct this error on Amazon Redshift database, changing the initial SEED value from -2147483648 [Add to Citavi project by ISBN] to -2147483647 for IDENTITY column with INT data type.
It is interesting that although INT data type has a valid value range between -2147483648 to +2147483647 above SQL error occurs for INT data type while with its -32768 to +32767 valid value range SMALLINT data type will not cause an SQL error when -32768 is used as the initial SEED value.