Re: insert binary data into a table column with psql - Mailing list pgsql-general

From Lonni J Friedman
Subject Re: insert binary data into a table column with psql
Date
Msg-id CAP=oouEeX5hf-+Y4Y0ZB9OGiBHYLw8qKYVfQ_SV0PJFbs_BrQQ@mail.gmail.com
Whole thread Raw
In response to Re: insert binary data into a table column with psql  (John R Pierce <pierce@hogranch.com>)
List pgsql-general
On Tue, Jul 24, 2012 at 2:22 PM, John R Pierce <pierce@hogranch.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'));"

pgsql-general by date:

Previous
From: Edson Richter
Date:
Subject: Re: Pg 9.1: Do I need to run vacuum analyze instead vacuum full?
Next
From: Anthony Bull
Date:
Subject: Re: Pg 9.1: Do I need to run vacuum analyze instead vacuum full?