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.

The new (9.0+) "hex" format" is not working for you?

Milen A. Radev
– Milen A. Radev

2011-07-09 22:36:22 +00:00
Commented Jul 9, 2011 at 22:36 — Milen A. Radev
– Milen A. Radev, Commented Jul 9, 2011 at 22:36

Grzegorz Szpetkowski · Accepted Answer · 2011-07-10 02:05:19Z

12

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

edited Jul 10, 2011 at 2:05

answered Jul 10, 2011 at 1:49

Grzegorz Szpetkowski

38.1k6 gold badges94 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Garen · Accepted Answer · 2013-08-15 20:51:59Z

4

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

answered Aug 15, 2013 at 20:51

Garen

9632 gold badges9 silver badges21 bronze badges

Comments

Siwei · Accepted Answer · 2022-10-10 09:51:13Z

0

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

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

answered Oct 10, 2022 at 9:51

Siwei

21.9k7 gold badges84 silver badges103 bronze badges

Collectives™ on Stack Overflow

Query bytea field in postgres via command line

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related