On Oct 11, 2012, at 4:48 PM, Ken Tanzer wrote: > Hi. I recently ran a query that generate the same error as this: > > 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: > > It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECT output list. > (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 " Apparently, the parser tries to pull an column index out of any constant appearing in that position. It can be trivially worked around: select * from generate_series(1,10) order by coalesce('foo'); but that doesn't help if your query is automatically generated. Cheers, M -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
