Re: insert binary data into a table column with psql

On Tue, 24 Jul 2012 14:37:52 -0700, Lonni J Friedman wrote:

> On Tue, Jul 24, 2012 at 2:22 PM, John R Pierce <pierce(at)hogranch(dot)com>
> wrote:
>> On 07/24/12 1:28 PM, jkells wrote:
>>>
>>> from psql
>>> I have tried several ways including creating a function to read a file
>>> without any success but basically I want to do something like the
>>> following from a bash shell
>>>
>>> psql <connection information> -c "insert into x (ID, load_date, image)
>>> values ($PID,clock_timestamp()::timestamp(0), copy from '/tmp/$FN'
>>> with binary);"
>>>
>>> Any help would be greatly appreciated
>>
>>
>> use a proper programming language that can read files and insert BYTEA
>> data. shell + psql just won't cut it. I'd suggest perl or python or
>> java or
>
> Actually, that's not true. Its definitely possible to INSERT data into
> bytea using just psql. The trick is to sub- 'select' the data with the
> bytea_import function in the INSERT. So something like this: insert
> into x (ID, load_date, image)
> values ($PID,clock_timestamp()::timestamp(0), (SELECT bytea_import(
> '/tmp/$FN'));"

John,

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

John