I am working on third party data which I need to load into my postgresql database. I am running into problems where sometimes I get the time '24:00:30' when it actually should be '00:00:30'. This rejects the data.
I tried to cast but it did not work.
insert into stop_times_test trip_id, cast(arrival_time as time), feed_id, status
from external_source;
Is there any way to convert to the correct one internally?
This may work for your case:
> select '0:0:0'::time + '24:00:30'::interval;
00:00:30
Cast to interval, then cast to time:
SELECT '24:00:30'::interval::time
If you want to bulk load the data with COPY or mass INSERT make the target column data type interval and convert it to time later. This works out of the box:
ALTER TABLE mytable ALTER col1 TYPE time;
No, there is no magic way of doing it. No cast will help you. 24:00:30 is an invalid time. Period.
You could try adding that value on a varchar and then using regular expressions to update the right values and insert them on the right columns. This sort of things happen a lot when doing data transformation.
