Thread: [GENERAL] Problem with PostgreSQL string sorting Hello All,

[GENERAL] Problem with PostgreSQL string sorting Hello All,

From

Hari Sankar A

Date:

15 February 2017, 22:01:26

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

Re: [GENERAL] Problem with PostgreSQL string sorting Hello All,

From

"David G. Johnston"

Date:

16 February 2017, 01:09:15

On Wed, Feb 15, 2017 at 9:01 AM, Hari Sankar A <hsshanthamhari@gmail.com> wrote:

[...]

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.

Duplicate post on -bugs already answered - it came through the mail system first even through this is the correct list for the topic.

David J.