Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2017
    Posts
    1

    Unanswered: Problem with PostgreSQL string sorting

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,969
    Provided Answers: 21
    Postgres uses the collations defined in the operating system (which sometimes is a big annoyance).

    So you would need to find a Linux collation that does what you want.
    Then you can create a new collation in Postgres: https://www.postgresql.org/docs/curr...collation.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •