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'\x7f\x7f'); 

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

insert into mytable (testcol) values (E'\x7f\x80'); 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       ------------------  \336\255\276\357 

which is more understandable with 9.0's default output:

   decode    ------------  \xdeadbeef 

The reason you can't just say E'\xDE\xAD\xBE\xEF' 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.

Answer 2

 INSERT INTO   mytable (testcol) VALUES   (decode('013d7d16d7ad4fefb61bd95b765c8ceb', 'hex'))