Inserting text string with hex into PostgreSQL as a bytea

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.