From: nha <lyondif02@free.fr>
To: JJ <jayson.prichard@comsouth.net>
Cc: PgSQL-sql <pgsql-sql@postgresql.org>
Subject: Re: Storing/sorting hex data in PostgreSQL
Date: Tue, 21 Jul 2009 13:14:36 +0200
Message-ID: <4A65A31C.7090604@free.fr> (raw)
In-Reply-To: <127c470d-1e29-46fe-87d7-63ce9828fb64@k1g2000yqf.googlegroups.com>
References: <127c470d-1e29-46fe-87d7-63ce9828fb64@k1g2000yqf.googlegroups.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lyondif02@free.fr, jayson.prichard@comsouth.net
Subject: Re: Storing/sorting hex data in PostgreSQL
In-Reply-To: <4A65A31C.7090604@free.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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