Message-ID: <4A65A31C.7090604@free.fr>
Date: Tue, 21 Jul 2009 13:14:36 +0200
From: nha <lyondif02@free.fr>
Organization: Lyon, Rhone, France
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: JJ <jayson.prichard@comsouth.net>
CC: PgSQL-sql <pgsql-sql@postgresql.org>
Subject: Re: Storing/sorting hex data in PostgreSQL
References: <127c470d-1e29-46fe-87d7-63ce9828fb64@k1g2000yqf.googlegroups.com>
In-Reply-To: 
 <127c470d-1e29-46fe-87d7-63ce9828fb64@k1g2000yqf.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hello,

On 20/07/09 21:59, JJ wrote :
> [...] 
> I want to sort and query the database based on the
> hex addresses [...]
> How can I store hex values in PostgreSQL and sort and query them?
> 

PostgreSQL (since version 8.3 and maybe older) sets string functions for
encoding/decoding strings from/to their hexadecimal representation. The
following page presents these functions:
http://www.postgresql.org/docs/8.3/interactive/functions-string.html

For example:

# SELECT decode('6d61702064617461', 'hex');
results in string: map data
# SELECT encode('map data', 'hex');
results in hexadecimal representation of "map data": 6d61702064617461
(ie. each character [each byte in fact] is replaced by its hexadecimal
representation in ASCII range [space " " is replaced by "20" (hex.
value) or "32" (decimal value)])

A (simplier?) alternative would be to convert hexadecimal value into
text with the natural class operator:
# SELECT '6d61'::text;
results in string (text type): 6d61

At this time, I do not know about any PostgreSQL function that converts
from/to hex. to/from integer. Such functions may be user-created. The
sorting speed may then depend on the meaning of integer values (numeric
relation relevance) with your data.

Regards.
--
nha / Lyon / France.