Vertica - Export table with IDENTITY

In Vertica, We have can created a table with IDENTITY or AUTO INCREMENT constraints or sequence for a field.
While using export command and taking dump and restore the dump into anther vertica setup, we facing error .
All rows are rejected due to above constraints .
Need help to take dump with constraint (IDENTITY or AUTO INCREMENT) and restore data dump with same constraint for backup process.
Regards
M.Ramesh

Comments

  • marcothesanemarcothesane - Select Field - Administrator
    Auto increment/ identity and accepting values from outside are diagonally opposed requirements.

    Workaround: create at least the target table like so: the previously IDENTITY column as INTEGER NOT NULL. Then export/import. Then SELECT MAX(<identity>) FROM <your table>; . Use the returned value for the initial value of your following CREATE SEQUENCE foo MINVALUE <your number>; . Then, ALTER TABLE <your table> ALTER COLUMN <idcolumn> DEFAULT foo.nextval;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file