Re: Inserting into the blob

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