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

erkkikerkkik Community Edition User


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

Best Answers

  • erkkikerkkik Community Edition User
    Answer ✓

    Thank You for information :)

    Im running
    dbadmin=> select version();

    version

    Vertica Analytic Database v9.3.1-0

  • Jim_KnicelyJim_Knicely - Select Field - 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 - Select Field - 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?

  • erkkikerkkik Community Edition User

    Thank you!
    This workaround did the trick.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

Leave a Comment

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