Re: Differences in WHERE clause of SELECT

On Tue, Jul 16, 2013 at 2:58 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Jul 16, 2013 at 12:58 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> PostgreSQL requires more casts that any other mainstream database
>> system on the planet, and it's one of the biggest barriers to
>> migrating from other database systems to PostgreSQL. I understand
>> that there is a consensus in this forum to pretend that our
>> intolerance is a virtue, but I don't agree with that consensus. A
>> request for =(text,int) is ambiguous; we really do not know what is
>> meant. A request for ~~(int,text) is not ambiguous; we reject it out
>> of pedantry, not necessity.
>
> You make some good points but I still wonder (I'm recalling your lpad
> discussion from a while back) if we're better off providing specific
> overloads and casts for portability pain points instead of changing
> casting mechanics.

TBH, I think we've gone too far in that direction already. We've got
numerous cases where there are multiple function or operator
declarations that exist only to work around shortcomings of the
typecasting system. For example, we've got pg_size_pretty(bigint) and
pg_size_pretty(numeric); if we adopted the patch that I proposed
previously, one of those would be enough, and then it would work for
integers, too. We could instead add a third version for integers, but
where's the joy in that?

We've currently got 10 different functions for || that include
||(anynonarray,text), ||(text,anynonarray), and ||(text,text). The
first two of those are workarounds for the fact that the third one
does not do what you want. And then there's concat(variadic "any"),
which is yet a third way of hacking around the same general category
of problem. The patch I proposed previously would allow concat to be
declared as variadic text if we so desired, but it wouldn't help with
the || case. I think that's OK; I never pretended that patch would
fix everything, but I still think it's an improvement over what we
have now.

We can certainly continue to play whack-a-mole and dream up a new
solution every time a really intolerable variant of this problem comes
up. But that doesn't seem good to me. It means that every case
behaves a little different from every other case, and the whole thing
is kinda arcane and hard to understand, even for hackers.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company