wilty wilty - 4 months ago 8
SQL Question

alter column from time with time zone to timestamp

I am having trouble changing a column called

end_date
in a table called
key_request
from time with time zone to
timestamp
in my Postgres database . I have tried using the following code:

alter table key_request alter column end_date type timestamp with time zone using end_date::timestamp with time zone


I keep getting the following error:

ERROR: cannot cast type time with time zone to timestamp with time zone


Any idea of how I can adjust this query to work?

Answer

I woul do this in a series of steps

  1. Alter the table, adding a new column end_date1 as time with time zone
  2. Copy the date from end_date(old) to end_date1
  3. Alter the table, droping the old end_date column
  4. Alter the table,reaming end_date1 to end_date