Working with column data conversions

Vertica conforms to the SQL standard by disallowing certain data conversions for table columns. However, you sometimes need to work around this restriction when you convert data from a non-SQL database. The following examples describe one such workaround, using the following table:

=> CREATE TABLE sales(id INT, price VARCHAR) UNSEGMENTED ALL NODES;
CREATE TABLE
=> INSERT INTO sales VALUES (1, '$50.00');
 OUTPUT
--------
      1
(1 row)

=> INSERT INTO sales VALUES (2, '$100.00');
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
=> SELECT * FROM SALES;
 id |  price
----+---------
  1 | $50.00
  2 | $100.00
(2 rows)

To convert the price column's existing data type from VARCHAR to NUMERIC, complete these steps:

1. Add a new column for temporary use. Assign the column a NUMERIC data type, and derive its default value from the existing price column.

2. Drop the original price column.

3. Rename the new column to the original column.

Add a new column for temporary use

1. Add a column temp_price to table sales. You can use the new column temporarily, setting its data type to what you want (NUMERIC), and deriving its default value from the price column. Cast the default value for the new column to a NUMERIC data type and query the table:

   => ALTER TABLE sales ADD COLUMN temp_price NUMERIC(10,2) DEFAULT
   SUBSTR(sales.price, 2)::NUMERIC;
   ALTER TABLE
   
   => SELECT * FROM SALES;
    id |  price  | temp_price
   ----+---------+------------
     1 | $50.00  |      50.00
     2 | $100.00 |     100.00
   (2 rows)
   

2. Use ALTER TABLE to drop the default expression from the new column temp_price. Vertica retains the values stored in this column:

   => ALTER TABLE sales ALTER COLUMN temp_price DROP DEFAULT;
   ALTER TABLE
   

Drop the original price column

Drop the extraneous price column. Before doing so, you must first advance the AHM to purge historical data that would otherwise prevent the drop operation:

1. Advance the AHM:

   => SELECT MAKE_AHM_NOW();
            MAKE_AHM_NOW
   -------------------------------
    AHM set (New AHM Epoch: 6354)
   (1 row)
   

2. Drop the original price column:

   => ALTER TABLE sales DROP COLUMN price CASCADE;
   ALTER COLUMN
   

Rename the new column to the original column

You can now rename the temp_price column to price:

1. Use ALTER TABLE to rename the column:

   => ALTER TABLE sales RENAME COLUMN temp_price to price;
   

2. Query the sales table again:

   => SELECT * FROM sales;
    id | price
   ----+--------
     1 |  50.00
     2 | 100.00
   (2 rows)