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

>
> I think the argument was that it's almost certainly a mistake, so we're
> more helpful by throwing an error than by silently executing a query
> that probably won't do what the user was expecting. In this particular
> example, it seems quite likely that the programmer meant "foo" (ie a
> quoted column reference) and got the quote style wrong ...
>

I guess it depends what you mean by mistake. In this case, here was the
actual code involved:

if ($GLOBALS['AG_DEMO_MODE']) {
$label_field="'XXXXXX, XXX'";
} else {
$label_field= $object . '_name(' . $id_field . ')'; //
e.g., client_name(client_id)
}
$op .= selectto('objectPickerPickList',$obj_opt )
. do_pick_sql("SELECT $id_field AS value,$label_field AS label
FROM " . $def['table'] . " ORDER BY $label_field")
...

So yes there are lots of workarounds (and thanks all for the suggestions),
including for this case just "ORDER BY 2". And there surely are better ways
to code this, but finding areas for potential improvement is a target-rich
environment, and one usually in need of prioritization. In this case,
there's no reason the code above _couldn't_ have been adequately
functional, had not some well-meaning software gotten in the way by trying
to watch out for me... ;)

Then again, my personal Postgres score of times it has helped me versus
times it has not is probably about 1.5 million to 7, so don't hear this as
a giant grumble or complaint. I really was more curious than anything...

Cheers,
Ken

On Thu, Oct 11, 2012 at 2:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:
> > 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
>
> > 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?
>
> I think the argument was that it's almost certainly a mistake, so we're
> more helpful by throwing an error than by silently executing a query
> that probably won't do what the user was expecting. In this particular
> example, it seems quite likely that the programmer meant "foo" (ie a
> quoted column reference) and got the quote style wrong ...
>
> regards, tom lane
>

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