7

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?

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
wilty
  • 175
  • 1
  • 1
  • 10
  • I think you wrote it wrong, you want to convert from `timestamp without timezone` to `timestamp with timezone`, right? – MatheusOl Aug 15 '13 at 21:14
  • nope the column is time with time zone and i wish to change to timestamp with time zone – wilty Aug 16 '13 at 14:39
  • OH! Sorry... Now I got it, I was reading `timestamp` instead of `time` all the time... lol... – MatheusOl Aug 20 '13 at 11:07

3 Answers3

19

you can do something like this:

alter table key_request
alter column end_date type timestamp with time zone using date('20130101') + end_date;

sql fiddle demo

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • 2
    This solution is better than the accepted on, as you don't have to drop the column. As well it corresponds to the PostgreSQL tutorial: https://www.postgresqltutorial.com/postgresql-change-column-type/ – olivmir May 27 '20 at 08:33
3

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
Declan_K
  • 6,726
  • 2
  • 19
  • 30
  • that works fine but is there a way i can place that new column back in the position of the old column without deleting columns in between? – wilty Aug 15 '13 at 14:54
  • 1
    postgresql does not support changing the column order in an existing table. If you "REALLY" need it to be in a specific position (and the is no valid reason why you would need that) then you would need to recreate the entire table. – Declan_K Aug 15 '13 at 15:05
  • 1
    With the solution of @Roman Pekar you don't have to drop the column. – olivmir May 27 '20 at 08:37
0

Changing from java.sql.Date to java.util.Date

ALTER TABLE key_request ALTER COLUMN end_date TYPE timestamp without time zone;

If you have dependant views that relate to that table

  1. drop views
  2. change column type
  3. recreate views

Solution coming from a java/hibernate approach while in type migration.

  • java.sql.Date is translated to date
  • java.util.Date is translated to timestamp without time zone
DanielK
  • 424
  • 6
  • 17