2

While working on a legacy Redshift database I discovered unfamiliar pattern for default identity values for an autoincrement column. E.g.:

create table sometable (row_id bigint default "identity"(24078855, 0, '1,1'::text), ...

And surprisingly I wasn't able to find any docs about that identity function. The only thing I was able to dig up is the following:

select * from pg_proc proc
join pg_language lang on proc.prolang = lang.oid
where proc.proname = 'identity';

So I've found out that function to be internal, and it's prosrc column is just ff_identity_int64 (not googleable, unfortunately).

Could someone please provide me with some info about its first and second arguments? I mean 24078855 and 0 from that example "identity"(24078855, 0, '1,1'::text). ('1,1'::text -- here first 1 is the start value and second 1 is the step of increment). But 24078855 and 0 are still mysterious for me.

Boris Uvarov
  • 58
  • 1
  • 9
  • something tells me it's max value and min value (seed), what else could it be? – AlexYes Oct 31 '17 at 12:31
  • @AlexYes I did some experiments using `"identity"(5, 0, '1,2'::text)`. I wasn't able to make it take 5 in account while adding rows to the table, deleting them, and adding again. Looks like it takes in account only '1,2'::text, consistently increasing that autoincrement. – Boris Uvarov Oct 31 '17 at 12:45

3 Answers3

4
"identity"(24078855, 0, '1,1'::text)
  1. table OID
  2. 0-based column index
  3. text representation of parameters provided with IDENTITY clause

For reference look at pg_attrdef table

Aleksandr Kravets
  • 5,750
  • 7
  • 53
  • 72
1

I've found that the first argument is the oid of the table. So, in your example (without the schema specified) 

select oid from pg_class where relname = 'sometable'
Intellicraft
  • 11
  • 1
0

The IDENTITY clause is documented in the CREATE TABLE docs here: http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html#identity-clause

IDENTITY(seed, step)

Clause that specifies that the column is an IDENTITY column. An IDENTITY column contains unique auto-generated values. The data type for an IDENTITY column must be either INT or BIGINT. When you add rows using an INSERT statement, these values start with the value specified as seed and increment by the number specified as step. When you load the table using a COPY statement, an IDENTITY column might not be useful. With a COPY operation, the data is loaded in parallel and distributed to the node slices. To be sure that the identity values are unique, Amazon Redshift skips a number of values when creating the identity values. As a result, identity values are unique and sequential, but not consecutive, and the order might not match the order in the source files.

Joe Harris
  • 13,671
  • 4
  • 47
  • 54