non-integer constant in ORDER BY: why exactly, and documentation?

From: Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: non-integer constant in ORDER BY: why exactly, and documentation?
Date: 2012-10-11 20:48:34
Message-ID: CAD3a31V7CETbYZxMpsCXxMewC10R0H2QSf_5Qv00nJ8EOSuLqA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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** "
*
Thanks in advance.

Ken

--
AGENCY Software
A data system that puts you in control
*http://agency-software.org/*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2012-10-11 20:59:35 Re: non-integer constant in ORDER BY: why exactly, and documentation?
Previous Message Evan D. Hoffman 2012-10-11 19:42:56 Re: Disabling inferred "group by" columns in 9.1?