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
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? |