Postgres Pro
Downloads
Home   >   mailing lists

Thread: Order by with column ordinal and collate - fails to parse

Order by with column ordinal and collate - fails to parse

From
Tim Kane
Date:
Hi all,

I seem to be having problems constructing a query that combines the use of distinct, order by, and collate key words.

For instance:

# select distinct(value)  from properties order by 1 collate "C";
ERROR:  collations are not supported by type integer
LINE 1: ... distinct(value)  from properties order by 1 collate "C...
                                                             ^
# select distinct(value)  from properties order by distinct(value) collate "C";
ERROR:  syntax error at or near "distinct"
LINE 1: ...ct distinct(value)  from properties order by distinct(v...
                                                             ^
# select distinct(value) as foo from properties order by foo collate "C";
ERROR:  column "foo" does not exist
LINE 1: ...tinct(value) as foo from properties order by foo collat...


Am I just being a numpty here? I can work around it with a subquery, but this seems like a bug to me. Particularly the first example where my ordinal field reference is treated as an integer literal. I should note that the field 'value' is of type 'text' (not integer).

Any input appreciated. Thanks :)

Re: Order by with column ordinal and collate - fails to parse

From
Adrian Klaver
Date:
On 06/20/2013 07:05 AM, Tim Kane wrote:
> Hi all,
>
> I seem to be having problems constructing a query that combines the use
> of distinct, order by, and collate key words.
>
> For instance:
>
> # select distinct(value)  from properties order by 1 collate "C";
> ERROR:  collations are not supported by type integer
> LINE 1: ... distinct(value)  from properties order by 1 collate "C...
>                                                               ^

How about:

select distinct(value) collate "C" from properties order by 1 ;

>
> Am I just being a numpty here? I can work around it with a subquery, but
> this seems like a bug to me. Particularly the first example where my
> ordinal field reference is treated as an integer literal. I should note
> that the field 'value' is of type 'text' (not integer).
>
> Any input appreciated. Thanks :)
>


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Order by with column ordinal and collate - fails to parse

From
Tim Kane
Date:
Nice one. Yep, that works. Cheers..
I'll submit a bug report for this, though I'm on the fence as to wether this is actually a bug per se.. I would have reasonably expected my original syntax to have worked (as it does without any ORDER BY)..   


On Thu, Jun 20, 2013 at 3:44 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 06/20/2013 07:05 AM, Tim Kane wrote:
Hi all,

I seem to be having problems constructing a query that combines the use
of distinct, order by, and collate key words.

For instance:

# select distinct(value)  from properties order by 1 collate "C";
ERROR:  collations are not supported by type integer
LINE 1: ... distinct(value)  from properties order by 1 collate "C...
                                                              ^

How about:

select distinct(value) collate "C" from properties order by 1 ;



Am I just being a numpty here? I can work around it with a subquery, but
this seems like a bug to me. Particularly the first example where my
ordinal field reference is treated as an integer literal. I should note
that the field 'value' is of type 'text' (not integer).

Any input appreciated. Thanks :)



--
Adrian Klaver
adrian.klaver@gmail.com