Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

renaming schema does not rename schema in tables default values function using sequence


column default does not change to nextval('new_schema.test_seq')
How to achive this?

Best Answers

  • erkkikerkkik
    Answer ✓

    Thank You for information :)

    Im running
    dbadmin=> select version();

    version

    Vertica Analytic Database v9.3.1-0

  • Jim_KnicelyJim_Knicely Administrator
    edited July 2020 Answer ✓

    Okay, for now you will have to manually update the DEFAULT value to point to the sequence's new schema:

    Example:
    ALTER TABLE new_schema.test_table ALTER COLUMN id SET DEFAULT NEXTVAL('new_schema.test_seq');

Answers

  • Jim_KnicelyJim_Knicely Administrator
    edited July 2020

    This issue has been resolved in Vertica 10.0.0-3 released on 07/29/2020 (Issue: VER-73468) and will soon be fixed in Vertica 9.3.1-14 (not released yet).

    Release Notes:
    https://www.vertica.com/docs/ReleaseNotes/10.0.x/Vertica_10.0.x_Release_Notes.htm#10.0.0-3

    Example:

    dbadmin=> SELECT version();
                   version
    -------------------------------------
     Vertica Analytic Database v10.0.0-3
    (1 row)
    dbadmin=> CREATE SCHEMA test;
    CREATE SCHEMA
    
    dbadmin=> CREATE SEQUENCE test.sequence1;
    CREATE SEQUENCE
    
    dbadmin=> CREATE TABLE test.test (c INT DEFAULT test.sequence1.NEXTVAL);
    CREATE TABLE
    
    dbadmin=> \d test.test
                                             List of Fields by Tables
     Schema | Table | Column | Type | Size |          Default          | Not Null | Primary Key | Foreign Key
    --------+-------+--------+------+------+---------------------------+----------+-------------+-------------
     test   | test  | c      | int  |    8 | nextval('test.sequence1') | f        | f           |
    (1 row)
    
    dbadmin=> ALTER SCHEMA test RENAME TO test_new;
    ALTER SCHEMA
    
    dbadmin=> \d test.test
    Did not find any relation.
    
    dbadmin=> \d test_new.test
                                                List of Fields by Tables
      Schema  | Table | Column | Type | Size |            Default            | Not Null | Primary Key | Foreign Key
    ----------+-------+--------+------+------+-------------------------------+----------+-------------+-------------
     test_new | test  | c      | int  |    8 | nextval('test_new.sequence1') | f        | f           |
    (1 row)
    

    Which Vertica version are you running?

  • Thank you!
    This workaround did the trick.

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.