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@xxxxxxxxxxxx> > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
