Problems with numeric (10,2) and big Decimal - Mailing list pgsql-jdbc

Thomas,

Ok,

I now know what is going on...

The select

davec=# select id from savingsaccount where balance between 1.1 and 5.1;

Returns an error

ERROR:  Unable to identify an operator '>=' for types 'numeric' and
'float8'
        You will have to retype this query using an explicit cast

Even in psql.

The problem is that postgres can't figure out if 1.1 is a float or a
numeric.

On my version of the jdk my BigDecimal will

Turn the query into

query: select id from savingsaccount where balance between
1.100000000000000088817841970012523233890
533447265625 and 5.0999999999999996447286321199499070644378662109375


Which seems to work

It also changes 1.0 and 5.0 into 1 and 5 respectively.

Which also works


So the solution is to change jdk's ( I use the latest from Sun) or
change the query to

select id from savingsaccount where balance between cast(1.1 as
numeric(10,2)) and cast(5.1 as numeric (10,2));


Cheers,

Dave



-----Original Message-----
From: Thomas Møller Andersen [mailto:tma@tmaonline.dk]
Sent: Wednesday, December 05, 2001 1:07 PM
To: dave@fastcrypt.com
Subject: SV: [JDBC] Exception?


Ok, thank you again.

Thomas

-----Oprindelig meddelelse-----
Fra: Dave Cramer [mailto:dave@fastcrypt.com]
Sendt: 5. december 2001 19:04
Til: tma@tmaonline.dk
Emne: RE: [JDBC] Exception?


Thomas,

From what I see you are doing everything ok, I have the logs off of my
machine, and your selects fail and mine work. I am going to send this
over to the hackers list

Dave

-----Original Message-----
From: Thomas Møller Andersen [mailto:tma@tmaonline.dk]
Sent: Wednesday, December 05, 2001 12:39 PM
To: dave@fastcrypt.com
Subject: SV: [JDBC] Exception?


I made a simple regular Java-program and got this message.

SQLException: ERROR:  Unable to identify an operator '>=' for types
'numeric' and 'float8' You will have to retype this query using an
explicit cast
SQLState: null
VendorError: 0

What am I doing wrong then?
I have attached the program (it's a bit spaghetti but should be ok for
testing).

Thomas

-----Oprindelig meddelelse-----
Fra: Dave Cramer [mailto:dave@fastcrypt.com]
Sendt: 5. december 2001 18:17
Til: tma@tmaonline.dk
Emne: RE: [JDBC] Exception?


Thomas,

Ok, you s/b able to connect to it. Let me know what the result is

Dave

-----Original Message-----
From: Thomas Møller Andersen [mailto:tma@tmaonline.dk]
Sent: Wednesday, December 05, 2001 12:09 PM
To: dave@fastcrypt.com
Subject: SV: [JDBC] Exception?


Alright, thanks.

It's 217.157.194.159

Thomas

-----Oprindelig meddelelse-----
Fra: Dave Cramer [mailto:dave@fastcrypt.com]
Sendt: 5. december 2001 17:52
Til: tma@tmaonline.dk
Emne: RE: [JDBC] Exception?


I just created your table exactly and retried with no luck.

Can you tell me your ip, and I will allow you to connect to my database
to test this.

The db is on alpha.ebox.com and the dbname is davec, you can use davec
as a username with no password.

Once I have your ip, I can add it to pg_hba.conf and it will let you in.

There are three values in the table so a query between 1.00 and 5.00
will get you 3 records back

Dave

-----Original Message-----
From: Thomas Møller Andersen [mailto:tma@tmaonline.dk]
Sent: Wednesday, December 05, 2001 11:35 AM
To: dave@fastcrypt.com
Subject: SV: [JDBC] Exception?


CREATE TABLE "savingsaccount" (
   "id" varchar(3) NOT NULL,
   "firstname" varchar(24),
   "lastname" varchar(24),
   "balance" numeric(10,2),
   CONSTRAINT "pk_savings_account" PRIMARY KEY ("id")
);

-----Oprindelig meddelelse-----
Fra: Dave Cramer [mailto:dave@fastcrypt.com]
Sendt: 5. december 2001 17:25
Til: tma@tmaonline.dk
Emne: RE: [JDBC] Exception?


Thomas,

Can you send me the table definition

Ie a pg_dump -s -t table db

This is bothersome. The jdbc driver really doesn't touch the sql, it
just passes it on to the backend, and usually if psql can do the query,
then the driver has no problem. I really would like to get down to the
bottom of this.

Dave

-----Original Message-----
From: Thomas Møller Andersen [mailto:tma@tmaonline.dk]
Sent: Wednesday, December 05, 2001 11:15 AM
To: dave@fastcrypt.com
Subject: SV: [JDBC] Exception?


Dave,

First of all thank you for being so helpful.
I changed the sql to use <= and >= but I still get the same exception. I
really don't understand it. I guess I will have to get the server
upgraded to 7.1.3 and then try again. But shouldn't this basic
functionality work on 7.1.2?

Thomas

-----Oprindelig meddelelse-----
Fra: Dave Cramer [mailto:dave@fastcrypt.com]
Sendt: 4. december 2001 23:48
Til: tma@tmaonline.dk
Emne: RE: [JDBC] Exception?


Thomas,

I used your code verbatim, and it worked ok. I wonder if this is an
issue with 7.1.2 ?

Can you change the query to the <= and >= and explicitly cast? There
won't be a difference in speed?

Dave
-----Original Message-----
From: Thomas Møller Andersen [mailto:tma@tmaonline.dk]
Sent: Tuesday, December 04, 2001 10:01 AM
To: Dave@micro-automation.net
Subject: SV: [JDBC] Exception?


Dave,

I'm using postgres 7.1.2 running on another machine which i cannot
upgrade. I got the newest source from CVS yesterday from where I build
the driver. The code is from an Entity Bean running on a Java
J2EE-server(1.3.1 FCS). Mayby it somehow is because of the Enterprise
Environment.

Thomas

-----Oprindelig meddelelse-----
Fra: Dave Cramer [mailto:Dave@micro-automation.net]
Sendt: 4. december 2001 02:17
Til: tma@tmaonline.dk; pgsql-jdbc@postgresql.org
Emne: RE: [JDBC] Exception?


Thomas,

I just tried this with the latest version of the jdbc driver, and
postgres 7.1.3 without any problems.

What versions are you running

Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Thomas Møller
Andersen
Sent: Monday, December 03, 2001 1:35 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Exception?


Does anyone have an explanation to the following exception:

Unable to identify an operator '>=' for types 'numeric' and 'float8 You
will have to retype this query using an explicit cast

The column is: numeric(10,2)

The query is from this method:

private Collection selectInRange(BigDecimal low, BigDecimal high) throws
SQLException {
      String selectStatement =
            "select id from mytable " +
            "where amount between ? and ?";

      PreparedStatement prepStmt =
con.prepareStatement(selectStatement);

      prepStmt.setBigDecimal(1, low);
      prepStmt.setBigDecimal(2, high);
      ResultSet rs = prepStmt.executeQuery();
      ArrayList a = new ArrayList();

      while (rs.next()) {
         String id = rs.getString(1);
         a.add(id);
      }

      prepStmt.close();
      return a;
}


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)