test=# create table vc ( n varchar);
CREATE
test=# insert into vc values ('1');
INSERT 304426 1
(...etc...)
test=# select n from vc order by n;
n
----
1
10
2
20
25
3
30
40
(8 rows)
test=# select n from vc order by int8(n);
n
----
1
2
3
10
20
25
30
40
(8 rows)
Hope this helps.
Allan.
Ben Ausden wrote:
> Hi,
>
>
> Is there any way to persuade postgresql to sort a VARCHAR column in numeric
> order first, and then text order?
>
> e.g
>
> 1, 2, 3, 10, 20, 25, 30, 40
>
> instead of:
>
> 1, 10, 2, 20, 25, 3, 30, 40
>
>
> From looking at the docs it seems that sort order is determined by the
> locale settings. Is this right, and would setting my locale to en_US cause
> postgres to use a different sort order?
>
>
> thanks,
> ben
>
>