Re: insert binary data into a table column with psql

On 2012-07-30, Jasen Betts <jasen@xnet.co.nz> wrote:

>> 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);"
>
> for small files (44kB?) you can do this:
>
> psql "$CONNECTION_INFO" -c "insert into x (ID, load_date, image) values
>    ($PID,'now', decode('`/usr/bin/base64 < /tmp/$FN`','base64'))"
>

For larger files ( upto 1Gib ) you need to stream it.

(
   echo "insert into x (ID, load_date, image) values ($PID,'now', decode('"
   /usr/bin/base64 < /tmp/$FN
   echo "','base64'));"
) | psql "$CONNECTION_INFO"

--
⚂⚃ 100% natural