CODEWITHSUNDEEP
postgresql
John Smith
John Smith

Reputation: 113

Query bytea field in postgres via command line

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.

Upvotes: 11

Views: 25742

Answers (3)

Siwei
Siwei

Reputation: 21549

under psql console, you can see its bytea content by default, such as:

enter image description here

also you can query like this (such as in pg_admin) :

xxx=# select id, encode(code_hash, 'hex') from your_table where id = 1195;

  id  |                              encode
------+------------------------------------------------------------------
 1195 | c5e5dcf215925f7ef4dfaf5f4b4f105bc321c02776d6e7d52a1db3fcd9d011a4

Upvotes: 0

Garen
Garen

Reputation: 961

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';

Upvotes: 4

Grzegorz Szpetkowski
Grzegorz Szpetkowski

Reputation: 37904

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

Upvotes: 12

Related Questions