Обсуждение: 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