Re: insert binary data into a table column with psql

On Tue, Jul 24, 2012 at 7:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> jtkells <jtkells(at)verizon(dot)net> writes:
>> Thanks much for your reply, that does the trick quite nicely. But, I just
>> came to the realization that this only works if your are running the
>> client and the file both resides on the database server. I thought that
>> I would be able to do this from a remote server where the client was
>> running, picking up a local file and sending it into a remote database
>> table. Unless I am missing something, I cant. I can create a temp table
>> on this server, upload the file to a similar table then replicate it to
>> the targeted server.. Lots of work that could be easily done with a
>> programming language (as someone else posted) but thats what I have to
>> work with for now and I have no direct access (I.e. sftp ) to the
>> database server
>
> Maybe use psql's \lo_import command to suck the data into a "large
> object" on the server, and then use loread() to insert it into the
> target table? (And don't forget to drop the large object after.)
> Pretty grotty but I think it might be the only solution with the
> currently available tools.
>
> btw, does "bytea_import" actually exist? It's not in the core
> server for sure.

Doh. No, its not standard, I found it here:
http://dba.stackexchange.com/questions/1742/how-to-insert-file-data-into-a-postgresql-bytea-column/2962#2962