Re: "Bad date external representation"

on 6/18/03 10:40 PM, Tom Lane at tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:

>> ERROR: copy: line 1, Bad date external representation '9/19/2002'
>
> That date as given is perfectly good:
>
> regression=# select '9/19/2002'::date;
> date
> ------------
> 2002-09-19
> (1 row)
>
> I suspect that the problem is whitespace that is not showing in your
> representation of the error message. A likely bet is that the dump
> file has been Microsoft-ized, with newlines converted into carriage
> return and linefeed ... the CR character will be taken as part of the
> data by COPY.

The file I'm trying to copy from has actually not been exposed to any
Microsoft products whatsoever - it was exported from FileMaker Pro on a Mac
and brought into BBEdit (a plain-text editor geared toward programmers) for
editing. I changed it to pipe-delimited in BBEdit, and there's no whitespace
showing anywhere, so I don't think that's it.

Bad line returns did seem like a possibility, so I checked and found that it
was in fact set to Mac rather then UNIX line returns, but when I changed
that, replaced all line returns in the document, and even ran Zap Gremlins
(BBEdit's way of getting rid of formatting junk from other programs) to get
rid of any other weirdness, I still got the same error.

>Is the error message strangely formatted at all?

Not as far as I can tell, except for having a backslash and a period on the
line above it - not sure what that means. The full text of what I told it to
do, and what it said in return (minus the prompt as that makes the line too
long for e-mail), is as follows:

\copy exhibitions from 'exhibitions2.txt' using delimiters '|'
\.
ERROR: copy: line 1, Bad date external representation '9/19/2002'
lost synchronization with server, resetting connection

Any other ideas?

Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org