PostgreSQL psql command line display bytea column

Question

I have an image database. With an image table with two bytea columns. I would like to query the table and see these columns to be able to see whether or not these entries are storing images or not. As they are nullable columns.

Currently when I query the DB using psql command line, my whole command line screen goes blank trying to display the bytea.

I've googled around for a while now and can't find a way to display the table suitably. Ideally the first 'x' characters would display.

I can confirm that the bytea will print if small enough by uploading a tiny tiny picture.

This is what I see when I do: SELECT * FROM Image;

Answer 1

you can use the encode function:

select encode(bytea_column, 'hex')
from image;

If you only want to see the first bytes, just use the left() function on that:

select left(encode(bytea_column, 'hex'), 40)
from image;

More details in the manual:
http://www.postgresql.org/docs/current/static/functions-binarystring.html

Answer 2

If you are not trying to interpret binary data yourself, why not just do:

select length(img1), length(img2) from Image

or

select img1 is null, img2 is null from Image

Answer 3

You can toggle expanded formatting mode with the following command

\x