Received: from jennifer.emc.com.ph ([203.167.64.3]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eAP7MNA89021 for ; Sat, 25 Nov 2000 02:22:28 -0500 (EST) (envelope-from pgsql-sql@fc.emc.com.ph) Received: from fc.emc.com.ph (unknown [203.167.64.16]) by jennifer.emc.com.ph (Postfix) with ESMTP id B1DF0D72F; Sat, 25 Nov 2000 15:22:19 +0800 (PST) Message-id: Date: Sat, 25 Nov 2000 15:22:15 +0800 Subject: Re(2): 7.0.3 BUG To: tgl@sss.pgh.pa.us Cc: pgsql-sql@postgresql.org From: "pgsql-sql" MIME-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200011/232 X-Sequence-Number: 263 tgl@sss.pgh.pa.us writes: >"pgsql-sql" writes: >> migrate=# select userid from users where userid = 'reynan@yahoo.com'; >> userid >> --------------------- >> reynan@yahoo.com >> admin >> (2 rows) > >That's a tad, um, startling :-( > >However, you haven't given us nearly enough information to have a shot >at figuring out what's going on. > > regards, tom lane The query used indexscan. I tried using trim in userid and I got something like ... migrate=# select userid from users where trim(userid) = 'reynan@yahoo.com'; userid --------------------- reynan@yahoo.com (1 row) I thought it was a varchar problem ('coz userid is varchar) of 7.0.3 so I changed to 7.0.2. Is it really a varchar bug? Anyway, I dumped all the database. When I was restoring it in 7.0.2 I got these ... You are now connected as new user root. CREATE You are now connected as new user pgsql. ERROR: copy: line 3910, Bad timestamp external representation '2000-01-05 00:00:60.00+08' PQendcopy: resetting connection ERROR: copy: line 3910, Bad timestamp external representation '2000-01-05 00:00:60.00+08' PQendcopy: resetting connection ERROR: copy: line 302, Bad timestamp external representation '2000-02-29 00:00:60.00+08' PQendcopy: resetting connection ERROR: copy: line 13, Bad timestamp external representation '1970-01-01 08:04:60.00+08' PQendcopy: resetting connection ERROR: copy: line 24, Bad timestamp external representation '1970-01-01 08:04:60.00+08' PQendcopy: resetting connection You are now connected as new user root. ERROR: copy: line 2, Bad timestamp external representation '1999-12-24 00:00:60.00+08' PQendcopy: resetting connection You are now connected as new user pgsql. CREATE I took a look at the database named 'migrate' (this is where the error occured), the tables were all empty. Most of the tables have field like this... createdate | timestamp | not null default now() Weird because those timestamps were generated by default now(). Is this an another bug (timestamp bug)? Take a look at this ... migrate=# select version(); version --------------------------------------------------------------- PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.3 (1 row) migrate=# select '2000-01-05 00:00:60.00+08'::timestamp; ERROR: Bad timestamp external representation '2000-01-05 00:00:60.00+08' migrate=# select '2000-11-25 14:05:23.00+08'::timestamp; ?column? --------------------------- 2000-11-25 14:05:23.00+08 (1 row) migrate=# create table testing (datetime timestamp); CREATE migrate=# insert into testing values('2000-01-05 00:00:60.00+08'); ERROR: Bad timestamp external representation '2000-01-05 00:00:60.00+08' migrate=# insert into testing values('2000-11-25 14:05:23.00+08'); INSERT 6834235 1 migrate=# Thanks, sherwin