Why is ' 2' > '10'?

Question

Why is ' 2' with an initial space bigger than '10'?

select ' 2' > '10';
 ?column? 
----------
 t
(1 row)

I tried it with both latin1 and utf8 english collations:

                                    List of databases
   Name    |   Owner    | Encoding  |    Collation     |      Ctype       |   Access privileges   
-----------+------------+-----------+------------------+------------------+-----------------------
 cpn       | cpn        | UTF8      | en_US.UTF-8      | en_US.UTF-8      | 
 teste     | cpn        | LATIN1    | en_US.ISO-8859-1 | en_US.ISO-8859-1 | 

I know it has to do with the type because when it is cast it works as expected:

teste=> select ' 2'::char > '10';
 ?column? 
----------
 f
(1 row)

What exactly is happening here?

EDIT:

All the above was done with 8.4.8 in Fedora 13. But I just tested with 9.04 in Centos 6 with the same result:

select ' 2' > '10';
 ?column? 
----------
 t
(1 row)

List of databases

   Name    |   Owner    | Encoding  |  Collation  |    Ctype    |   Access privileges   
-----------+------------+-----------+-------------+-------------+-----------------------
 cpn       | postgres   | UTF8      | en_US.UTF-8 | en_US.UTF-8 | 

New Edit:

This is to further confuse:

select ' ' > '1';
 ?column? 
----------
 f
(1 row)

Answer 1

I think PostgreSQL automatically tries to figure out the type behind the scenes and in Linux it tries to get rid of the ' ', some of the comparisons are also based on locale.

• Thus, ' 2' > '10' becomes '2'>'10' and the comparison is '2'>'1'; they are not equal, so no need to continue with the rest of the string, and ascii('2') is greater than ascii('1'), so it evaluates to true.

• If it were an equality operation (e.g. ' 22' = '22 ') it would result to false because Postgres does a byte by byte comparison. This is important because the engine uses two different algorithms when doing comparisons.

• If you specify the type via typecasting, then it won't override the space rules (' '=>'').

---

Also credit goes to: RhodiumToad and Peerce in #postgresql

Answer 2

I think this has to do with locale settings.

According to PostgreSQL docs: Locale Support:

The locale settings influence the following SQL features:

• Sort order in queries using ORDER BY on textual data
• The ability to use indexes with LIKE clauses
• The upper, lower, and initcap functions
• The to_char family of functions