casting character varying to integer - order by numeric sort

From: Bryce W Nesbitt <bryce1(at)obviously(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: casting character varying to integer - order by numeric sort
Date: 2005-10-20 01:01:17
Message-ID: 4356EC5D.4080706@obviously.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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!

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Terry Fielder 2005-10-20 01:34:17 Re: casting character varying to integer - order by numeric
Previous Message Tom Lane 2005-10-19 21:11:30 Re: NULL in IN clause