This is my first post on stackoverflow! If I fail to follow the "ask a question" policy properly, let me apologize firsthand here.
With that said, I'm running into an issue with an "Identity" column in AWS Redshift, in relation to the following post, but different question:
Identity Column not respected on Insert into() (Amazon Redshift)
Question (Example values provided below are "just example", to illustrate the problem): "If I have a max(identityColumn) = 100, for a table that has 100 records, why wouldn't new records I insert later have identityColumn value greater than 100?"
Expected Result: If I insert 5 new records to the new table, the identityColumn will have values of "101", "102", "103", "104", "105"
Actual Result: New records are having values of "52", "57", "62"...
If I understand correctly from the post I pasted above, the Identity column may not always be in sequence or consecutive order...which is ok. However, the issue I face is when I attempt to insert new records, the identity column isn't continuing from the last "max" identity value.
Here are small examples of the kind of code being used:
---EXAMPLE 1---
--1. Create new table with identity column
CREATE TABLE newTable_With_Identity
(
identityColumn bigint IDENTITY(1,1)
,column1
,column2
,column3
,column4
,column5
);
--2. Insert data from old table into new, identity table
INSERT INTO newTable_With_Identity
(
column1
,column2
,column3
,column4
,column5
)
(Select * from oldtable order by column3 asc);
---EXAMPLE 2---
(Same as above, but changing the last select statement to the following)
(Select * from oldtable order by column1, column2, column3 asc);
