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

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 ....

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

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)