Thread: How can I insert the image as a blob in the table

How can I insert the image as a blob in the table

From
Igor Korot
Date:
Hi, ALL,
I have an image on my hard drive and I'd like to store it in the BLOB
column of the images table.

Is there a simple SQL to do that in PostgreSQL?

Thank you.



Re: How can I insert the image as a blob in the table

From
"David G. Johnston"
Date:
On Sun, Apr 11, 2021 at 2:04 PM Igor Korot <ikorot01@gmail.com> wrote:
Hi, ALL,
I have an image on my hard drive and I'd like to store it in the BLOB
column of the images table.

Is there a simple SQL to do that in PostgreSQL?


SQL proper has no concept of "your hard drive".  You need to define what you plan to use as an in between layer that can produce SQL AND see "your hard drive" (i.e., what database client).  Also, BLOB isn't an actual thing in PostgreSQL proper (though generic client APIs may use that term), so I presume you mean to store the contents in a "bytea" typed column.  You could also convert the binary to a encoded text compatible type (e.g., base64) and use a text field instead.

If you can decide on what client interface you want to use there should be existing resources on the web walking through how to do this using that client interface.

David J.

Re: How can I insert the image as a blob in the table

From
Igor Korot
Date:
Hi, David,


On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Sun, Apr 11, 2021 at 2:04 PM Igor Korot <ikorot01@gmail.com> wrote:
>>
>> Hi, ALL,
>> I have an image on my hard drive and I'd like to store it in the BLOB
>> column of the images table.
>>
>> Is there a simple SQL to do that in PostgreSQL?
>>
>
> SQL proper has no concept of "your hard drive".  You need to define what you plan to use as an in between layer that
canproduce SQL AND see "your hard drive" (i.e., what database client).  Also, BLOB isn't an actual thing in PostgreSQL
proper(though generic client APIs may use that term), so I presume you mean to store the contents in a "bytea" typed
column. You could also convert the binary to a encoded text compatible type (e.g., base64) and use a text field
instead.
>
> If you can decide on what client interface you want to use there should be existing resources on the web walking
throughhow to do this using that client interface. 

Something like this:

INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );

Looking to run it from gAdmin in the Terminal.

Thank you.

>
> David J.
>



Re: How can I insert the image as a blob in the table

From
"David G. Johnston"
Date:
On Sunday, April 11, 2021, Igor Korot <ikorot01@gmail.com> wrote:
Hi, David,


On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

> If you can decide on what client interface you want to use there should be existing resources on the web walking through how to do this using that client interface.

Something like this:

INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );

Looking to run it from gAdmin in the Terminal.

As SQL is executed on the server there is the major issue of the server being unable to resolve /home on the client machine.  But maybe pgAdmin has its own way to deal with this.  Have you read its documentation?

David J.

Re: How can I insert the image as a blob in the table

From
"David G. Johnston"
Date:
On Sunday, April 11, 2021, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sunday, April 11, 2021, Igor Korot <ikorot01@gmail.com> wrote:
Hi, David,


On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

> If you can decide on what client interface you want to use there should be existing resources on the web walking through how to do this using that client interface.

Something like this:

INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );

Looking to run it from gAdmin in the Terminal.

As SQL is executed on the server there is the major issue of the server being unable to resolve /home on the client machine.  But maybe pgAdmin has its own way to deal with this.  Have you read its documentation?


Or:


David J.

Re: How can I insert the image as a blob in the table

From
Igor Korot
Date:
Hi, David,

On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Sunday, April 11, 2021, Igor Korot <ikorot01@gmail.com> wrote:
>>
>> Hi, David,
>>
>>
>> On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>>
>> > If you can decide on what client interface you want to use there should be existing resources on the web walking
throughhow to do this using that client interface.
 
>>
>> Something like this:
>>
>> INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );
>>
>> Looking to run it from gAdmin in the Terminal.
>
>
> As SQL is executed on the server there is the major issue of the server being unable to resolve /home on the client
machine. But maybe pgAdmin has its own way to deal with this.  Have you read its documentation?
 

I don't know - server is running on the same OSX machine as pgAdmin will be.

Thank you.

>
> David J.
>



Re: How can I insert the image as a blob in the table

From
Igor Korot
Date:
Hi, guys,

On Sun, Apr 11, 2021 at 7:08 PM Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi, David,
>
> On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
> >
> > On Sunday, April 11, 2021, Igor Korot <ikorot01@gmail.com> wrote:
> >>
> >> Hi, David,
> >>
> >>
> >> On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
> >> <david.g.johnston@gmail.com> wrote:
> >>
> >> > If you can decide on what client interface you want to use there should be existing resources on the web walking
throughhow to do this using that client interface.
 
> >>
> >> Something like this:
> >>
> >> INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );
> >>
> >> Looking to run it from gAdmin in the Terminal.
> >
> >
> > As SQL is executed on the server there is the major issue of the server being unable to resolve /home on the client
machine. But maybe pgAdmin has its own way to deal with this.  Have you read its documentation?
 
>
> I don't know - server is running on the same OSX machine as pgAdmin will be.

So nobody is playing with the images?

Is it even possible - to insert an image as BLOB into the database
using a simple
INSERT INTO query?

Thank you.

>
> Thank you.
>
> >
> > David J.
> >



Re: How can I insert the image as a blob in the table

From
Rob Sargent
Date:
>
On Apr 12, 2021, at 7:29 PM, Igor Korot <ikorot01@gmail.com> wrote:

Hi, guys,

On Sun, Apr 11, 2021 at 7:08 PM Igor Korot <ikorot01@gmail.com> wrote:

Hi, David,

On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Sunday, April 11, 2021, Igor Korot <ikorot01@gmail.com> wrote:

Hi, David,


On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

If you can decide on what client interface you want to use there should be existing resources on the web walking through how to do this using that client interface.

Something like this:

INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );

Looking to run it from gAdmin in the Terminal.


As SQL is executed on the server there is the major issue of the server being unable to resolve /home on the client machine.  But maybe pgAdmin has its own way to deal with this.  Have you read its documentation?

I don't know - server is running on the same OSX machine as pgAdmin will be.

So nobody is playing with the images?

Is it even possible - to insert an image as BLOB into the database
using a simple
INSERT INTO query?

Many of us ‘play with images’.  We use tools to write to the database.  I’m currently using java to write millions of records into the database include bytea columns with 50-100K binary data.  What tools do you have at hand?
It’s difficult to get an image into a string like ‘insert into tablename values (<what would go here>)’



Re: How can I insert the image as a blob in the table

From
Ron
Date:
On 4/12/21 8:29 PM, Igor Korot wrote:
> Hi, guys,
>
> On Sun, Apr 11, 2021 at 7:08 PM Igor Korot <ikorot01@gmail.com> wrote:
>> Hi, David,
>>
>> On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>>> On Sunday, April 11, 2021, Igor Korot <ikorot01@gmail.com> wrote:
>>>> Hi, David,
>>>>
>>>>
>>>> On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
>>>> <david.g.johnston@gmail.com> wrote:
>>>>
>>>>> If you can decide on what client interface you want to use there should be existing resources on the web walking
throughhow to do this using that client interface.
 
>>>> Something like this:
>>>>
>>>> INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );
>>>>
>>>> Looking to run it from gAdmin in the Terminal.
>>>
>>> As SQL is executed on the server there is the major issue of the server being unable to resolve /home on the client
machine. But maybe pgAdmin has its own way to deal with this.  Have you read its documentation?
 
>> I don't know - server is running on the same OSX machine as pgAdmin will be.
> So nobody is playing with the images?
>
> Is it even possible - to insert an image as BLOB into the database
> using a simple
> INSERT INTO query?

psql only understands text input.  Therefore, the image must be text (like 
base64) or hex with a leading "\\x".

-- 
Angular momentum makes the world go 'round.



Re: How can I insert the image as a blob in the table

From
Abdul Qoyyuum
Date:
Have you tried

insert into table(picture) values(lo_import('D:\image.jpg'));
?


On Tue, Apr 13, 2021 at 9:30 AM Igor Korot <ikorot01@gmail.com> wrote:
Hi, guys,

On Sun, Apr 11, 2021 at 7:08 PM Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi, David,
>
> On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
> >
> > On Sunday, April 11, 2021, Igor Korot <ikorot01@gmail.com> wrote:
> >>
> >> Hi, David,
> >>
> >>
> >> On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
> >> <david.g.johnston@gmail.com> wrote:
> >>
> >> > If you can decide on what client interface you want to use there should be existing resources on the web walking through how to do this using that client interface.
> >>
> >> Something like this:
> >>
> >> INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );
> >>
> >> Looking to run it from gAdmin in the Terminal.
> >
> >
> > As SQL is executed on the server there is the major issue of the server being unable to resolve /home on the client machine.  But maybe pgAdmin has its own way to deal with this.  Have you read its documentation?
>
> I don't know - server is running on the same OSX machine as pgAdmin will be.

So nobody is playing with the images?

Is it even possible - to insert an image as BLOB into the database
using a simple
INSERT INTO query?

Thank you.

>
> Thank you.
>
> >
> > David J.
> >




--
Abdul Qoyyuum Bin Haji Abdul Kadir
HP No: +673 720 8043

Re: How can I insert the image as a blob in the table

From
Rob Sargent
Date:


On Apr 12, 2021, at 8:31 PM, Abdul Qoyyuum <aqoyyuum@cardaccess.com.au> wrote:

Have you tried

insert into table(picture) values(lo_import('D:\image.jpg'));
?

No, I’m doing some non-standard compression on the way down.