SELECT * FROM generate_series(1,10) ORDER BY 'foo';
ERROR: non-integer constant in ORDER BY
LINE 1: SELECT * FROM generate_series(1,10) ORDER BY 'foo';
The query was generated by an app (and the result somewhat inadvertent), so it was easy enough to change and I'm not asking here about a practical problem.
I am curious though about why this "limitation" exists. I get that integer constants are reserved for sorting by column numbers. But if Postgres already knows that it's a non-integer constant, why not let it go through with the (admittedly pointless) ordering?
Also, I couldn't see that this was explictly mentioned in the documentation. The relevant pieces seemed to be:
Each _expression_ can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary _expression_ formed from input-column values.
followed closely by:
(http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-ORDERBY)
And looking at the expressions page (http://www.postgresql.org/docs/8.4/static/sql-expressions.html), the first type of value _expression_ is a "constant or literal _expression_." So nothing seems to explicitly rule out a literal ORDER BY.
I'm not sure if it would do violence to something I'm missing, but would the following combined statement work for the documentation?
"Each _expression_ can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary _expression_. The _expression_ can include column values--whether they appear in the SELECT output list or not. An _expression_ may not, however, consist solely of a non-integer constant. And an integer constant will be interpreted as the ordinal number of an output column "
Thanks in advance.
Ken
--
AGENCY Software
A data system that puts you in control
(253) 245-3801
