From: | "Saha, Sushanta K" <sushanta(dot)saha(at)verizonwireless(dot)com> |
---|---|
To: | Victor Yegorov <vyegorov(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [E] Re: CSV From Oracle with timestamp column getting errors |
Date: | 2021-03-23 01:04:44 |
Message-ID: | CAHty+vNHPj-bKx96YmMHpV5iW44p2t1iXwjVpi-sTdmgey_Dbw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Awesome. Thanks Victor!
.... Sushanta
On Mon, Mar 22, 2021 at 4:47 PM Victor Yegorov <vyegorov(at)gmail(dot)com> wrote:
> пн, 22 мар. 2021 г. в 21:38, Saha, Sushanta K <
> sushanta(dot)saha(at)verizonwireless(dot)com>:
>
>> \COPY table1 FROM '/tmp/Oracle_2020_06.csv' DELIMITER ',' CSV HEADER;
>> ERROR: invalid input syntax for type timestamp: "01-JUN-20
>> 06.04.20.634000 AM"
>> CONTEXT: COPY table1, line 2, column last_update_timestamp: "01-JUN-20
>> 06.04.20.634000 AM"
>>
>> Appreciate any help with this psql command.
>>
>
> I would recommend issuing one of these on the Oracle side *before* taking
> the CSV snapshot.
> export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
>
> Otherwise, you have to load this CSV file in a table, that has `text` type
> for the column and do a post-processing,
> smth like:
>
> INSERT INTO permanent_tab
> SELECT *, to_timestamp(col, 'DD-MON-YY HH12.MI.SS.SSSSS AM') FROM
> temp_table;
>
> Hope this helps.
>
> --
> Victor Yegorov
>
--
*Sushanta Saha|*MTS IV-Cslt-Sys Engrg|WebIaaS_DB Group|HQ -
* VerizonWireless O 770.797.1260 C 770.714.6555 Iaas Support Line
949-286-8810*
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2021-03-23 01:11:55 | Re: Binary encoding of timetz type |
Previous Message | Michael Schanne | 2021-03-22 22:24:48 | MultiXactId wraparound and last aggressive vacuum time |