Re: Very puzzling sort behavior - Mailing list pgsql-general
On Thu, Sep 10, 2015 at 2:02 PM, Alvaro Herrera <alvherre@2ndquadrant.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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: