Different behaviour in "order by" clause: Oracle vs. PostgreSQL -

i have following table (created , populated them in oracle , postgresql):

> create table foo (a varchar(10)); 

i populated them values, , order by clause behaving differently in postgresql , oracle (i don't think versions relevant question):

oracle:

> select a, length(a) foo order a;       length(a) ---------- ---------- .1          2 01          2 1           1 1#0         3 1#1         3 1.0         3 1.1         3 10          2 11          2  9 rows selected. 

i expect. .1 before 01, since . before 0 in ascii table.

however, in postgresql have:

=> select a, length(a) foo order a;    | length  -----+--------  01  |      2  1   |      1  .1  |      2  10  |      2  1.0 |      3  1#0 |      3  11  |      2  1.1 |      3  1#1 |      3 (9 rows) 

why difference? know has collate order or similar, pointers on read more it.

update: collate info on postgresql database:

encoding: utf8 collante: en_us.utf-8 ctype: en_us.utf-8 |  

thanks!

postgres has 2 built-in collations: c , posix. other collations provided operating system. on many linux systems in utf locales non alphanumeric characters ignored during sorting.

you can obtain expected result using collate c:

select a, length(a) foo order collate "c"; 

you can find more detailed explanation in answer.