Thread: NUMERIC and arithmetics
Hello,
in PG 7.1.x the NUMERIC data type makes some trouble with arithmetic and comparison
operators:
delete from produkt where preis > 3.00;
yields
ERROR: Unable to identify an operator '>' for types 'numeric' and 'float8'
preis is typed NUMERIC(8,2). Strangely, "preis > 3" works.
Is this fixed in version 7.2?
Thanks,
Christoph Dalitz
On Thu, Sep 19, 2002 at 02:27:21PM +0200, Christoph Dalitz wrote: > Hello, > > in PG 7.1.x the NUMERIC data type makes some trouble with arithmetic and comparison > operators: > > delete from produkt where preis > 3.00; > > yields > > ERROR: Unable to identify an operator '>' for types 'numeric' and 'float8' > > preis is typed NUMERIC(8,2). Strangely, "preis > 3" works. > > Is this fixed in version 7.2? If you do: delete from produkt where preis > '3.00'; it works fine. I'm thinking we could save ourselves a lot of hassle by requiring all constants to be quoted :) Not gonna happen. Oh well. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
On Thu, 19 Sep 2002 22:57:28 +1000 Martijn van Oosterhout <kleptog@svana.org> wrote: > > > > ERROR: Unable to identify an operator '>' for types 'numeric' and 'float8' > > > > preis is typed NUMERIC(8,2). Strangely, "preis > 3" works. > > > > If you do: > > delete from produkt where preis > '3.00'; > > it works fine. I'm thinking we could save ourselves a lot of hassle by > requiring all constants to be quoted :) > This is strange. I thought '3.00' would be a string constant, ie. of type VARCHAR. In Oracle 3.00 is a number constant and '3.00' a string constant which needs to be explicitly converted with to_number(). Does this mean that string constants in PostgreSQL are implicitly cast to numeric when '>' is used? Or is preis implicitly cast to VARCHAR and the comparison is not done numeric but according to the ASCII table? Christoph Dalitz
On Thu, Sep 19, 2002 at 03:25:16PM +0200, Christoph Dalitz wrote:
> On Thu, 19 Sep 2002 22:57:28 +1000
> Martijn van Oosterhout <kleptog@svana.org> wrote:
> > If you do:
> >
> > delete from produkt where preis > '3.00';
> >
> > it works fine. I'm thinking we could save ourselves a lot of hassle by
> > requiring all constants to be quoted :)
> >
> This is strange. I thought '3.00' would be a string constant, ie. of type
> VARCHAR. In Oracle 3.00 is a number constant and '3.00' a string constant
> which needs to be explicitly converted with to_number().
>
> Does this mean that string constants in PostgreSQL are implicitly cast to
> numeric when '>' is used? Or is preis implicitly cast to VARCHAR and the
> comparison is not done numeric but according to the ASCII table?
I have no idea about Oracle, but a quoted constant is defined by postgres as
type 'unknown'. This type will be auto-cast to any type works. So, if you
say:
select 5 + '3';
Postgres will cast the 3 to an integer. But if you say:
select 3.5 + '3';
it's will be cast to a float. And if you say:
select 'hello' || '3';
Both will be treated as strings. It will cause ambiguities such as in:
# select date_part('month','2002-02-01');
ERROR: Function 'date_part(unknown, unknown)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
But with one cast:
# select date_part('month','2002-02-01'::date);
date_part
-----------
2
(1 row)
It works. If you do a \df date_part you can see that the cast resolved the
ambiguity and coerced the first argument to a string.
Anyway, I hope this explains things.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
Christoph Dalitz <christoph.dalitz@hs-niederrhein.de> writes:
> ERROR: Unable to identify an operator '>' for types 'numeric' and 'float8'
> Is this fixed in version 7.2?
No, but 7.3 fixes it.
regards, tom lane