Re: Very puzzling sort behavior

On Thu, Sep 10, 2015 at 2:02 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> Ken Tanzer wrote:
>
> > Are there any other potential solutions, pitfalls or considerations that
> > come to mind? Any thoughts welcome. And as I said, if there's not a
> good
> > way to do this I'll probably leave it alone.
>
> In part, it boils down to what you use the in ORDER BY clause. If you
> concatenate the last name and first name, they will be considered as a
> single string and run afoul of funny behavior of dictionary sorting,
> which ignores non-alphanumeric chars in the first pass. But if you keep
> them separate by using "ORDER BY last_name, first_name" then sorting
> will consider the last name separately from first name, and you'd get
> the results you want (I think).
>

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. I still might
be clinging to futile hope, but is there really no way to specify a
collation for the return value of a function? And are there any pitfalls
associated with pg_dump-ing into a new database with a different collation?

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.