CODEWITHSUNDEEP
sqlpostgresql
Marko Petričević
Marko Petričević

Reputation: 333

How to convert time without time zone to timestamp without time zone?

I need to convert couple of columns in one table in PSQL and I really dont want to drop the table to fix this (thats my last resort). Is there a way to do this, because when I write:

    ALTER TABLE table ALTER TABLE column type TIMESTAMP without time zone using column::TIMESTAMP without time zone;

it doesnt work, says:

    ERROR:cannot cast type time without time zone to timestamp without time zone.

P.S. If its possible, I would like to avoid dropping columns because I already use indexes of those columns.

Upvotes: 8

Views: 17545

Answers (2)

user19312748
user19312748

Reputation: 11

Postgresql will not directly allow to convert time without zone to timestamp without time zone.

You just need to convert first into time without time zone to character varying then change to character varying to timestamp without time zone

You can refer the following query

  1. Changing to character varying:
ALTER TABLE table_name
ALTER COLUMN colum_name TYPE character varying
USING column_name::character varying
  1. Now to change to Timestamp without time zone:
ALTER TABLE table_name
ALTER COLUMN colum_name TYPE Timestamp without time zone
USING column_name::Timestamp without time zone

Now you will be able update successfully.

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

If you want the date part to be today:

alter table the_table
alter column the_column type timestamp without time zone 
using current_date + the_column

Upvotes: 14

Related Questions