From: Craig Longman <craigl@begeek.com>
To: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Subject: casts and conversions
Date: 16 Jun 2001 01:07:29 -0400
Message-ID: <992668049.2128.5.camel@jigra.begeek.com> (raw)
i have a problem in a table with the following columns:
...
probability | double precision |
unitcost | numeric(15,2) |
...
when i try and do the following sql:
select (unitcost*probability) from oppproducttype
i get the following error:
ERROR: Unable to identify an operator '*' for types 'numeric' and
'float8'
You will have to retype this query using an explicit cast
is this kind of thing a regular thing for postgresql? it seems that to
postgres, everything (even operators) are methods that take (and only
take) declared parameters, and postgres also seems strangely shy at
doing relatively straightforward conversions. how does one handle this?
we are getting our product running on postgresql ( running very well
indeed btw, congratulations ) and can't really afford to ship our own
version of postgresql that has the required parameterized operator
methods installed. nor is it possible (or realistic really) to manually
cast these things. all the sql is intentionally generic and runs on
many different databases, as well as all the sql being dynamically
generated. figuring out when postgresql is going to have a problem
doing the math would be difficult.
the only solution i can think of, if it is possible, is to have a script
that the client would need to run to declare the missing parameter
combinations for the standard operators. this isn't very desireable,
but it is the only real solution i can see so far. any other
suggestions?
why does this happen? i have run into this before, and was able to
perform the explicit cast (the code was postgresql only), but it seems
like this is always going to be a problem, unless i'm missing something.
are there plans for more thorough dynamic-casting logic, or does one
just need to go through all the combinations of datatypes and make sure
that every combination is entered in the operator mapping tables? i
guess it would need to be entered both ways also ( int*float8,
float8*int )?
explanations and/or suggestions greatly appreciated.
--
CraigL->Thx();
Be Developer ID: 5852
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: craigl@begeek.com
Subject: Re: casts and conversions
In-Reply-To: <992668049.2128.5.camel@jigra.begeek.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox