Re: Very puzzling sort behavior

On 09/10/2015 03:03 PM, Andrew Sullivan wrote:
> On Thu, Sep 10, 2015 at 02:54:31PM -0700, Ken Tanzer wrote:
>> Thanks, but I guess I should have been clearer. Thanks to y'all wonderful
>> mailing list folks, I get it now as to why the two sorts are not the same.
>> I'm hoping for practical suggestions or advice about how to get C locale
>> sorting without having to rewrite all my existing queries.
>
> Why not change the collation for the column?
> http://www.postgresql.org/docs/9.4/interactive/sql-altertable.html
>
>> be clinging to futile hope, but is there really no way to specify a
>> collation for the return value of a function?
>
> I don't believe so. I think you need to specify the collation for the
> data itself.

I have not read the entire thread, and not sure I am following the issue
correctly, but isn't this what you want?

8<---------------
create table t2(a text);
insert into t2 values('abc,def'),('abcd');

show lc_collate;
lc_collate
-------------
en_US.UTF-8
(1 row)

select a from t2 order by a;
a
---------
abcd
abc,def
(2 rows)

select a from t2 order by a COLLATE "C";
a
---------
abc,def
abcd
(2 rows)
8<---------------

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development