On Thu, 2008-07-03 at 13:54 +0100, Gregory Stark wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
>
> > On Thu, 2008-07-03 at 14:11 +0300, Heikki Linnakangas wrote:
> >
> >> > What I'd like it to do is to recognise that the 0 should be cast
> >> > implicitly to another datatype within the same family. I want and expect
> >> > nvl(char_column, 0)
> >> > to fail, but I expect the various numeric/integer types we have to play
> >> > nicely together without tears.
> >>
> >> So, it would be analogous to the 'unknown' type, but for numeric
> >> literals instead of text literals. Seems reasonable. It still wouldn't
> >> allow nvl(1::bigint, 2::int4), though, just as the unknown type doesn't
> >> help with nvl('foo'::text, 'bar'::varchar).
> >
> > Well, it would be nice if we could work with the unknown type also, but
> > I don't expect that's meaningful.
>
> Postgres's way of spelling constants of unknown type is to put them in single
> quotes. That is, 'foo' isn't a character string in Postgres, it's *any* kind
> of constant with an unknown type. So this would work:
>
> nvl(numeric_column, '0')
>
> I think what you're suggesting is making integer and floating point constants
> like 0 and 0.1 be treated as "unknown" or perhaps a different kind of unknown,
> "unknown integral type" and "unknown numeric type".
>
> Personally I think the way it works now is weird too, but it's been that way
> forever and changing it would be a pretty massive behaviour change.
Well, I can workaround the problem, it just seems like there shouldn't
be one.
I'm OK with massive behaviour change (like 8.3) as long as its a
controllable option.
By far the biggest behaviour change is to get the rest of the world to
work the way we do. People write (and *have written*) SQL that doesn't
work this way.
-- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
