How to store image into postgreSQL

[Is_celine]

Has anyone tried storing images in PostgreSQL using KNIME? How was the workflow? Below is my current workflow, I have no idea what should I do now.

[MartinDDDD]

Well I think it depends on what you want to achieve.

What I think won’t work is storing a plain image as jpg or png.

You definitely can store a link (although by the looks of it that’s not what you seem to be after) or probably an image converted to binary format.

There’s PNG to binary node:

PNGs to Binary Objects

And for non png you could use ImgPlus to png

ImgPlus to PNG Images

To then get int into the DB there’s an example from @takbb here:

Inserting Binary Data into SQL Database Using KNIME

Hi @SnigdhaBiswas , I’ve put a demo workflow on the hub, using the H2 database as an example. This uploads some binary files from the workflow’s data folder, and then reads the binary data back from the database, for display in a KNIME table. [image] On the H2 database, I defined the column as BLOB datatype. create table IMAGES (ID numeric(5), IMAGE_BINARY blob) In the DB Insert node, I set the data type mapping for Binary object as follows: [image] It didn’t work if I set it mapping …

In general I think you have to set the column in your DB to type binary in the insert node.

[ActionAndi]

I totally agree with Martin. You have to convert the images into binary format before you can store them in a database.

But be sure if you have many of them it may bloat your DB.

[Is_celine]

May I know is there any way to convert binary object?

[ActionAndi]

Check the link Martin provided

[mlauber71]

@Is_celine you can create an entry in a PostgeSQL database like this

CREATE TABLE images (
    id NUMERIC(5),
    image_binary BYTEA
);

And then upload a binary file. I adapted an example by @takbb .

DB SQL - PostgreSQL - Upload binary files to database – mlauber71

[Is_celine]

Now i having an issues in converting binary format, there’s no data under the out_image_binary. I have formatting the image into PNG format then use PNG to Binary Objects node to convert it, but it couldn’t work.

[MartinDDDD]

I don’t have a computer with me right now as I’m traveling, but looking at the screenshot: are you sure images are of type png (from Knime data type perspective)?

Can you use imgPlus node I shared above to convert image column to png and then use that to turn into binary?

[Is_celine]

I’m not sure is it correct cuz there is missing value under the out_image_binary column

[MartinDDDD]

What’s the Java snippet doing?

[Is_celine]

Convert it to PNG format. If I did not use this code, the ImPlus node could not connect to PNG to binary object

[ActionAndi]

Hi,
isn’t the “ImgPlus to PNG Images” node creating png format?

There’s a “PNG Image to ImgPlus” Node. If you place it behind the java node it should produce exactly the same data which enters the “ImgPlus to PNG Images”.