sql - Inserting text string with hex into PostgreSQL as a bytea

Question

I have a text file with several strings of hex in it:

013d7d16d7ad4fefb61bd95b765c8ceb
007687fc64b746569616414b78c81ef1

I would like to store these in the database as a bytea, instead of a varchar. That is, I would like the database to store 01 as the single byte 00000001, not characters '0' & '1'.

I can easily run this file through sed to format/escape it any way I need to.

This is what I have tried:

create table mytable (testcol BYTEA);

This works:

insert into mytable (testcol) values (E'x7fx7f');

However, as soon as I have a byte that goes above x7f, I get this error:

insert into mytable (testcol) values (E'x7fx80');
ERROR:  invalid byte sequence for encoding "UTF8": 0x80

Any ideas, or am I approaching things wrong?

Answer 1

You can convert a hex string to bytea using the decode function (where "encoding" means encoding a binary value to some textual value). For example:

select decode('DEADBEEF', 'hex');
      decode      
------------------
 336255276357

which is more understandable with 9.0's default output:

   decode   
------------
 xdeadbeef

The reason you can't just say E'xDExADxBExEF' is that this is intended to make a text value, not a bytea, so Postgresql will try to convert it from the client encoding to the database encoding. You could write the bytea escape format like that, but you need to double the backslashes: E'\336\255\276\357'::bytea. I think you can see why the bytea format is being changed.... IMHO the decode() function is a reasonable way of writing inputs, even though there is some overhead involved.