Обсуждение: insert binary data into a table column with psql
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
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 whatever. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
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'));"
On Tue, 24 Jul 2012 14:37:52 -0700, Lonni J Friedman wrote: > 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'));" 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
jtkells <jtkells@verizon.net> writes:
> 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
On Tue, Jul 24, 2012 at 7:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > jtkells <jtkells@verizon.net> writes: >> 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
jtkells, 25.07.2012 03:43:
> 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
On 2012-07-24, jkells <jtkells@verizon.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
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