Problem with PostgreSQL string sorting

From: Hari Sankar A <hsshanthamhari(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problem with PostgreSQL string sorting
Date: 2017-02-15 15:44:44
Message-ID: CAL1h+Q-6bmpGo2w+KvaT7ib4oE4NmoJvvxWp5jH9CaZT+M6cKw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello All,

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.

Lets say table person has field name with values likes

name
------
Abc
abc
.dcb
Dcb
$sdf
EDF

en_US.UTF-8 collate sorting this field as:

name
------
abc
Abc
.dcb
Dcb
EDF
$sdf

It ignore's Special characters.

"C" collate sorting this field as:
name
------
$sdf
.dcb
Abc
Dcb
EDF
abc

It does case sensitive sort.

My expected sorting should be: (Case insensitive and special character)

name
------
$sdf
.dcb
Abc
abc
Dcb
EDF

I can use lower() and collate 'C' to get this result. But I need a default
collate to support this.

Is there any collation that support both (Case insensitive and special
character) sorting in utf8?

your help would be really appreciated.

Thanks,
Hari

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2017-02-15 16:54:56 Re: Problem with PostgreSQL string sorting
Previous Message Stuart Bishop 2017-02-15 10:02:35 pg_rewind fails after failover, 'invalid record length'