Thread

1. CSV From Oracle with timestamp column getting errors

   Saha, Sushanta K <sushanta.saha@verizonwireless.com> — 2021-03-22T20:37:20Z

   \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.
   
   Thanks & Regards
   .... Sushanta
   

2. Re: CSV From Oracle with timestamp column getting errors

   Saha, Sushanta K <sushanta.saha@verizonwireless.com> — 2021-03-22T20:42:02Z

   The table column definition:
   
             Column           |              Type              |
   ---------------------------+--------------------------------+
    last_update_timestamp     | timestamp(6) without time zone |
   
   Thanks & Regards
   .... Sushanta
   
   
   On Mon, Mar 22, 2021 at 4:37 PM Saha, Sushanta K <
   sushanta.saha@verizonwireless.com> wrote:
   
   > \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.
   >
   > Thanks & Regards
   > .... Sushanta
   >
   >
   
   -- 
   
   *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*
   

3. Re: CSV From Oracle with timestamp column getting errors

   Victor Yegorov <vyegorov@gmail.com> — 2021-03-22T20:47:08Z

   пн, 22 мар. 2021 г. в 21:38, Saha, Sushanta K <
   sushanta.saha@verizonwireless.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
   

4. Re: CSV From Oracle with timestamp column getting errors

   Tim Cross <theophilusx@gmail.com> — 2021-03-22T20:56:42Z

   "Saha, Sushanta K" <sushanta.saha@verizonwireless.com> writes:
   
   > \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"
   >
   
   The problem is psql doesn't understand/recognise the timestamp format
   being used in the CSV dump from Oracle.
   
   Modify the SQL used to extract the data from Oracle so that it formats
   the timestamp as a string which psql can parse into a timestamp type -
   for example ISO or any of the psql timestamp formats (see psql manual). 
   
   -- 
   Tim Cross
   
   
   
   

5. Re: [E] Re: CSV From Oracle with timestamp column getting errors

   Saha, Sushanta K <sushanta.saha@verizonwireless.com> — 2021-03-23T01:04:44Z

   Awesome. Thanks Victor!
   
   .... Sushanta
   
   
   On Mon, Mar 22, 2021 at 4:47 PM Victor Yegorov <vyegorov@gmail.com> wrote:
   
   > пн, 22 мар. 2021 г. в 21:38, Saha, Sushanta K <
   > sushanta.saha@verizonwireless.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*