Casting rules (was: an untitled thread)

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> Shall we abandon all that work and go back to "any available cast can be
>> applied implicitly"?
>>
>> My vote is "tough, time to fix your SQL code".

> That would be a OK if the current behavior conformed to the SQL standard,
> which it doesn't. The standard says that all numerical types are mutually
> assignable, which in my mind translates directly as implicitly castable.

If we take that stance then we will never make any progress at all on
fixing our problems with poor choices of numeric operators and inability
to choose an appropriate operator. We can *not* adopt the attitude that
all numeric casts are equal; some have got to be more equal than others,
or the parser will be unable to choose desirable interpretations over
undesirable ones.

As an example, current code does the right thing with
select * from foo where numeric_col = 10.1
whereas 7.2 failed with
ERROR: Unable to identify an operator '=' for types 'numeric' and 'double precision'
This improvement comes precisely because the numeric->float8 cast
pathway is not treated on an even footing with the other direction.

> Additionally, your stance breaks the following SQL compatible and probably
> quite common code:

> create table test ( a int extract(year from current_date) );

I previously suggested that it might be okay to allow non-implicit casts
to be used when assigning a value to a target column in INSERT and
UPDATE (including the case where the value is a default value). If we
do that, then the above will work, and we haven't abandoned all hope of
choosing sensible cast pathways within expressions.

Alternatively we could think about a three-level scheme where pg_cast
can declare different "strengths" of implicit castability for a cast
pathway; then it'd be possible to allow or disallow implicit coercion
to a target column type on a cast-by-cast basis. Dunno if we need that
much complexity here...

regards, tom lane