current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 1 down vote favorite

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;

my view when querying this table

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer
    
Thanks for this. It's exactly what I wanted. :D I found that for very large images even encoding to hex wasn't enough. But limiting to the first 40 chars was what I needed :D – DropTheTable Oct 31 '14 at 14:47
up vote 2 down vote

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
share|improve this answer
    
Thanks for these suggestions. I marked the other as the answer as I said "Ideally the first 'x' characters would display" in my question. However these are great alternatives :D – DropTheTable Oct 31 '14 at 14:48
up vote 1 down vote

You can toggle expanded formatting mode with the following command

\x
share|improve this answer
1  
Yep, this is all about the formatting of the output in psql, not about the queries. – Craig Ringer Oct 30 '14 at 21:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.