Tom Larard <larard@cs.umb.edu> writes:
Just for reference these two cases are interpreted in ways that may not be
intuitive for programmers. Most languages treat constants that look like 'foo'
as string constants and then have rules for how string constants get cast.
Postgres has string datatypes but 'foo' isn't necessarily a string data type.
It's of unknown type. It default to being cast to a string in some cases but
if it's used in arithmetic or function calls it can be cast to whatever's
necessary.
> prod=> update tmp set a = a * 2.63;
> UPDATE 1
In this case 2.63 is recognized by the parser as a numeric constant which is
an arbitrary precision data type. So postgres sees <integer> * <numeric> and
picks the appropriate * operator. That operator returns a numeric result which
it then casts to integer to handle the assignment.
So it parses as "set a = (a::numeric * 2.63)::integer"
> prod=> update tmp set a = a * '2.63';
> ERROR: invalid input syntax for integer: "2.63"
In this case the parser sees '2.63' which is not a string, but rather a
constant of unknown type. In that case postgres sees <integer> * <unknown> and
picks the most convenient type for the unknown side. Usually (always? I'm not
sure) the choice is the same type as the other side of the operator. So it
then tries to cast the unknown to an integer and fails.
> prod=> update tmp set a = (a::float * '2.63');
> UPDATE 1
In this case postgers sees <float> * <unknown> and the same thing happens and
it works.
> prod=> update tmp set a = a::float * '2.63';
> UPDATE 1
<float> * <unknown>
> prod=> update tmp set a = floort(a * '2.63');
> ERROR: invalid input syntax for integer: "2.63"
<integer> * <unknown>
> prod=> update tmp set a = floort(a * 2.63);
> ERROR: function floort(numeric) does not exist
> HINT: No function matches the given name and argument types. You may need
> to add explicit type casts.
So in this case it sees <integer> * <numeric> which it can satisfy with the
numeric*numeric operator which returns a numeric. But there's no
floort(numeric) operator.
> prod=> update tmp set a = floor(a * 2.63);
> UPDATE 1
This is "set a = (floor(a::numeric * <numeric>))::integer"
--
greg