On 3/29/23 16:31, Adrian Klaver wrote:
On 3/29/23 16:24, Ron wrote:
Postgresql 13.10
$ psql -h myhost.example.com -X dba \
-c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH
DELIMITER '|';"
ERROR: date/time field value out of range: "2013061914122501"
CONTEXT: COPY t_id_master, line 1, column update_timestamp:
"2013061914122501"
The timestamp format generated by a legacy dbms is YYYYMMDDHHmmSSCC
(year to centisecond, with no delimiters).
Is there any way to convince Postgresql to import these fields?
One option:
1) Import into staging table as varchar field.
2) Use to_timestamp()(NOTE change in template pattern) from here:
https://www.postgresql.org/docs/current/functions-formatting.html
select to_timestamp('2013061914122501', 'YYYYMMDDHH24miSSCC');
to_timestamp
-------------------------
06/19/2013 14:12:25 PDT
Actually it probably should be:
select to_timestamp('2013061914122501', 'YYYYMMDDHH24miSSMS');
to_timestamp
----------------------------
06/19/2013 14:12:25.01 PDT
to move the data into final table.
There are 550+ tables, so something that I can do once on this end
would make my life a lot easier.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
