Re: COPY and custom datestyles. Or some other technique?

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@aklaver.com