Thread: String comparison problem.

Dear All,
           I have some problem with string comparison. I use 
postgresql 8.3.1,UTF8 encoding, on CentOS 5.5. Why dose the result of  
SQL statement :  "select '1.1' < '101';" is false but "select 
'1.1'::bytea < '101'::bytea;" is ture?


mis=# select '.' < '0';
?column?
----------
t
(1 row)



mis=# select '1.' < '10';
?column?
----------
t
(1 row)



mis=# select '1.1' < '101';
?column?
----------
f
(1 row)


mis=# select '1.1'::text < '101'::text;
?column?
----------
f
(1 row)


mis=# select '1.1'::bytea < '101'::bytea;
?column?
----------
t
(1 row)


Thank you very much.

On Thu, May 29, 2008 at 9:45 PM, Theerasak Maneeneim
<theerasak@thns.co.th> wrote:
> Dear All,
>
>           I have some problem with string comparison. I use postgresql
> 8.3.1,UTF8 encoding, on CentOS 5.5. Why dose the result of  SQL statement :
>  "select '1.1' < '101';" is false but "select '1.1'::bytea < '101'::bytea;"
> is ture?

The UTF encoding isn't what's important here, it's your locale, which
is likely something like en_US (but different, whatever you'd have in
your country).  This locale will be collated (i.e. sorted etc.) by a
set of rules for the language where you live.

For instance, with a list like:
a dcabca bcadc

and an order by on it in locale en_US I get this ordering:
abca bcadca dc

Why?  Because according to the rules of english, spaces don't count.

If you want byte code ordering, then you need to use Locale=C when you
run initdb.  Locale can only be changed at initdb time.