pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcasting character varying to integer - order by numeric sort
6+ messages / 5 participants
[nested] [flat]
* casting character varying to integer - order by numeric sort
@ 2005-10-20 01:01 Bryce W Nesbitt <bryce1@obviously.com>
2005-10-20 01:34 ` Re: casting character varying to integer - order by numeric Terry Fielder <terry@ashtonwoodshomes.com>
2005-10-20 03:33 ` Re: casting character varying to integer - order by numeric sort Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Bryce W Nesbitt @ 2005-10-20 01:01 UTC (permalink / raw)
To: 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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: casting character varying to integer - order by numeric
2005-10-20 01:01 casting character varying to integer - order by numeric sort Bryce W Nesbitt <bryce1@obviously.com>
@ 2005-10-20 01:34 ` Terry Fielder <terry@ashtonwoodshomes.com>
1 sibling, 0 replies; 6+ messages in thread
From: Terry Fielder @ 2005-10-20 01:34 UTC (permalink / raw)
To: Bryce W Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
Check out the function to_number()
In particular here's an example...
If a field named section is text containing numbers:
ORDER BY to_number(t.section, text(99999999))
If the field can also contain non-numerals such as 3a, 3b, and you want
3a to show first then do this:
ORDER BY to_number(t.section, text(99999999)), t.section
And if the field section can actually START with an alpha, then to
prevent to_number from failing do this:
to_number(textcat('0', t.section), text(99999999)), t.section
Terry
Bryce W Nesbitt wrote:
> 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!
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: casting character varying to integer - order by numeric sort
2005-10-20 01:01 casting character varying to integer - order by numeric sort Bryce W Nesbitt <bryce1@obviously.com>
@ 2005-10-20 03:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-20 06:57 ` Re: casting character varying to integer - order by numeric Bryce Nesbitt (mailing list account) <bryce1@obviously.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-10-20 03:33 UTC (permalink / raw)
To: Bryce W Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
Bryce W Nesbitt <bryce1@obviously.com> writes:
> 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".
As a general rule, you need to be more specific than that about which
version you are working with ;-)
You may find that username::text::integer will work, depending on which
7.x this actually is.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: casting character varying to integer - order by numeric
2005-10-20 01:01 casting character varying to integer - order by numeric sort Bryce W Nesbitt <bryce1@obviously.com>
2005-10-20 03:33 ` Re: casting character varying to integer - order by numeric sort Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-20 06:57 ` Bryce Nesbitt (mailing list account) <bryce1@obviously.com>
2005-10-20 07:32 ` Re: casting character varying to integer - order by numeric Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bryce Nesbitt (mailing list account) @ 2005-10-20 06:57 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:<br>
<blockquote cite="mid3687.1129779194@sss.pgh.pa.us" type="cite">
<blockquote type="cite">
<pre wrap="">But postgres 7 rejects this with "ERROR: cannot cast type character
varying to integer".
</pre>
</blockquote>
<pre wrap=""><!---->
As a general rule, you need to be more specific than that about which
version you are working with ;-)
You may find that username::text::integer will work, depending on which
7.x this actually is.
regards, tom lane
</pre>
</blockquote>
Oooh, I'd be so happy to. But I don't know. Yes, I don't know.<br>
<br>
I know which version of "psql" is installed on my local machine, but
the actual database is remote. Is there a command to get the server
version? As close as I can find is:<br>
<br>
stage=> \copyright<br>
PostgreSQL Data Base Management System<br>
<br>
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group<br>
<br>
<br>
-Bryce<br>
<br>
PS: If it's a hint, "select username from eg_member order by
username::text::integer" works fine. Thanks.<br>
</body>
</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: casting character varying to integer - order by numeric
2005-10-20 01:01 casting character varying to integer - order by numeric sort Bryce W Nesbitt <bryce1@obviously.com>
2005-10-20 03:33 ` Re: casting character varying to integer - order by numeric sort Tom Lane <tgl@sss.pgh.pa.us>
2005-10-20 06:57 ` Re: casting character varying to integer - order by numeric Bryce Nesbitt (mailing list account) <bryce1@obviously.com>
@ 2005-10-20 07:32 ` Richard Huxton <dev@archonet.com>
2005-10-20 15:58 ` Re: casting character varying to integer - order by numeric Bryce Nesbitt (mailing list account) <bryce1@obviously.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2005-10-20 07:32 UTC (permalink / raw)
To: Bryce Nesbitt (mailing list account) <bryce1@obviously.com>; +Cc: pgsql-sql
Bryce Nesbitt (mailing list account) wrote:
> Tom Lane wrote:
>>As a general rule, you need to be more specific than that about which
>>version you are working with ;-)
> Oooh, I'd be so happy to. But I don't know. Yes, I don't know.
>
> I know which version of "psql" is installed on my local machine, but the actual
> database is remote. Is there a command to get the server version? As close as
> I can find is:
>
> stage=> \copyright
SELECT version();
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: casting character varying to integer - order by numeric
2005-10-20 01:01 casting character varying to integer - order by numeric sort Bryce W Nesbitt <bryce1@obviously.com>
2005-10-20 03:33 ` Re: casting character varying to integer - order by numeric sort Tom Lane <tgl@sss.pgh.pa.us>
2005-10-20 06:57 ` Re: casting character varying to integer - order by numeric Bryce Nesbitt (mailing list account) <bryce1@obviously.com>
2005-10-20 07:32 ` Re: casting character varying to integer - order by numeric Richard Huxton <dev@archonet.com>
@ 2005-10-20 15:58 ` Bryce Nesbitt (mailing list account) <bryce1@obviously.com>
0 siblings, 0 replies; 6+ messages in thread
From: Bryce Nesbitt (mailing list account) @ 2005-10-20 15:58 UTC (permalink / raw)
To: pgsql-sql
Cool, thanks.
PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
Richard Huxton wrote:
> SELECT version();
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-10-20 15:58 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-20 01:01 casting character varying to integer - order by numeric sort Bryce W Nesbitt <bryce1@obviously.com>
2005-10-20 01:34 ` Re: casting character varying to integer - order by numeric Terry Fielder <terry@ashtonwoodshomes.com>
2005-10-20 03:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-20 06:57 ` Re: casting character varying to integer - order by numeric Bryce Nesbitt (mailing list account) <bryce1@obviously.com>
2005-10-20 07:32 ` Re: casting character varying to integer - order by numeric Richard Huxton <dev@archonet.com>
2005-10-20 15:58 ` Re: casting character varying to integer - order by numeric Bryce Nesbitt (mailing list account) <bryce1@obviously.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox