ryan.vilim@gmail.com writes:
> I recently found that postgres will accept the
> SELECT -144mycol
> to be equivalent to
> SELECT -144 AS mycol.
Yup. There are two things going on there:
1. "-144mycol" is read as three tokens, "-", "144", and "mycol".
2. AS is optional.
> I haven't been able to find any other references to this being standard SQL.
As best I can tell, the SQL spec says that in spec-conformant SQL there
should be a space between "144" and "mycol", because it says
Any <token> may be followed by a <separator>. A <nondelimiter
token> shall be followed by a <delimiter token> or a <separator>.
and <regular identifier>, <key word>, and <unsigned numeric literal> all
count as <nondelimiter token>s. (White space is a <separator>.) On the
other hand, this restriction seems a tad arbitrary, because quoted
identifiers count as <delimiter token>s. Which means that
-144mycol
isn't quite valid SQL, but
-144"mycol"
is gold-plated valid SQL. So you would need a rather curious set of
assumptions about the abilities of your lexer in order to decide that the
first case is ambiguous while the second isn't. You could maybe argue
that the second case is more readable, but that argument seems a bit thin.
In any case, the odds that we'd change this behavior in PG are nil;
there's no real upside to doing so and we'd inevitably break a lot of
existing applications if we did.
As for #2 (AS being optional), that's required by spec as well.
Personally I find it one of the dumbest syntax decisions in the whole
language, but it's in the spec so we're stuck with it.
> I think it might be a bug in postgres.
You can argue whether it's a bug or not, but it's intentional behavior.
regards, tom lane