Thread: How can I change a cast from explicit only to implicit?

In 7.2.x

template1=# select point('1'::text, '2'::text);
 point
-------
 (1,2)
(1 row)

but in 7.4.x

template1=# select point('1'::text, '2'::text);
ERROR:  function point(text, text) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit type casts.

                                          List of casts
         Source type         |         Target type         |      Function
|   Implicit?
-----------------------------+-----------------------------+----------------
-----+---------------
...
 text                        | double precision            | float8
| no

OK, so to make the cast work without explicit casts in the SQL, I need the
text to float8 (or another suitable numeric type) cast to be implicit.  But:

template1=# create cast (text as float8) with function float8(text) as
implicit;
ERROR:  cast from type text to type float8 already existsoat8(text) as
implicit;

template1=# drop cast (text as float8);
ERROR:  cannot drop cast from text to double precision because it is
required by the database system

So how can I force a built-in cast to become implicit?

Thanks

Julian Scarfe

"Julian Scarfe" <julian.scarfe@ntlworld.com> writes:
>                                           List of casts
>          Source type         |         Target type         |      Function
> |   Implicit?
> -----------------------------+-----------------------------+----------------
> -----+---------------
> ...
>  text                        | double precision            | float8
> | no

> OK, so to make the cast work without explicit casts in the SQL, I need the
> text to float8 (or another suitable numeric type) cast to be implicit.  But:
> ...
> So how can I force a built-in cast to become implicit?

If you're intent on doing that, you can change its entry in pg_cast.
But I think you'd be far better off to fix your application.  Implicit
casts across type categories have a habit of kicking in when you least
expected it, causing the backend to adopt surprising and unpleasant
interpretations of straightforward-looking queries.  If you check the
pgsql-bugs archives you will find some of the examples that prompted us
to change this cast to non-implicit...

            regards, tom lane

From: "Tom Lane" <tgl@sss.pgh.pa.us>

> > So how can I force a built-in cast to become implicit?
>
> If you're intent on doing that, you can change its entry in pg_cast.
> But I think you'd be far better off to fix your application.  Implicit
> casts across type categories have a habit of kicking in when you least
> expected it, causing the backend to adopt surprising and unpleasant
> interpretations of straightforward-looking queries.  If you check the
> pgsql-bugs archives you will find some of the examples that prompted us
> to change this cast to non-implicit...

It's a fair point, Tom.  Having developed the code with 7.2, we are looking
for the simplest way to recreate backward compatible behaviour in 7.4.  Sure
enough, we'll flag the code for revision next time it gets an overhaul, but
a single change to the database would be preferable in the short term.   I
think the current default of these casts being explicit only is sensible.

Thanks

Julian