PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Importing CSV file to Spanner Error (Parse Date)

I started to follow the official documentation of gcp in order to import my csv data to an instance on cloud spanner spanner, but I’m getting thhis error:

 *Error java.time.format.DateTimeParseException: Text '2024-05-30THH24:16:30.892' could not be parsed at index 10*

I’m using this command to generate the csv file:

psql -h localhost -U myuser -d mydatabase -c 
"Copy (SELECT id, TO_CHAR(created_at, 'YYYY-MM-DD"T"HH24:MI:SS.MS') AS created_at FROM mytable) To STDOUT With CSV DELIMITER ',';" > /mycarpet/.csv

The table has this configuration:

id character varying(10) NOT NULL,
created_by character varying(50),

On the configuration of the job I’m setting: dateFormat=yyyy-MM-dd hh:mm:s

Any idea, how I can solve this?

Tags:

2

Answers


  1. 0

    The problem seems to be that your export from PostgreSQL results in invalid data being generated. The import job complains that '2024-05-30THH24:16:30.892' is not a valid date/time string, which is obviously a correct conclusion. Your export seems to be writing the fixed string HH24 to the file, instead of the actual hour.

    I would recommend that you manually inspect:

    1. What is in your export file.
    2. What the output is when you just run SELECT id, TO_CHAR(created_at, 'YYYY-MM-DD"T"HH24:MI:SS.MS') AS created_at FROM mytable in psql.

    Tip: As you seem to try to copy data from a PostgreSQL database to a Spanner PostgreSQL database, you can also use PGAdapter to copy it directly without going through a csv file. See this example for how to do that.

    Login or Signup to reply.
  2. 0

    To expand on my comment:

    create table ts_test (id integer generated always as identity, ts_fld timestamptz);

insert into ts_test (ts_fld) 
select ts from 
generate_series('2024-08-17 08:31', '2024-08-18 10:01', '4 hrs 10 mins'::interval) as t(ts);

show datestyle;
 DateStyle 
-----------
 ISO, MDY

select * from ts_test;
 id |         ts_fld         
----+------------------------
  1 | 2024-08-17 08:31:00-07
  2 | 2024-08-17 12:41:00-07
  3 | 2024-08-17 16:51:00-07
  4 | 2024-08-17 21:01:00-07
  5 | 2024-08-18 01:11:00-07
  6 | 2024-08-18 05:21:00-07
  7 | 2024-08-18 09:31:00-07

set datestyle = 'SQL, MDY';

select * from ts_test;
 id |         ts_fld          
----+-------------------------
  1 | 08/17/2024 08:31:00 PDT
  2 | 08/17/2024 12:41:00 PDT
  3 | 08/17/2024 16:51:00 PDT
  4 | 08/17/2024 21:01:00 PDT
  5 | 08/18/2024 01:11:00 PDT
  6 | 08/18/2024 05:21:00 PDT
  7 | 08/18/2024 09:31:00 PDT

select to_char(ts_fld, 'YYYY-MM-DD"T" HH24:MI:SS.MS') from ts_test;
         to_char          
--------------------------
 2024-08-17T 08:31:00.000
 2024-08-17T 12:41:00.000
 2024-08-17T 16:51:00.000
 2024-08-17T 21:01:00.000
 2024-08-18T 01:11:00.000
 2024-08-18T 05:21:00.000
 2024-08-18T 09:31:00.000

    Timestamps are not stored formatted, that is something done on presentation to the user. You can change that presentation using DateStyle or by using the Data formatting function to_char(). If the data is going to be ingested by another program any common date format should work in getting the correct information copied over.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search