ALTER TABLE with USING clause for timestamp
Hi,
I have a column in a table defined as type TEXT and I'd like to
convert to a TIME type - I tried this:
ALTER TABLE t1 ALTER COLUMN tcol TYPE TIME WITHOUT TIME ZONE;
which throws:
ERROR: column "tcol" cannot be cast to type "pg_catalog.time"
From reading the ALTER TABLE documents there appears to be a USING
clause that I could use to do the cast manually, but the examples are
for converting integers to timestamps and my google-fu isn't giving
me any help. Any ideas on how to convert from text to time without
a drop/add? The data is in HH:MM:SS format.
PG 8.1
Thanks,
Josh
Josh Trutwin <josh@trutwins.homeip.net> writes:
I have a column in a table defined as type TEXT and I'd like to
convert to a TIME type - I tried this:
ALTER TABLE t1 ALTER COLUMN tcol TYPE TIME WITHOUT TIME ZONE;
which throws:
ERROR: column "tcol" cannot be cast to type "pg_catalog.time"
You just need to cast it explicitly --- to prevent surprises, ALTER
COLUMN TYPE won't implicitly do anything there's no implicit cast for.
ALTER TABLE t1 ALTER COLUMN tcol TYPE TIME USING tcol::TIME;
regards, tom lane