Re: Allowing postgresql to accept 0xff syntax for data types that it makes sense for?

On Fri, 22 May 2015 11:02:47 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alban Hertroys <haramrae(at)gmail(dot)com> writes:
> > On 22 May 2015 at 04:46, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> >> With all that being said, if I were to build a patch, would it be likely
> >> to be accepted into core?
>
> > Wouldn't you also need to support similar syntax for octal numbers for
> > the patch to be complete? Or are those already supported (ISTR that's
> > '077' for decimal 63)?
>
> A patch that made 077 be interpreted as octal would certainly get rejected
> out of hand, because that's valid syntax right now and it doesn't mean 63.

You'll get no objection from me on that point.

> A similar objection might be raised to 0x..., as that is also valid
> syntax; it's read as 0 followed by an identifier:
>
> regression=# select 0xff;
> xff
> -----
> 0
> (1 row)
>
> (Yet another place where the fact that AS is optional yields surprising
> results...) So there would be a backwards compatibility break here,
> and no you can't fix it with a GUC. Still, it might be a small enough
> break that we could get away with it.

I hadn't even considered that issue. I really hate the fact that AS is
optional, and I'm irritated by code that omits it ... but nobody's
asking me ...

> I'm not personally very excited
> but other people might be.
>
> Other questions you'd have to think about: what is the data type of
> 0xffffffff; what do you do with 0xffffffffffffffffffffffff (too big
> even for int8). And it'd likely behoove you to check how Microsoft
> answers those questions, if you want to point to SQL Server as what's
> going to keep you out of standards-compatibility problems. (IOW,
> if 0x ever did get standardized, the text might well match what
> SQL Server does.)

MSSQL seems to use it specifically for the equivalent of BYTEA types,
and it seems to me that should be how it works in PostgreSQL. Does
anyone watching this thread have access to a MSSQL server to verify?
If an implicit cast from a 4-byte BYTEA to int works now, then it
should work ... otherwise an explicit cast would be needed, with the
same behavior if you tried to specify a number that overflows an int
in any other way.

MySQL is more liberal in that you can use it to specify ints as
well, but I don't think MySQL is a good example of proper behavior.

--
Bill Moran