Re: insert binary data into a table column with psql

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'));"