I am new to postrges and want to sort varchar type columns. want to explain the problem with with below example:
table name: testsorting
order name
1 b
2 B
3 a
4 a1
5 a11
6 a2
7 a20
8 A
9 a19
case sensitive sorting (which is default in postgres) gives:
select name from testsorting order by name;
A
B
a
a1
a11
a19
a2
a20
b
case in-sensitive sorting gives:
select name from testsorting order by UPPER(name);
A
a
a1
a11
a19
a2
a20
B
b
how can i make alphanumeric case in-sensitive sorting in postgres to get below order:
a
A
a1
a2
a11
a19
a20
b
B
I wont mind the order for capital or small letters, but the order should be "aAbB" or "AaBb" and should not be "ABab"
Please suggest if you have any solution to this in postgres.
Best Answer
My PostgreSQL sorts the way you want. The way PostgreSQL compares strings is determined by locale and collation. When you create database using
createdb
there is-l
option to set locale. Also you can check how it is configured in your environment usingpsql -l
:As you see my database uses Polish collation.
If you created database using other collation then you can use other collation in query just like:
You can list available collations by:
EDITED:
Oh, I missed that 'a11' must be before 'a2'.
I don't think standard collation can solve alphanumeric sorting. For such sorting you will have to split string into parts just like in Clodoaldo Neto response. Another option that is useful if you frequently have to order this way is to separate name field into two columns. You can create trigger on INSERT and UPDATE that split
name
intoname_1
andname_2
and then:(I changed collation from Polish into English, you should use your native collation to sort letters like aącć etc)