X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B411F3A432A for ; Wed, 10 Nov 2004 21:40:42 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29754-09 for ; Wed, 10 Nov 2004 21:40:32 +0000 (GMT) Received: from mta1.co.kern.ca.us (mta1.co.kern.ca.us [206.169.45.188]) by svr1.postgresql.org (Postfix) with ESMTP id 0DBE83A434C for ; Wed, 10 Nov 2004 21:40:33 +0000 (GMT) Received: from kc.co.kern.ca.us (kc.co.kern.ca.us [206.169.45.183]) by mta1.co.kern.ca.us (Postfix) with SMTP id 8955D11535C for ; Wed, 10 Nov 2004 13:40:54 -0800 (PST) Received: from no.name.available by kc.co.kern.ca.us via smtpd (for mta1.co.kern.ca.us [206.169.45.188]) with SMTP; Wed, 10 Nov 2004 13:42:22 -0800 Received: from KERNMAIL-Message_Server by co.kern.ca.us with Novell_GroupWise; Wed, 10 Nov 2004 13:40:33 -0800 Message-Id: X-Mailer: Novell GroupWise Internet Agent 5.5.7.1 Date: Wed, 10 Nov 2004 13:40:02 -0800 From: "Sharon Schooley" To: Subject: Bad Timestamp Error with COPY Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=_F0D02EC1.94F5F89C" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200411/93 X-Sequence-Number: 15478 This is a MIME message. If you are reading this text, you may want to consider changing to a mail reader or gateway that understands how to properly handle MIME multipart messages. --=_F0D02EC1.94F5F89C Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable I am new to Postgresql. I'm trying to load tables from SQLServer bcp = export, comma delimited. I'm loading datetime datatype into timestamp. = I've loaded one table successfully and failed on others with the same = error:=20 Bad timestamp external representation ''. The table that loaded successfully had datetime data in the exact same = format as this one. I have taken this file apart and successfully loaded it one or = two fields at a time, including the datetime fields (into timestamp) but = it will not load as is. I've changed the delimiter to \t and tried \. at = the end. I've changed the field types from timestamp to char(25) and it = loaded fine. I tried to create another table and cast to timestamp from = char. That's not allowed.=20 Any help will be appreciated. Here is the version, the table description, the COPY stmt with error, and = the input file(it's only one row). =20 ems=3D# select version(); PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 = (SuSE Linux) ems=3D# \d edadvisor_char; Table "public.edadvisor_char" =20 advisor1 | character varying(60) =20 whenent1 | timestamp without time zone=20 advisor2 | character varying(60)=20 whenent2 | timestamp without time zone=20 advisor3 | character varying(60) whenent3 | timestamp without time zone=20 advkey | character(1) =20 whenchange | timestamp without time zone bhhwhenchange | timestamp without time zone=20 bmhwhenchange | timestamp without time zone=20 kmcwhenchange | timestamp without time zone=20 merwhenchange | timestamp without time zone=20 sjhwhenchange | timestamp without time zone=20 ems=3D# copy edadvisor_char from '/home/xxxxxx/test_data.txt' using = delimiters ','; ERROR: copy: line 1, Bad timestamp external representation '' ems=3D#=20 ****** test_data.txt ******** Off E.D. Rotation,2004-09-01 00:00:01.000,testing,2004-09-01 00:00:06.000,,= ,1,2004-09-02 12:56:43.000,2004-08-05 15:13:10.000,2004-08-05 14:29:01.000,= 2004-08-05 14:57:32.000,2004-08-05 13:49:46.000,2004-08-06 14:05:07.000 --=_F0D02EC1.94F5F89C Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Description: HTML
I am new to Postgresql. I'm trying to load tables from SQLServer = bcp=20 export, comma delimited.  I'm loading datetime datatype into = timestamp.=20 I've loaded one table successfully and failed on others with the same = error:=20
Bad timestamp external representation ''.
 
The table that loaded successfully had datetime data in the exact = same=20 format as
this one. I have taken this file apart and successfully = loaded it=20 one or two fields at a time, including the datetime fields (into timestamp)= but=20 it will not load as is. I've changed the delimiter to \t and tried \. at = the=20 end.   I've changed the field types from timestamp to char(25) = and it=20 loaded fine. I tried to create another table and cast to timestamp from=20 char.  That's not allowed. 
 
Any help will be appreciated.
 
Here is the version, the table description, the COPY stmt with = error,=20 and the input file(it's only one row). 
 
ems=3D# select version();
PostgreSQL 7.3.4 on i686-pc-linux-gnu, = compiled=20 by GCC gcc (GCC) 3.3.1 (SuSE Linux)
ems=3D# \d edadvisor_char;
Table=20 "public.edadvisor_char"
 
 advisor1    =         =20 | character=20 varying(60)       
 whenent1&nbs= p;         =20 | timestamp without time=20 zone 
 advisor2        = ;    =20 | character=20 varying(60) 
 whenent2      &nbs= p;   =20 | timestamp without time=20 zone 
 advisor3        = ;    =20 | character=20 varying(60)
 whenent3       &nbs= p;   |=20 timestamp without time=20 zone 
 advkey        &= nbsp;     =20 |=20 character(1)          &nb= sp;   
 whenchange       | timestamp = without=20 time zone
 bhhwhenchange  | timestamp without time=20 zone 
 bmhwhenchange | timestamp without time=20 zone 
 kmcwhenchange | timestamp without time=20 zone 
 merwhenchange  | timestamp without time=20 zone 
 sjhwhenchange   | timestamp without time = zone=20
 

ems=3D# copy edadvisor_char from '/home/xxxxxx/test_data.txt' = using=20 delimiters ',';
ERROR:  copy: line 1, Bad timestamp external=20 representation ''
ems=3D#
 
****** test_data.txt ********
Off E.D. Rotation,2004-09-01 00:00:01.000,testing,2004-09-01=20 00:00:06.000,,,1,2004-09-02 12:56:43.000,2004-08-05 15:13:10.000,2004-08-05= =20 14:29:01.000,2004-08-05 14:57:32.000,2004-08-05 13:49:46.000,2004-08-06=20 14:05:07.000
 
 
--=_F0D02EC1.94F5F89C--