How can I force a character field to sort as a numeric field?
I've got something like this:
Postgres=> SELECT username,last_name
FROM eg_member ORDER BY username;
----------+-----------
0120 | Foley
1 | Sullivan
10 | Guest
11 | User
(5 rows)
(I can't change the field type). I tried:
SELECT username,last_name
FROM eg_member ORDER BY username::integer;
But postgres 7 rejects this with "ERROR: cannot cast type character
varying to integer". Is there a way to force numeric sort order? I
tried a variety of functions, such as to_char() and convert() without
any luck. Thanks for your insight!