insert binary data into a table column with psql

[jkells]

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

Any help would be greatly appreciated

John

[John R Pierce]

Post by jkells
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 whatever.

[Lonni J Friedman]

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

[jtkells]

Post by John R Pierce

Post by jkells
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

[Tom Lane]

Post by jtkells
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

Maybe use psql's \lo_import command to suck the data into a "large
object" on the server, and then use loread() to insert it into the
target table? (And don't forget to drop the large object after.)
Pretty grotty but I think it might be the only solution with the
currently available tools.

btw, does "bytea_import" actually exist? It's not in the core
server for sure.

regards, tom lane

[Lonni J Friedman]

Post by Tom Lane

Post by jtkells
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

Maybe use psql's \lo_import command to suck the data into a "large
object" on the server, and then use loread() to insert it into the
target table? (And don't forget to drop the large object after.)
Pretty grotty but I think it might be the only solution with the
currently available tools.
btw, does "bytea_import" actually exist? It's not in the core
server for sure.

Doh. No, its not standard, I found it here:
http://dba.stackexchange.com/questions/1742/how-to-insert-file-data-into-a-postgresql-bytea-column/2962#2962

[Thomas Kellerer]

Post by jtkells
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.

If you can use a different client, you might want to look at SQL Workbench/J

It has support to do just that. You can write e.g.


insert into x (id, load_date, image)
values
(1, current_date, {$blobfile=/tmp/myimage.jpg});

and the file will be read from the client (where SQL Workbench is running).
(note that "large objects" are _not_ supported, only bytea)

http://www.sql-workbench.net/
http://www.sql-workbench.net/manual/using.html#blob-support

It's Java/JDBC based, has a GUI and console mode.

Disclaimer: I am the author of that tool.

Regards
Thomas

[Jasen Betts]

Post by jkells
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

[Jasen Betts]

Post by Jasen Betts

Post by jkells
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);"

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"