Обсуждение: Copy from csv - timestamp issue

Hi all

First post here from a total postgresql newbie.  Many years working on SQL
Server, but ...

I'm trying to copy in some csv data.

My first column is, confusingly, called datetime and has datatype timestamp
without time zone.
This is followed by flow_m3hr (real), sitename (integer) and pmac (integer).

Here's my command:

ST_mpcc=# COPY st_mpcc_data FROM 'C:\blahblah\ST_Mpcc_CSV_sample.csv';

and here's my error:

ERROR:  invalid input syntax for type timestamp: "2008-07-11
03:00:00,0,10545079
,26739"
CONTEXT:  COPY st_mpcc_data, line 1, column datetime: "2008-07-11
03:00:00,0,105
45079,26739"

Does this imply it's trying to place the entire row of data into the first
field?

If so, how do I prevent it?

If not, what else is wrong?

Thanks for your help

Ian



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Copy-from-csv-timestamp-issue-tp5780855.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

IanHingley wrote:
> First post here from a total postgresql newbie.  Many years working on SQL
> Server, but ...
> 
> I'm trying to copy in some csv data.
> 
> My first column is, confusingly, called datetime and has datatype timestamp
> without time zone.
> This is followed by flow_m3hr (real), sitename (integer) and pmac (integer).
> 
> Here's my command:
> 
> ST_mpcc=# COPY st_mpcc_data FROM 'C:\blahblah\ST_Mpcc_CSV_sample.csv';
> 
> and here's my error:
> 
> ERROR:  invalid input syntax for type timestamp: "2008-07-11
> 03:00:00,0,10545079
> ,26739"
> CONTEXT:  COPY st_mpcc_data, line 1, column datetime: "2008-07-11
> 03:00:00,0,105
> 45079,26739"
> 
> Does this imply it's trying to place the entire row of data into the first
> field?
> 
> If so, how do I prevent it?

If you want to import CSV, you must tell COPY that because the default
format is different.

COPY ... FROM ... (FORMAT csv);

or, on older releases,

COPY ... FROM ... CSV

Yours,
Laurenz Albe

Hi Laurenz

Fantastic!  As simple as that.

I had a few 'null' issues to deal with, but once that was done my 1000 row
sample csv copied in instantly.

I've started another copy with 99 million rows, so fingers crossed!

Thanks again,

Ian



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Copy-from-csv-timestamp-issue-tp5780855p5780873.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.