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

Vertica v8.0 - changing data type int to decimal(39,0) in a production table with 4 billion records

Hi Vertica Experts,
I have a scenario that I need to change the datatype of a column from integer to decimal(39,0) in a production table but I am struggling to find an efficient way how to do it.

The table is in production and has 4 billion records.

I've tried the following in a development environment:

The standard alter table doesn't work:
ALTER TABLE SCHEMA.TableName alter column ColumnName set data type decimal(39,0);

Changing the data type from int to decimal(18,0) works fine but extending from decimal(18,0) to decimal(39,0) doesn't work.

Converting int to varchar doesn't work.

Any help is welcome.

Thanks
Migert

Comments

  • marcothesanemarcothesane Administrator

    Try:

    ALTER TABLE table_name ADD COLUMN colname_as_num DECIMAL(39,0) DEFAULT colname;

    Then:

    ALTER TABLE table_name ALTER COLUMN colname_as_int DROP DEFAULT;

    Then:

    ALTER TABLE table_name DROP COLUMN colname;

    And finally:

    ALTER TABLE table_name RENAME COLUMN colname_as_int TO colname;

    Only works, however, if you have no projections on table_name with colname as an ORDER BY column or a SEGMENTED BY HASH() column; you would have to replace those projections with another superprojection and drop those projections first ....

  • Jim_KnicelyJim_Knicely Administrator

    @Migert - Fyi ... I've used the method that @marcothesane described (adding a new column with a default value) on tables with 65+ billion records and it works great! Definitely the "fastest" way to accomplish your goal :)

  • Hi guys, thanks for your reply.

    @marcothesane - I was thinking for a similar solution but I wasn't convinced about the step that copies the data from an int column to a decimal column.

    @Jim_Knicely - thanks for sharing your experience using this method.

    Just thinking about a similar scenario but converting from numeric to varchar, do you think using default cast(colname as varchar) the performance will be similar?

    ALTER TABLE table_name ADD COLUMN colname_as_num VARCHAR(50) DEFAULT cast(colname as varchar);

  • Jim_KnicelyJim_Knicely Administrator

    Hi,

    I think using the "default value" method will always be the fastest route of changing a columns data type.

    Not that you don't need to use the CAST function in the column default expression as Vertica will perform an implicit data type conversion for you.

    Example:

    dbadmin=> create table test (c1 int, c2 numeric) order by c1 segmented by hash(c1) all nodes;
    CREATE TABLE
    
    dbadmin=> insert into test select 1, 1.1;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> \d public.test;
                                         List of Fields by Tables
     Schema | Table | Column |      Type      | Size | Default | Not Null | Primary Key | Foreign Key
    --------+-------+--------+----------------+------+---------+----------+-------------+-------------
     public | test  | c1     | int            |    8 |         | f        | f           |
     public | test  | c2     | numeric(37,15) |   16 |         | f        | f           |
    (2 rows)
    
    dbadmin=> insert into test select 1, 1.1;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> select * from test;
     c1 |        c2
    ----+-------------------
      1 | 1.100000000000000
    (1 row)
    
    dbadmin=> alter table public.test add column c3 varchar(17) default c2;
    ALTER TABLE
    
    dbadmin=> alter table public.test alter column c3 drop default;
    ALTER TABLE
    
    dbadmin=> alter table public.test drop column c2;
    ALTER TABLE
    
    dbadmin=> alter table public.test rename column c3 to c2;
    ALTER COLUMN
    
    dbadmin=> \d public.test;
                                       List of Fields by Tables
     Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
    --------+-------+--------+-------------+------+---------+----------+-------------+-------------
     public | test  | c1     | int         |    8 |         | f        | f           |
     public | test  | c2     | varchar(17) |   17 |         | f        | f           |
    (2 rows)
    
    dbadmin=> select * from public.test;
     c1 |        c2
    ----+-------------------
      1 | 1.100000000000000
    (1 row)
    

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.