Problem with COPY FROM loading time datatype

[modexmail]

Hi all,

I'm trying to copy from CSV file into table. Let say, I got one csv file named test.csv that contains the following data:

1,2012/02/20 08:10:10:123 +08:00,Row1
2,2012/02/20 08:10:10:456 +08:00,Row2
3,2012/02/20 08:10:10:789 +08:00,Row3


I've created a table named test with the following SQL syntax:

CREATE TABLE map_test (
"ID" integer default 0,
"Start Date" date default CURRENT_DATE,
"Start Time" time default current_time,
"Row Number" varchar(255) default ' ' collate ucs_basic
)
with structure=vectorwise
\p\g


Currently I'm using this syntax to load the data:

SET DATE_FORMAT 'YMD';
COPY TABLE map_test (
"ID" = d0comma,
"Start Date" = 'c0 ',
"Start Time" = 'c8',
"Start Time Timezone" = d0comma, --ignoring this
"Row Number" = c0nl
)
FROM 'test.csv'
WITH ON_ERROR=CONTINUE
\p\g

The data can be loaded without any error. The problem is that I also want to add milliseconds into the time as I need more precision for that data. Whenever I change:

"Start Time" = 'c8', ---> "Start Time" = 'c0 ',
Error: ' 08:10:10:123' is an invalid format or value for time.


I know that for milliseconds, it needs to start with '.', where my data using ':'. Is there possibility to load this without changing the original csv file? The one I'm working with got > 500million lines which is a pain to convert them to HH:mm:ss.fff format.


Thanks,
modexmail

[fba]

I suppose your original csv file was not generated using Ingres/Vectorwise?

[kschendel]

How about loading the milliseconds into a work column and then doing an update of the time column? or load into a temp table with an integer milliseconds column, then an insert/select into the final table combining the time and milliseconds.

I can't imagine that it would be too hard to preprocess the input file with awk to replace that ':', and I'm pretty sure that's how I would do it.

[modexmail]

Thanks for reply.

Yes the csv was not generated by Ingres/Vectorwise. It came from different source.

Actually I can preprocess the file using sed. Doing something like this would help:

sed 's/\(:[0-9]\{2,2\}\)\(:\)\([0-9]\{3,3\} \+\)/\1.\3/g'

Its not hard but it will take more time by preprocessing it. Some files have around 100GB in size.

I guess the only choice is introducing new column for milliseconds then.

How about loading the milliseconds into a work column and then doing an update of the time column?

Thanks again.