Обсуждение: Inserting into the blob
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.
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
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
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
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
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.
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
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!
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
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
> 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 > >
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:
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.
>> 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.