Clever Geek Handbook

PostgreSQL psql command line string bytea column

I have a database of images. With a table of images with two columns bytea

. I would like to query the table and see these columns to see if these records store images or not. As they are nullable columns.

Currently, when I query the DB using psql command line, the whole command line screen is blank trying to display bytes.

I have searched the web for a while and cannot find a way to display the table in a suitable way. Ideally, the first "x" characters will be displayed.

I can confirm that bytea will print if it's small enough by uploading a tiny tiny image.

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

my view when querying this table

+3


source to share


3 answers


you can use the function encode

:

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

If you only want to see the first bytes, just use the left()

function:



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

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

+4


source


If you're 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
+3


source


You can switch the advanced formatting mode with the following command

\x
+1


source






More articles:


All Articles