I want to have an "identity" column in a dimension data warehouse table. As a surrogate key. And an auto-generated one at that -- so that I don't have to do any work to populate the value of the surrogate key.
I also want this dimension to have an "Unspecified" member, with -1 as its surrogate key value.
But I can't figure out how to get both. I did some research, and Redshift seems pretty strict with its identity columns -- I don't see any way to ever specify a value in an identity column.
I think I found that with SQL Server, you can temporarily turn off the "identity" feature of a column, and do an insert/update with a specified value for that column.
Is there any "hack" like this for Redshift, or anything I'm missing, that would allow me to have my cake and eat it, too?
4 Community Answers
David Lipowitz —
You can’t manipulate the identity column once it’s created, but you can create the column with a starting value (called a seed in their documentation). See the IDENTITY(seed, step) syntax in Redshift’s documentation here: CREATE TABLE.
Note that you can also create identity columns using Matillion’s Create Table Component if you set the “Identity Columns” property (more documentation on that here: Create Table Component).
Oh, except that when I do an "upsert" to the table with a Table Update component...the first record with the key value of -1 does not seem to KEEP its key value of -1. It had its key value changed. Even though I believe that first record shouldn't have been updated, as part of the upsert. Weird.
Why is that? I don't get it. In general, with an identity column, can I not assume that records will keep their key values after a table update?
As far as i know, Redshift does allow updating a column set to IDENTITY.
The only reason I can think of is that your TABLE UPDATE component is set to DELETE/INSERT and the Identity-column is mapped to ‘Unique Keys’ property. In this configuration matillion will issue a DELETE statement to delete any rows with matching keys and then perform an INSERT. So the DELETE would have deleted your original keys and the INSERT would have affected a new value in the IDENTITY field.
If the above reflects your configuration of TABLE-UPDATE component, change your Update Strategy to UPDATE/INSERT. Then your identity would not be affected.