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

Question

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

I don't know why this is down voted ? - I am asking technical problem in vertica database which I need to fix ASAP . — Nagaraj Vittal, Jun 23 '17 at 12:47

score 1 · Accepted Answer · answered Jun 27 '17 at 09:23

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

Thanks marcothesane. This work around fixed the issue. By seeing the severity of the issue I feel question should be up voted. Can you please upvote this question. — Nagaraj Vittal, Jun 28 '17 at 11:39

Kamil Gosciminski · Answer 2 · 2017-06-24T14:38:14.497

0

I believe this should work given a table t with column c:

ALTER TABLE t ALTER COLUMN c SET NOT NULL;
ALTER TABLE t ALTER COLUMN c SET DATA TYPE TIMESTAMPTZ;

References:

edited Jun 24 '17 at 14:38

answered Jun 23 '17 at 10:15

Kamil Gosciminski

16,547
8
49
72

1

What does no luck mean? Errors? If so, what is the message? – Kamil Gosciminski Jun 23 '17 at 16:08
I am getting error "ERROR: Syntax error at or near "DATA" [SQL State=42601]" – Nagaraj Vittal Jun 24 '17 at 12:18
This is because it should be set in two separate statements. See updated answer. – Kamil Gosciminski Jun 24 '17 at 14:38
Existing column has null values , so set not null is throwing error. – Nagaraj Vittal Jun 25 '17 at 14:52
Well you have to update your existing values not to be null, but this is obvious, since it works for every value within the column. You can't have not null constraint and null values. – Kamil Gosciminski Jun 25 '17 at 20:27

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

2 Answers2