Search code examples
postgresqltimestampalter-table

Change column Timezone to Time

By mistake, a table was set up having a column type Timestamp without time zone, where type Time will be needed. I tried

ALTER TABLE "Scheme"."Table" ALTER COLUMN "Length" TYPE _time USING "Length"::_time;

and got an ERROR: cannot cast type timestamp without time zone to time without time zone

Solution

  • _time isn't a valid Postgres data type. You need to use time 

    ALTER TABLE "Scheme"."Table" 
  ALTER COLUMN "Length" TYPE time USING "Length"::time;

    Online example