Re: Column Does Not Exist Error: Order by an Particular Cell of an Array

Ah, that makes sense -- thank you Kevin.

- David

On Tue, Aug 2, 2016 at 2:17 PM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:

> On Tue, Aug 2, 2016 at 11:48 AM, David Muller <dmuller(at)guidebook(dot)com>
> wrote:
>
> > gears=> select *, Array[4,5] as array_from_select from foo order by
> > array_from_select[1];
> >
> > ERROR: column "array_from_select" does not exist
>
> array_from_select is not a column name, it is an alias. The ways
> in which an alias can be used in a query are somewhat limited --
> you can order by the alias of a result column, but you can't use
> the alias in arbitrary expressions.
>
> You could do something like this:
>
> test=# with x as (select *, Array[4,5] as array_from_select from foo)
> test-# select * from x order by array_from_select[1];
> value | array_from_select
> -------+-------------------
> 1 | {4,5}
> 2 | {4,5}
> (2 rows)
>
> In this case array_from_select becomes a column in the result set
> of common table expression (CTE) x and *can* be used in
> expressions.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>