Re: insert binary data into a table column with psql

On 2012-07-24, jkells <jtkells(at)verizon(dot)net> wrote:
> I am running REDHAT 5.5 64 bit with PostgreSQL 8.4.7 64 bit. I am trying
> to load a binary file into a bytea column into a table without any luck
> from psql.
> On the linux server a script is looking for a file and if found would
> create a record into a table that contains a ID, date of load and the
> file in the bytea column.
> table looks like the following
> table x
> (ID number,
> load_date date,
> image bytea
> )
> 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);"

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 you can write a function to read files, you'll
probably need to use an "untrusted" language, eg plpythonu

--
⚂⚃ 100% natural