Copy a Table with an Identity to a Table with an Identity on the Same Database Keeping the Identities in Sync

An IDENTITY associates a table column with a sequence. This sequence automatically increments the column value as new rows are added.

There are some noteable restrictions on IDENTITY columns:

1. You cannot INSERT values manually into an IDENTITY column
2. You cannot UPDATE the values of an IDENTITY column once they has been populated via the sequence
3. You cannot ALTER a table column to become an IDENTITY
4. You cannot add an IDENTITY column to a table

These restrictions make it problematic to copy a table that contains an IDENTITY column to a new table that has an IDENTITY column on the same database. None the less, you’ll probably at some point want to do this.

Let’s try the typical approaches.

First, a COPY_TABLE.

dbadmin=> SELECT * FROM schema1.ident;
   c1   |  c2   |    c3
--------+-------+-----------
      1 | 90299 | XVARWMYHL
      2 |  3950 | GMJWQGKVB
      3 | 19514 | HMFPWWMTC
 250001 | 80360 | RBYGWDTLM
 250002 | 24514 | FRVNWRBWC
 250003 | 78210 | VFKYBFXJC
(6 rows)

dbadmin=> SELECT export_tables('', 'schema1.ident');
                                     export_tables
----------------------------------------------------------------------------------------
CREATE TABLE schema1.ident
(
    c1  IDENTITY ,
    c2 int,
    c3 varchar(80)
);

(1 row)

dbadmin=> SELECT COPY_TABLE('schema1.ident', 'schema2.ident');
                                COPY_TABLE
---------------------------------------------------------------------------
 Created table schema2.ident.
Copied table schema1.ident to schema2.ident

(1 row)

dbadmin=> SELECT export_tables('', 'schema2.ident');
                                      export_tables
------------------------------------------------------------------------------------------
CREATE TABLE schema2.ident
(
    c1 int NOT NULL,
    c2 int,
    c3 varchar(80)
);

(1 row)

The COPY_TABLE did not copy the IDENTITY. It simply made the destination column an INT.

How about a CREATE TABLE LIKE?

dbadmin=> DROP TABLE schema2.ident;
DROP TABLE

dbadmin=> CREATE TABLE schema2.ident LIKE schema1.ident INCLUDING PROJECTIONS;
CREATE TABLE

dbadmin=> SELECT export_tables('', 'schema2.ident');
                                      export_tables
------------------------------------------------------------------------------------------
CREATE TABLE schema2.ident
(
    c1 int NOT NULL,
    c2 int,
    c3 varchar(80)
);

(1 row)

Same deal. The column in the new table is not an IDENTITY.

How about we create our own table with an IDENTITY, then do a simple INSERT SELECT?

dbadmin=> DROP TABLE schema2.ident;
DROP TABLE

dbadmin=> CREATE TABLE schema2.ident (c1 IDENTITY, c2 INT, c3 VARCHAR(80));
CREATE TABLE

dbadmin=> INSERT INTO schema2.ident (c1, c2, c3) SELECT * FROM schema1.ident;
ERROR 2444:  Cannot insert into or update IDENTITY/AUTO_INCREMENT column "c1"

dbadmin=> INSERT INTO schema2.ident (c2, c3) SELECT c2, c3 FROM schema1.ident;
 OUTPUT
--------
      6
(1 row)

dbadmin=> SELECT * FROM schema2.ident;
 c1 |  c2   |    c3
----+-------+-----------
  1 | 90299 | XVARWMYHL
  2 |  3950 | GMJWQGKVB
  3 | 19514 | HMFPWWMTC
  4 | 80360 | RBYGWDTLM
  5 | 24514 | FRVNWRBWC
  6 | 78210 | VFKYBFXJC
(6 rows)

We were able to copy the data, but the IDENTITY values are out of sync!

To keep them in sync, the trick is to instead use Vertica’s IMPORT/EXPORT functionality, where we connect Vertica to itself!

For this to work, make sure the configuration parameter CopyFromVerticaWithIdentity is set to 1.

dbadmin=> DROP TABLE schema2.ident;
DROP TABLE

dbadmin=> CREATE TABLE schema2.ident (c1 IDENTITY, c2 INT, c3 VARCHAR(80));
CREATE TABLE

dbadmin=> SELECT node_name, node_address, export_address FROM nodes WHERE node_name = local_node_name();
        node_name        | node_address  | export_address
-------------------------+---------------+----------------
 v_verticademos_node0001 | 74.208.xxx.xx | 74.208.xxx.xx
(1 row)

dbadmin=> SELECT current_value, default_value, description FROM configuration_parameters WHERE parameter_name = 'CopyFromVerticaWithIdentity';
current_value | default_value |                                                  description
---------------+---------------+----------------------------------------------------------------------------------------------------------------
1             | 1             | When doing a COPY FROM VERTICA without an explicit columns list, include IDENTITY columns in the implicit list
(1 row)

dbadmin=> CONNECT TO VERTICA verticademos USER dbadmin PASSWORD 'xxxxxxxx' ON '74.208.xxx.xx', 5433;
CONNECT

dbadmin=> COPY schema2.ident FROM VERTICA verticademos.schema1.ident;
 Rows Loaded
-------------
           6
(1 row)

dbadmin=> SELECT * FROM schema2.ident;
   c1   |  c2   |    c3
--------+-------+-----------
      1 | 90299 | XVARWMYHL
      2 |  3950 | GMJWQGKVB
      3 | 19514 | HMFPWWMTC
 250001 | 80360 | RBYGWDTLM
 250002 | 24514 | FRVNWRBWC
 250003 | 78210 | VFKYBFXJC
(6 rows)

dbadmin=> DISCONNECT verticademos;
DISCONNECT

Now that the IDENTITY values have been preserved, the final step is to restart the the new table’s IDENTITY’s sequence to the current value of the original table’s IDENTITY’s sequence.

dbadmin=> SELECT sequence_schema, sequence_name, current_value FROM sequences WHERE identity_table_name = 'ident';
 sequence_schema | sequence_name | current_value
-----------------+---------------+---------------
 schema1         | ident_c1_seq  |        500000
 schema2         | ident_c1_seq  |             0
(2 rows)

dbadmin=> ALTER SEQUENCE schema2.ident_c1_seq RESTART 500001;
ALTER SEQUENCE

dbadmin=> SELECT sequence_schema, sequence_name, current_value FROM sequences WHERE identity_table_name = 'ident';
 sequence_schema | sequence_name | current_value
-----------------+---------------+---------------
 schema1         | ident_c1_seq  |        500000
 schema2         | ident_c1_seq  |        500000
(2 rows)

Have fun!

Helpful Links:
AUTO_INCREMENT and IDENTITY Sequences
Exporting Data
General Parameters