Tom you are a legend! There were invisible characters at the start of
the file which were not being shown with "head" and "cat". Removing
them fixed the issue and copy worked like a charm. Thank you!
On Sun, Mar 3, 2013 at 3:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Sarfraz Nawaz <sarfraz@gmail.com> writes:
>> However, when I try to import data into "durations" table from a CSV
>> file, I get this error which is puzzling me because the date in the
>> CSV file is in the same DMY format that the db should be expecting. I
>> have also tried by giving "durations(startdate, enddate, ....)" format
>> as well but same result.
>
>> mydb=> \copy durations FROM 'myfile.csv' DELIMITER ',' CSV;
>> ERROR: invalid input syntax for type date: "12/09/2011"
>> CONTEXT: COPY durations, line 1, column startdate: "12/09/2011"
>
> That certainly *looks* right, and it does work in testing here.
> However, since this is the first column of the first line of the
> file, my bet is that you've got some invisible characters at the
> start of the file that are screwing up COPY. Text editors that
> think they should put UTF8 "BOM" marks into text files are a
> frequent cause of this on Windows.
>
> regards, tom lane