bytea display as hex

Bytea display as hex

I recently had to deal with a postgres database that had a column with data type bytea.  You can find out more information on bytea from http://www.postgresql.org/docs/9.0/static/datatype-binary.html [1]

Now programmatically querying the database gives you an ascii byte stream, which works out just fine.  But if you are just doing a simple command line query it will display each byte as an ascii character.  And for those characters that cannot be displayed for example byte 00000000  which in ascii is NUL, it will display it in an escaped format for example \000  (it’s in octet and always 3 numbers)

So a simple query might return something like this.

pd\323\362r

pd are the ascii characters p and d

in hex

using http://www.asciitable.com/ [2] to convert the ascii

p          = 70

d          = 64

\323     = D3

\362     = F2

r           = 72

This mixed ascii and escaped, for me at least is very hard to read.  I would rather do a query that just returns it in hex to begin with.

Here is a query with a function that will do that

> select encode(data, 'hex') from table where index=1;

Using the encode and ‘hex’ the results would now be

7064d3f272

References

[1]  Documentaion 9.0:Binary Data type 

       http://www.postgresql.org/docs/9.0/static/datatype-binary.html

       Visited 9/2012

[2]  ASCII table 

       http://www.asciitable.com/  

       Visited 9/2012