Re: MySQL search query is not executing in Postgres DB

On 27 November 2012 22:41, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> ... I think if you relaxed
>> the function sigs of a few functions on this page
>> (http://www.postgresql.org/docs/9.2/interactive/functions-string.html),
>> most reported problems would go away.
>
> That's an interesting way of approaching it. Do we have any data on
> exactly which functions people do complain about?
>
>> One thing that worries me is introducing ambiguous cases where
>> previously there weren't any though.
>
> Right, but at least we'd be confining the ambiguity to a small number
> of function names. Tweaking the casting rules could have a lot of
> unforeseen consequences.

There have been many good points made on this thread.

Being sloppy in all cases is a bad thing we all agree or reluctantly
admit; what is needed is the ability for a user to be able to more
closely define what they mean by such conversions, so that application
SQL can be made to work.

It certainly isn't easy to say that COLUMN LIKE '1%' would work in all
cases, since the preferred format of that data might be (xxx)
xxx-xxxx, or $xxxxx or <EURO symbol>xxxx,xx (i.e. with a comma as the
decimal separator). The format comes from the meaning of the data,
which we cannot know.

What would be useful is to be able to define default format models for
each column. If not defined, there is no implicit cast. If FORMAT is
defined then we know to apply it in the absence of a global cast.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services