NUMERIC and arithmetics

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/

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/

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