Thread: "Bad date external representation"
Hi folks, I just got the following error when attempting to use the \copy command in psql to import data from a text file into a PostgreSQL table: ERROR: copy: line 1, Bad date external representation '9/19/2002' I tried setting the datestyle to SQL and US using "set datestyle", but it didn't help. Anyone have any idea what the problem might be? Thanks, Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
Lynna Landstreet <lynna@gallery44.org> writes:
> 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. Is the error message strangely formatted at all?
regards, tom lane
on 6/18/03 10:40 PM, Tom Lane at tgl@sss.pgh.pa.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
Some additional information with regard to this issue, in case it helps any.
/* --------------------------------------------------------
Table structure for table "exhibitions"
-------------------------------------------------------- */
CREATE TABLE "exhibitions" (
"exhibition_id" int4 DEFAULT
nextval('"exhibitions_exhibition_id_seq"'::text) NOT NULL,
"exhibition_name" varchar(128) NOT NULL,
"start_date" date NOT NULL,
"end_date" date NOT NULL,
"curator" varchar(128),
"description" varchar(1024) NOT NULL,
"created_date" date NOT NULL,
"created_by" varchar(32) NOT NULL,
"modified_date" date NOT NULL,
"modified_by" varchar(32) NOT NULL,
CONSTRAINT "exhibitions_pk" PRIMARY KEY ("exhibition_id")
);
And the first line from the data file I was trying to import:
100|Wild Writing|9/19/2002|10/26/2002|Leslie Thompson|Kroeger enters the
landscape armed with a notebook and pencil for these nature-based images.
Using graph paper, the artist makes graphite rubbings from tree bark, which
has seen the passage of various beetle type insects. The paths, which are
left in their wake, appear like so many strange prehistoric symbols, once
Kroeger has done his magic in the darkroom. Printed as colour photograms of
relatively large scale, these works simultaneously reference moonscapes,
cave paintings, and microcosms.|2/4/2003|Lynna Landstreet|2/14/2003|Lynna
Landstreet
Don't know if that helps any...
Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org
Hi, Im finding a lot of "[local] idle in transaction" listings for postgres when i ps -auwwx|grep postg They are using memory and cpu time most are in the 20:00 time range. Is this normal? I am using mod_perl and I wonder if there are some scripts that aren't disconnecting from the database. Thanks for any help. -Chris
Lynna Landstreet <lynna@gallery44.org> writes:
> Some additional information with regard to this issue, in case it helps any.
Fooling around with this, I discovered that the datestyle setting *does*
make a difference --- which is surprising considering that this
particular date is quite non-ambiguous. (There's a big flamewar in
progress right now on pgsql-general that presumes the present behavior
is to accept anything that is non-ambiguous. Looks like we don't have
all our facts straight :-()
regression=# set datestyle = 'iso, us';
SET
regression=# select '9/19/2002'::date;
date
------------
2002-09-19
(1 row)
regression=# set datestyle = 'iso, euro';
SET
regression=# select '9/19/2002'::date;
ERROR: Bad date external representation '9/19/2002'
So the answer is probably to make sure you have the US sub-mode
selected.
regards, tom lane
on 6/18/03 7:37 PM, Lynna Landstreet at lynna@gallery44.org wrote: > I just got the following error when attempting to use the \copy command in > psql to import data from a text file into a PostgreSQL table: > > ERROR: copy: line 1, Bad date external representation '9/19/2002' > > I tried setting the datestyle to SQL and US using "set datestyle", but it > didn't help. Anyone have any idea what the problem might be? Well... turns out my initial intuition was half correct, anyway. It *was* a US vs European datestyle issue! When I reversed the month and day, it worked fine. What I can't figure out is why saying SET DATESTYLE TO 'US'; in psql didn't work. That was the *first* thing I tried! Oh well. At least I know how to fix the problem - I just have to change the date formats before exporting the records. The fact that I now feel like a complete moron for not trying reversing the month and day much sooner is beside the point, really... :-/ Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
So it is only the european setting that doesn't swap month/day? This is getting confusing. --------------------------------------------------------------------------- Tom Lane wrote: > Lynna Landstreet <lynna@gallery44.org> writes: > > Some additional information with regard to this issue, in case it helps any. > > Fooling around with this, I discovered that the datestyle setting *does* > make a difference --- which is surprising considering that this > particular date is quite non-ambiguous. (There's a big flamewar in > progress right now on pgsql-general that presumes the present behavior > is to accept anything that is non-ambiguous. Looks like we don't have > all our facts straight :-() > > regression=# set datestyle = 'iso, us'; > SET > regression=# select '9/19/2002'::date; > date > ------------ > 2002-09-19 > (1 row) > > regression=# set datestyle = 'iso, euro'; > SET > regression=# select '9/19/2002'::date; > ERROR: Bad date external representation '9/19/2002' > > So the answer is probably to make sure you have the US sub-mode > selected. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073