Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Specify value in identity column?

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

Matillion Agent  

David Lipowitz —

Hi Kevin,

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).

Last, if you decide to manage the surrogate key manually, my colleague wrote a nice piece about that on our forums, which you can find here: Recommended method to create surrogate keys.

I hope that helps.

Best Regards,
Dave


Kevin Havice —

Oh, of course! If I want a single "Unspecified" member with a key value of -1, then I can set the "seed" to -1, and ensure it is the first record entered into the table.

And if I want the rest of the records to have key values that are greater than 0, then I can set the "step" to 2 (or more).

Just tried this, and it works. Thanks!


Kevin Havice —

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?


Matillion Agent  

Kalyan Arangam —

Hi Kevin,

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.

Hope that makes sense.

Best
Kalyan

Post Your Community Answer

To add an answer please login