Bytea request field in postgres via command line

Question

I have a table with a byte field, and it would be convenient if I could make queries through the command line (or pgAdmin query executor). I have a hex value as a string. Is there a built-in function to convert hex to bytea?

I would like to do something like:

SELECT * FROM table WHERE my_bytea_field=some_???_function('fa26e312'); 

where 'fa26e312' is the hexadecimal value of the byte field that I want.

Note: this is just useful when I develop / debug things, I can do this with code, but I would like to be able to do this manually in the request.

Answer 1

Try using the built-in decode(string text, type text) function decode(string text, type text) (it returns bytea ). You can run queries through the CLI using psql in non-interactive mode, i.e. with the -c switch (there are some formatting options if you like):

 psql -c "SELECT * FROM table WHERE my_bytea_field=decode('fa26e312', 'hex');" 

Example:

 CREATE TABLE test(id serial, my_bytea_field bytea); INSERT INTO test (my_bytea_field) VALUES (E'\\320\\170'::bytea), (E'\\100\\070'::bytea), (E'\\377\\377'::bytea); psql -tc "SELECT * FROM test WHERE my_bytea_field=decode('ffff', 'hex');" 3 | \377\377 

Answer 2

 SELECT * FROM table WHERE my_bytea_field=E'\\xfa26e312'; 

As in the example in the Binary Data Types docs (note the prefix E '\\ x'):

 SELECT E'\\xDEADBEEF';