Re: Problem with PostgreSQL string sorting

On Wed, Feb 15, 2017 at 7:44 AM, Hari Sankar A <hsshanthamhari(at)gmail(dot)com> wrote:
> I am a Database administrator and we are using PostgreSQL-9.6.1 version in
> RHEL-7 linux machine. Current server Encoding setup is UTF8 and LC_COLLATE
> and LC_CTYPE is en_US.UTF-8.

The behavior of each collation comes from the operating system's own
libc, except for the C collation, which is based on the ordering
implied by strcmp() comparisons. Generally, most implementations have
the behavior you describe, in that they assign least weight of all to
caseness and whitespace, and somewhat more weight to punctuation. I
don't think that there is much that can be done about it in practice,
though in principal there could be a collation that has all the
properties you want.

Future versions of PostgreSQL may have better support for this kind of thing.

--
Peter Geoghegan