Search code examples
databaseverticavsql

How to alter vertica database column of timestamp type to timestamp with timezone?

I have a vertica database table with column of type timestamp, now I want to alter it to type of "timestamp with time zone null". While creating the table column created without timezone but now for new users we wanted to store timezone but I am not seeing any option.

Can someone help me on this ?

I have already tried below queries:

ALTER TABLE product_info ALTER COLUMN "product_sold_at" SET DATA TYPE timestampt with time zone null

Solution

  • There are some source/target data type conversions that you can't make. TIMESTAMP to TIMESTAMP WITH TIME ZONE is one of them:

    SQL>ALTER TABLE w_ts ALTER COLUMN ts SET DATA TYPE TIMESTAMP WITH TIME ZONE;
executing statement returns SQL_ERROR:
0A000:1:2377:ROLLBACK 2377:  Cannot convert column "ts" from 
"timestamp" to type "timestamptz"

    Workaround could be:

    ALTER TABLE w_ts ADD COLUMN tsz TIMESTAMP WITH TIME ZONE DEFAULT ts;

    This adds a column with your new type and fills it instantly with the contents of the old column.

    Now remove the default constraint:

    ALTER TABLE w_ts ALTER COLUMN tsz DROP DEFAULT;

    And advance the Ancient History Mark to get rid of the still existing link from the dropped default constraint:

    SELECT MAKE_AHM_NOW();

    Now you can drop the old column:

    ALTER TABLE w_ts DROP COLUMN ts;

    And rename the new column to the original column's name:

    ALTER TABLE w_ts RENAME COLUMN tsz TO ts;

    Quite a few steps, but it works and is not too slow ....

    Good luck

    Marco