Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> As far as I know, the fact that LIKE is another spelling for ~~ is
a
> PostgreSQL implementation detail with which users ought not to be
> burdened.
+1
LIKE is well defined by the standard, and the ~~ operator is not
mentioned there anywhere.
On the other hand, LIKE is defined to either work on character
strings or binary strings -- there is nothing in the standard about
using it with other data types or automatic casting to support that.
Any such support would be a non-standard PostgreSQL extension. As
such, anyone wanting to write portable code should avoid that by
explicit casting (which should be portable).
> I remember there was a time when you couldn't say "SELECT a x FROM
> foo" in PostgreSQL.
That was in violation of the SQL standard, which makes AS an allowed
but optional noise word.
In spite of all that, perhaps we should have a compatibility
extension which provides more casts, in an attempt to ease the
transition from other databases? Personally, I like having the
default behavior this strict -- I think it reduces the chance of
errors, reduces the chances of accidentally having type mismatches
which defeat optimizations, and improves portability. But I have
nothing against allowing someone to give all that up to ease
transition from another product. My biggest concern is whether we
might "paint ourselves into a corner" by including such an
extension. It might shut off avenues for other cool features
because anyone using the extension would have conflicts. Perhaps
such a thing would be more appropriate on PGXN with admonitions that
it was only intended to ease conversion and that users were
encouraged to migrate to standard syntax as soon as possible.
-Kevin