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

erkkik

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

erkkik

Thank You for information

Im running
dbadmin=> select version();

version

Vertica Analytic Database v9.3.1-0

Jim_Knicely

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');

Jim_Knicely

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?

erkkik

Thank you!
This workaround did the trick.

Jim_Knicely

@erkkik - Fyi, Vertica 9.3.1-14 is now avaiilable!

https://www.vertica.com/docs/ReleaseNotes/9.3.x/Vertica_9.3.x_Release_Notes.htm#9.3.1-14