How do I copy sequences to a new DB?

[Brett]

Hi,

How can I copy sequences from one db to another so that they maintain the current values from the original db. If I go the export_objects route, it does not retain the current value of the sequence.

Thank you.

[JimKnicely]

Brett,

Once you create the sequences in the new location, you can do something like the following...

Code: Select all

/opt/vertica/bin/vsql -Atc "select 'select ''ALTER SEQUENCE ' || sequence_schema || '.' || sequence_name || ' RESTART WITH '' || NVL((SELECT MAX(' || column_name || ') + 1 FROM ' || table_schema || '.' || table_name || '), 1) || '';'';' from sequences join primary_keys on table_schema = sequence_schema and table_name = replace(sequence_name, '_seq', '') where sequence_schema = '?';" | vsql -At | vsql

Replace the ? with the schema name that contains the sequences you want to alter.

Note: The example assumes that you've created primary keys for the sequence column. You might have to modify the script a bit otherwise.