Received: from ga.prestige.net (dns2.prestige.net [208.220.88.11] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id WAA97512; Thu, 27 Jan 2000 22:53:46 -0500 (EST) (envelope-from johnbr@mindspring.com) Received: from jb1 [208.246.188.22] by ga.prestige.net (SMTPD32-6.00) id A29F574013C; Thu, 27 Jan 2000 22:53:03 -0500 Message-Id: <4.1.20000127224614.00c96190@pop.mindspring.com> X-Sender: (Unverified) X-Mailer: QUALCOMM Windows Eudora Pro Version 4.1 Date: Thu, 27 Jan 2000 22:48:39 -0500 To: Nicolas Huillard , "'pgsql-general@hub.org'" , "'pgsql-sql@hub.org'" From: John Brothers Subject: Re: [GENERAL] Problem with SELECT on large negative INT4 In-Reply-To: <01BF68CB.9C1C3F90@agen.int.ghs> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id WAC97609 We have the same problem with 64-bit integers - and it isn't just psql - the same problem exists in jdbc. Anyways, to fix it, we did the following: '-11233232322233232323232321232'::int8 whenever we did any operation on the large field. you may want to try '-2057496808|'::int4 in the same manner. At 01:37 PM 1/27/00 +0100, Nicolas Huillard wrote: >I have a DB with is updated using MS Access. Primary keys are Int4 with >default random values ("NuméroAuto" + "Aléatoire" in Access). >The DB is migrated as-is in Postgres, with tbl_prod.cle_prod field >containing values from -2057496808 to 2139583719. >When I SELECT in the table, using the INT4 cle_prod value, PG doesn't find >the tuple. When I SELECT using the VARCHAR(10) ref_prod value, PG finds the >tuple, and show the right value for the cle_prod filed : the same as the one >I SELECTed for... > >This sounds like the long negative integer values given in PSQL is not >converted correctly while executing. >Using a long positive integer value, all works like a charm... > >Below is the queries type sto sho what append. >I'm using Postgres 6.5.2 from the RPMs. > >Nicolas Huillard > > > > >gest=> select cle_prod, ref_prod from tbl_prod where ref_prod='4866VC'; > cle_prod|ref_prod >-----------+-------- >-2057496808|4866VC >(1 row) > >gest=> select cle_prod, ref_prod from tbl_prod where cle_prod = -2057496808; >cle_prod|ref_prod >--------+-------- >(0 rows) > >gest=> \d tbl_prod >Table = tbl_prod >+----------------------------------+----------------------------------+---- ---+ >| Field | Type | Length| >+----------------------------------+----------------------------------+---- ---+ >| cle_prod | int4 not null default nextval('" | 4 | >| ref_prod | varchar() not null | 10 | >... >+----------------------------------+----------------------------------+---- ---+ >Index: tbl_prod_pkey > >gest=> select version(); >version >-------------------------------------------------------------- >PostgreSQL 6.5.2 on i486-pc-linux-gnu, compiled by gcc 2.7.2.3 >(1 row) > >gest=> > > >************ ------------ johnbr@incanta.net