IDENTITY sequences

IDENTITY (synonymous with AUTO_INCREMENT) columns are defined with a sequence that automatically increments column values as new rows are added. You define an IDENTITY column in a table as follows:

CREATE TABLE table-name...
  (column-name {IDENTITY | AUTO_INCREMENT}
      ( [ cache-size | start, increment [, cache-size ] ] )

Settings

Managing settings

Like named sequences, you can manage an IDENTITY column with ALTER SEQUENCE—for example, reset its start integer. Two exceptions apply: because the sequence is defined as part of a table column, you cannot change the sequence name or schema. You can query the SEQUENCES system table for the name of an IDENTITY column's sequence. This name is automatically created when you define the table, and conforms to the following convention:

table-name_col-name_seq

For example, you can change the maximum value of an IDENTITY column that is defined in the testAutoId table:

=> SELECT * FROM sequences WHERE identity_table_name  = 'testAutoId';
-[ RECORD 1 ]-------+-------------------------
sequence_schema     | public
sequence_name       | testAutoId_autoIdCol_seq
owner_name          | dbadmin
identity_table_name | testAutoId
session_cache_count | 250000
allow_cycle         | f
output_ordered      | f
increment_by        | 1
minimum             | 1
maximum             | 1000
current_value       | 1
sequence_schema_id  | 45035996273704980
sequence_id         | 45035996274278950
owner_id            | 45035996273704962
identity_table_id   | 45035996274278948

=> ALTER SEQUENCE testAutoId_autoIdCol_seq maxvalue 10000;
ALTER SEQUENCE

This change, like other changes to a sequence, take effect only when you start a new database session. One exception applies: changes to the sequence owner take effect immediately.

You can obtain the last value generated for an IDENTITY column by calling LAST_INSERT_ID.

Restrictions

The following restrictions apply to IDENTITY columns:

• A table can contain only one IDENTITY column.
• IDENTITY column values automatically increment before the current transaction is committed; rolling back the transaction does not revert the change.
• You cannot change the value of an IDENTITY column.

Examples

The following example shows how to use the IDENTITY column-constraint to create a table with an ID column. The ID column has an initial value of 1. It is incremented by 1 every time a row is inserted.

1. Create table Premium_Customer:

   => CREATE TABLE Premium_Customer(
        ID IDENTITY(1,1),
        lname VARCHAR(25),
        fname VARCHAR(25),
        store_membership_card INTEGER
   );
   => INSERT INTO Premium_Customer (lname, fname, store_membership_card )
        VALUES ('Gupta', 'Saleem', 475987);
   

   The IDENTITY column has a seed of 1, which specifies the value for the first row loaded into the table, and an increment of 1, which specifies the value that is added to the IDENTITY value of the previous row.

2. Confirm the row you added and see the ID value:

   => SELECT * FROM Premium_Customer;
    ID | lname | fname  | store_membership_card
   ----+-------+--------+-----------------------
     1 | Gupta | Saleem |                475987
   (1 row)
   

3. Add another row:

   => INSERT INTO Premium_Customer (lname, fname, store_membership_card)
      VALUES ('Lee', 'Chen', 598742);
   

4. Call the Vertica function LAST_INSERT_ID. The function returns value 2 because you previously inserted a new customer (Chen Lee), and this value is incremented each time a row is inserted:

   => SELECT LAST_INSERT_ID();
    last_insert_id
   ----------------
                  2
   (1 row)
   

5. View all the ID values in the Premium_Customer table:

   => SELECT * FROM Premium_Customer;
    ID | lname | fname  | store_membership_card
   ----+-------+--------+-----------------------
     1 | Gupta | Saleem |                475987
     2 | Lee   | Chen   |                598742
   (2 rows)
   

The next three examples illustrate the three valid ways to use IDENTITY arguments.

The first example uses a cache of 100, and the defaults for start value (1) and increment value (1):

=> CREATE TABLE t1(x IDENTITY(100), y INT);

The next example specifies the start and increment values as 1, and defaults to a cache value of 250,000:

=> CREATE TABLE t2(y IDENTITY(1,1), x INT);

The third example specifies start and increment values of 1, and a cache value of 100:

=> CREATE TABLE t3(z IDENTITY(1,1,100), zx INT);