Amazon Redshift table generating ID out of range with identity function

Question

Hello AWS community,

I am encountering an issue while attempting to insert records into a table in Amazon Redshift, and I'm seeking assistance from the community to help me resolve it. The error message I'm receiving is: "Value out of range for 4 bytes. Detail: error: Value out of range for 4 bytes. code: 8001 context: Input:2147483705."

The table structure includes a column named id defined as an integer. The current highest ID value in the table is 2126824931, which falls well within the range of a 4-byte integer. However, when I try to insert a new record with a higher ID, I encounter the aforementioned error.

Here is an example of the INSERT command that triggers the error:

INSERT INTO announcer_users (hash, hash_type, announcer_file_id, exclude_include, created_at, announcer_id) VALUES ...

And this is the id column definition:

id                integer default "identity"(1316003, 0, ('1,1'::character varying)::text) not null encode az64

I have verified that there are no duplicate IDs in the table and confirmed that the values I'm attempting to insert are within the valid range for a 4-byte integer. Despite these checks, I am still unable to insert records with higher IDs.

I kindly request the community's insights and suggestions to help me troubleshoot and resolve this issue. Has anyone encountered a similar problem before? Are there any specific configurations or settings in Amazon Redshift that I should examine? Any guidance or recommendations would be greatly appreciated.

Thank you in advance for your assistance.

Best regards,

Answer

Hi, it seems that the id you try to insert (2147483705 as per error message) is slightly out of allowed range of 4-byte int type allowed by Redshift:  -2147483648 to +2147483647.

Answer

Yes, thanks for your answer. I know that redshift can skip a number of values, but the difference between the highest ID 2126824931 and the current one 2147483705 is trying to insert is at 20658774. It's making a big skip.

Answer

The column `IDENTITY (seed, step)` , where values start with the value specified as `seed` and increment by the number specified as `step`. Double check how you have defined the columns. Also Amazon Redshift can skip a number of values when creating the identity values. Identity values are unique, but the order might not be maintained. Your can make the column `BIGINT` or `INT8` which provides `8 bytes` from ranging from `-9223372036854775808` to `9223372036854775807`

Amazon Redshift table generating ID out of range with identity function

Relevant content