Message-ID: <4356EC5D.4080706@obviously.com>
Date: Wed, 19 Oct 2005 18:01:17 -0700
From: Bryce W Nesbitt <bryce1@obviously.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.7.8) Gecko/20050511
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: casting character varying to integer - order by numeric sort
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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!