Re: [SQL] Storing/sorting hex data in PostgreSQL
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Order of operations in ascii vs archive format (moderately urgent)
Is there a difference in the order of execution between an ascii dump and one using the "custom" format? Or any difference in the general operation? I need to know if I can rely on the ascii version to tell me what the custom format might have done. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Order of operations in ascii vs archive format (moderately urgent)
Rob Sargent writes: > Is there a difference in the order of execution between an ascii dump > and one using the "custom" format? Or any difference in the general > operation? There's not supposed to be. One standard test on the pg_dump code is pg_dump >textfile pg_dump -Fc >dumpfile pg_restore dumpfile >textfile2 diff textfile textfile2 If these don't produce the same results something is broken. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Order of operations in ascii vs archive format (moderately urgent)
Wonderful news. I just ran dump and restore against same production server. Constraints and the absence of drop calls appears to have saved my butt. Tom Lane wrote: Rob Sargent writes: Is there a difference in the order of execution between an ascii dump and one using the "custom" format? Or any difference in the general operation? There's not supposed to be. One standard test on the pg_dump code is pg_dump >textfile pg_dump -Fc >dumpfile pg_restore dumpfile >textfile2 diff textfile textfile2 If these don't produce the same results something is broken. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Order of operations in ascii vs archive format (moderately urgent)
The ascii dump has serveral CREATE FUNCTION gbtreeN_{in,out} but I don't
see them in the current (source) database using '\df gbtree*'. Using
'\df gbt*' I get 111 functions for which all the names begin 'gbt_'.
Have I lost them? The gbtreekeyN types are still there.
Tom Lane wrote:
Rob Sargent writes:
Is there a difference in the order of execution between an ascii dump
and one using the "custom" format? Or any difference in the general
operation?
There's not supposed to be. One standard test on the pg_dump code is
pg_dump >textfile
pg_dump -Fc >dumpfile
pg_restore dumpfile >textfile2
diff textfile textfile2
If these don't produce the same results something is broken.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Order of operations in ascii vs archive format (moderately urgent)
Rob Sargent writes:
> The ascii dump has serveral CREATE FUNCTION gbtreeN_{in,out} but I don't
> see them in the current (source) database using '\df gbtree*'.
Which ones? Pre-8.4 \df will deliberately suppress I/O functions
(or functions that it thinks are I/O functions, anyway).
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to tell if column set on update
> Le 20/07/09 15:19, chester c young a écrit : > > within a trigger need to know if the UPDATE statement > set a column. the column might be set to the old value > or a different value. > > > > (want to make sure the app is sending all necessary > values) > > > > thanks > > > > If the column to test is known -- e.g. column MyCol --, > NEW.MyCol and > OLD.MyCol -- respectively value of MyCol after UPDATE and > value of MyCol > before UPDATE -- can be compared. for example, create table t1( c1 int, c2 int ); insert into t1 values( 1, 2 ); 1) update t1 set c1=4 where c1=1; 2) update t1 set c1=4, c2=2 where c1=1; each update results in the same row, but in the second update c2 was actually set. a trigger on the update - how do we know if c2 has been actually set or not? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
