Wednesday, March 02, 2011

Easy way to import image to bytea field in PostgreSQL/ Postgres

During the migration of our Sybase databases to PostgreSql, I came across a small table with a few records that was used to store images used by the reports. Specially the company logos ..etc. " image " was the data type used in the sybase database and I had to store the same data in postgres data base in a " bytea " field. I tried exporting the sybase table into a csv and import the same csv to postgres which didn't give me successful results. So I had to import the actual image files(which I already had with me) into postgres and I wanted to do it without  doing any java coding. So here's the way I managed to do it,

Step 1 (convert the image to base64 string)

Using an online converter such as,  http://bit.ly/Ee6Ju, convert the image to base64 string.

Step 2 (Insert the baste64 string after decoding it into bytea)

Copy the string from the site and paste it in the PgAdmin query tool within the insert query like below,

INSERT INTO images (image_name, image_data)
VALUES( 'image_one', decode('', 'base64') );

And thats it, your base64 is converted into bytea and inserted in the bytea field of the image table.

8 comments:

volterra79 said...

Thanks for your post, it is usefull.
Now i have a question:
I imported an image into my postgresql table in bytea data type.My question is. Now how can export this image and store in my system? What is the function or query that i can use?

Thanks

Francesco Boccacci

volterra79 said...
This comment has been removed by the author.
volterra79 said...
This comment has been removed by the author.
Franklyn Braudilio Urey Guardado said...

amazing. Tks

pham Thinh said...

Thank!

Anonymous said...

Hi,

This helps alot!
I got the company logo into my postgres DB as binary data but now i need to use this field as a source for a Jaspersoft report image.
But whenever i try to run the report to view the logo it given me a null pointer exception error.

Do you have any advice regarding using this image field in reports on jaspersoft?

André Souza said...

THank you , you saved my life today

Anonymous said...

Hi,

I am working on Postgresql 9.3 and pgAdmin III . I am having a Table in which I have an attribute named image of bytea datatype and I want to insert jpeg type image into the database and also want to view that image in the database

I tried inserting the image using these steps but I am having a doubt what should be the string in insert query to decode . Is it the one that we get when we select the jpeg file and convert it to base64?

And also once the image is inserted how can i view that bytea image in the database?

Any kind of help will be appreciated :)

Thank you :)