Re: NUMERIC and arithmetics

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(at)svana(dot)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(at)svana(dot)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.