Query bytea field in postgres via command line

Question

I have a table with a bytea field, and it would be convenient if I could do queries via the command line (or pgAdmin's query executor). I've got the hex value as a string. Is there a built in function to convert hex to bytea?

I'd like to do something like:

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

where 'fa26e312' is the hex value of the bytea field I want.

Note: this is just to be helpful while I'm developing / debugging things, I can do it via code but I'd like to be able to do it by hand in a query.

Answer 1

Try using built-in decode(string text, type text) function (it returns bytea). You can run queries via CLI using psql in non-interactive mode, that is with -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';

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

SELECT E'\\xDEADBEEF';