Inserting into the blob

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.

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.

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

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

Thank you.

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.

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, properly quoted, into your psql command line. But \lo stuff seems much more likely to work.

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.

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.