From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ron <ronljohnsonjr(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: COPY and custom datestyles. Or some other technique? |
Date: | 2023-03-29 23:31:21 |
Message-ID: | 9aa715ce-9485-638f-6567-e642a50699c4@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2023-03-29 23:40:49 | Re: COPY and custom datestyles. Or some other technique? |
Previous Message | Ron | 2023-03-29 23:24:36 | COPY and custom datestyles. Or some other technique? |