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.

3 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.