Thread: Inserting into the blob

Inserting into the blob

From
Igor Korot
Date:
Hi, ALL,
How do I insert the png file (content, not a name) into the BLOB field
in the table i PostgreSQL DB?

Thank you.



Re: Inserting into the blob

From
Igor Korot
Date:
Hi, Adrian,

On Sun, Jun 9, 2019 at 11:14 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 6/9/19 8:28 AM, Igor Korot wrote:
> > Hi, ALL,
> > How do I insert the png file (content, not a name) into the BLOB field
> > in the table i PostgreSQL DB?
>
> Are you talking about this data type?:
>
> https://www.postgresql.org/docs/11/datatype-binary.html
>
> Or this?:
>
> https://www.postgresql.org/docs/11/largeobjects.html

Which one is best to hold an images of unknown size?

>
> What client/language are you using?

psql if possible.

Thank you.

>
>
> >
> > Thank you.
> >
> >
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com



Re: Inserting into the blob

From
Adrian Klaver
Date:
On 6/9/19 8:28 AM, Igor Korot wrote:
> Hi, ALL,
> How do I insert the png file (content, not a name) into the BLOB field
> in the table i PostgreSQL DB?

Are you talking about this data type?:

https://www.postgresql.org/docs/11/datatype-binary.html

Or this?:

https://www.postgresql.org/docs/11/largeobjects.html

What client/language are you using?


> 
> Thank you.
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Inserting into the blob

From
Igor Korot
Date:
Hi, Adrian,

On Mon, Jun 10, 2019 at 7:03 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 6/10/19 9:30 AM, Igor Korot wrote:
>
> >
> > According to
https://stackoverflow.com/questions/16048649/postgresql-9-x-pg-read-binary-file-inserting-files-into-bytea,
> > the file needs to be on the server inside PGDATA directory.
> > It is not a problem in general, but just curious - is there a more
> > generic solution (to get the file from the client)?
>
> This would depend on what is is you are trying to accomplish:
>
> 1) Are you really wanting to insert a file at a time at the psql command
> line?

Yes.

>
> 2) If not then is there a program you are using/writing that will insert
> the data?

More like the program will query for the data...

Thank you.

>
> >
> > Thank you.
> >
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com



Re: Inserting into the blob

From
Adrian Klaver
Date:
On 6/9/19 10:06 AM, Igor Korot wrote:
> Hi, Adrian,
> 
> On Sun, Jun 9, 2019 at 11:14 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 6/9/19 8:28 AM, Igor Korot wrote:
>>> Hi, ALL,
>>> How do I insert the png file (content, not a name) into the BLOB field
>>> in the table i PostgreSQL DB?
>>
>> Are you talking about this data type?:
>>
>> https://www.postgresql.org/docs/11/datatype-binary.html
>>
>> Or this?:
>>
>> https://www.postgresql.org/docs/11/largeobjects.html
> 
> Which one is best to hold an images of unknown size?

Probably bytea as it is easier to work with overall.

> 
>>
>> What client/language are you using?
> 
> psql if possible.

Take a look at(NOTE: need to be superuser):

https://www.postgresql.org/docs/current/functions-admin.html

pg_read_binary_file is similar to pg_read_file, except that the result 
is a bytea value; accordingly, no encoding checks are performed. In 
combination with the convert_from function, this function can be used to 
read a file in a specified encoding:

> 
> Thank you.
> 
>>
>>
>>>
>>> Thank you.
>>>
>>>
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Inserting into the blob

From
Igor Korot
Date:
Hi, David,

On Mon, Jun 10, 2019 at 10:45 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Mon, Jun 10, 2019 at 8:32 PM Rob Sargent <robjsargent@gmail.com> wrote:
>>
>> > On Jun 10, 2019, at 6:40 AM, Igor Korot <ikorot01@gmail.com> wrote:
>> >
>> > Hi, Adrian,
>> >
>> >> On Mon, Jun 10, 2019 at 7:03 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> >>
>> >>> On 6/10/19 9:30 AM, Igor Korot wrote:
>> >>>
>> >>> According to
https://stackoverflow.com/questions/16048649/postgresql-9-x-pg-read-binary-file-inserting-files-into-bytea,
>> >>> the file needs to be on the server inside PGDATA directory.
>> >>> It is not a problem in general, but just curious - is there a more
>> >>> generic solution (to get the file from the client)?
>> >>
>> >> This would depend on what is is you are trying to accomplish:
>> >>
>> >> 1) Are you really wanting to insert a file at a time at the psql command
>> >> line?
>> >
>> > Yes.
>> Gnarly. I suppose you could open the pdf in emacs and tell emacs to  NOT render it. Cut the entire buffer and paste
it,properly quoted, into your psql command line. But \lo stuff seems much more likely to work.
 
>>
>
> This is what I've used for text:
>
>     \set file_content `cat './file.txt'`
>     SELECT:'file_content';
>
> For smallish files I'd probably just stick with the same theme but encode the binary data as Base64 and then decode
itinto the bytea field.
 
>
> For not-so-small files probably better off storing the content elsewhere and inserting location data into the
database.
>
> I have not yet had the desire to incorporate the large object API into my designs.

You are lucky you didn't work with the face databases...
Or photographs...

Thank you.

>
> David J.



Re: Inserting into the blob

From
Igor Korot
Date:
Hi, Adrian,

On Mon, Jun 10, 2019 at 8:38 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 6/9/19 10:06 AM, Igor Korot wrote:
> > Hi, Adrian,
> >
> > On Sun, Jun 9, 2019 at 11:14 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> >>
> >> On 6/9/19 8:28 AM, Igor Korot wrote:
> >>> Hi, ALL,
> >>> How do I insert the png file (content, not a name) into the BLOB field
> >>> in the table i PostgreSQL DB?
> >>
> >> Are you talking about this data type?:
> >>
> >> https://www.postgresql.org/docs/11/datatype-binary.html
> >>
> >> Or this?:
> >>
> >> https://www.postgresql.org/docs/11/largeobjects.html
> >
> > Which one is best to hold an images of unknown size?
>
> Probably bytea as it is easier to work with overall.

OK.

>
> >
> >>
> >> What client/language are you using?
> >
> > psql if possible.
>
> Take a look at(NOTE: need to be superuser):
>
> https://www.postgresql.org/docs/current/functions-admin.html
>
> pg_read_binary_file is similar to pg_read_file, except that the result
> is a bytea value; accordingly, no encoding checks are performed. In
> combination with the convert_from function, this function can be used to
> read a file in a specified encoding:

According to
https://stackoverflow.com/questions/16048649/postgresql-9-x-pg-read-binary-file-inserting-files-into-bytea,
the file needs to be on the server inside PGDATA directory.
It is not a problem in general, but just curious - is there a more
generic solution (to get the file from the client)?

Thank you.

>
> >
> > Thank you.
> >
> >>
> >>
> >>>
> >>> Thank you.
> >>>
> >>>
> >>>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.klaver@aklaver.com
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com



Re: Inserting into the blob

From
Matthias Apitz
Date:
El día Monday, June 10, 2019 a las 11:30:42AM -0500, Igor Korot escribió:

> 
> According to
https://stackoverflow.com/questions/16048649/postgresql-9-x-pg-read-binary-file-inserting-files-into-bytea,
> the file needs to be on the server inside PGDATA directory.
> It is not a problem in general, but just curious - is there a more
> generic solution (to get the file from the client)?

When we migrated a Sybase database to PG, we used the format TEXT for
the COPY command and loaded all image data too. The column of the image
data must be coded in 2-byte hex values and must have \\x in front of
it. The file was just in "user land", i.e.

COPY tablename FROM 'myfile' ....

    matthias 

-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!



Re: Inserting into the blob

From
"Daniel Verite"
Date:
    Igor Korot wrote:

> It is not a problem in general, but just curious - is there a more
> generic solution (to get the file from the client)?

With psql:

 \lo_import /path/to/file

It creates a large object with the contents of the file from the client
file system and returns its unique ID, in the output and into the
:LASTOID variable in psql.

There's no equivalent for bytea contents. Bytea contents need
to be either injected into the query as text, or passed separately
as parameters, but psql does not provide helper methods for this,
and it also lack binary support for variables.
So it's easier to implement "upload bytea to server" in a script language
than in psql.

Alternatively, if you don't care about the contents being written twice, a
file can be imported as a large object, copied as bytea into a row of the
target table, and the large object purged immediately.

In psql, a sequence like this should work:

 \lo_import /path/to/file
 \set tmp_oid :LASTOID

insert into tablename ( bytea_col, [other columns] )
 values ( lo_get(:tmp_oid), [other values] )

\lo_unlink :tmp_oid


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite



Re: Inserting into the blob

From
Adrian Klaver
Date:
On 6/10/19 9:30 AM, Igor Korot wrote:

> 
> According to
https://stackoverflow.com/questions/16048649/postgresql-9-x-pg-read-binary-file-inserting-files-into-bytea,
> the file needs to be on the server inside PGDATA directory.
> It is not a problem in general, but just curious - is there a more
> generic solution (to get the file from the client)?

This would depend on what is is you are trying to accomplish:

1) Are you really wanting to insert a file at a time at the psql command 
line?

2) If not then is there a program you are using/writing that will insert 
the data?

> 
> Thank you.
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Inserting into the blob

From
Rob Sargent
Date:

> On Jun 10, 2019, at 6:40 AM, Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi, Adrian,
>
>> On Mon, Jun 10, 2019 at 7:03 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>>> On 6/10/19 9:30 AM, Igor Korot wrote:
>>>
>>>
>>> According to
https://stackoverflow.com/questions/16048649/postgresql-9-x-pg-read-binary-file-inserting-files-into-bytea,
>>> the file needs to be on the server inside PGDATA directory.
>>> It is not a problem in general, but just curious - is there a more
>>> generic solution (to get the file from the client)?
>>
>> This would depend on what is is you are trying to accomplish:
>>
>> 1) Are you really wanting to insert a file at a time at the psql command
>> line?
>
> Yes.
Gnarly. I suppose you could open the pdf in emacs and tell emacs to  NOT render it. Cut the entire buffer and paste it,
properlyquoted, into your psql command line. But \lo stuff seems much more likely to work.  
>
>>
>> 2) If not then is there a program you are using/writing that will insert
>> the data?
>
> More like the program will query for the data...
>
> Thank you.
>
>>
>>>
>>> Thank you.
>>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
>



Re: Inserting into the blob

From
"David G. Johnston"
Date:
On Mon, Jun 10, 2019 at 8:32 PM Rob Sargent <robjsargent@gmail.com> wrote:
> On Jun 10, 2019, at 6:40 AM, Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi, Adrian,
>
>> On Mon, Jun 10, 2019 at 7:03 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>>> On 6/10/19 9:30 AM, Igor Korot wrote:
>>>
>>> According to https://stackoverflow.com/questions/16048649/postgresql-9-x-pg-read-binary-file-inserting-files-into-bytea,
>>> the file needs to be on the server inside PGDATA directory.
>>> It is not a problem in general, but just curious - is there a more
>>> generic solution (to get the file from the client)?
>>
>> This would depend on what is is you are trying to accomplish:
>>
>> 1) Are you really wanting to insert a file at a time at the psql command
>> line?
>
> Yes.
Gnarly. I suppose you could open the pdf in emacs and tell emacs to  NOT render it. Cut the entire buffer and paste it, properly quoted, into your psql command line. But \lo stuff seems much more likely to work.


This is what I've used for text:

    \set file_content `cat './file.txt'`
    SELECT:'file_content';

For smallish files I'd probably just stick with the same theme but encode the binary data as Base64 and then decode it into the bytea field.

For not-so-small files probably better off storing the content elsewhere and inserting location data into the database.

I have not yet had the desire to incorporate the large object API into my designs.

David J.

Re: Inserting into the blob

From
Rob Sargent
Date:



>> 1) Are you really wanting to insert a file at a time at the psql command
>> line?
>
> Yes.
Gnarly. I suppose you could open the pdf in emacs and tell emacs to  NOT render it. Cut the entire buffer and paste it, properly quoted, into your psql command line. But \lo stuff seems much more likely to work.


This is what I've used for text:

    \set file_content `cat './file.txt'`
    SELECT:'file_content';

For smallish files I'd probably just stick with the same theme but encode the binary data as Base64 and then decode it into the bytea field.

For not-so-small files probably better off storing the content elsewhere and inserting location data into the database.

I have not yet had the desire to incorporate the large object API into my designs.

David J.
Yes if you’re comfortable managing the great leap of faith that the file remains where it once claimed to be. And of course the other camp must contend with what could be excessively large database data directories.