Thread: Bytea updation

ramasubramanian wrote:
>     How to insert or update a file in a table using the query in postgres
> CREATE TABLE excel_file_upload
> (
>   user_id integer,
>   excel_file bytea
> }
>
> example
> insert into excel_file_upload values(1,file1)
>
> file1 can be any file *.doc,*.xls
>     How i can do this(with out using java or any other language) using query?

Why did you post this to the performance list?

You can use the command line interface psql and
use a large object:

/* import the file as a large object with a unique commentary */
\lo_import 'dontdo.bmp' 'Excel File'

/* read the large object as bytea and insert it */
INSERT INTO excel_file_upload (user_id, excel_file)
   VALUES (1,
           pg_catalog.loread(
              pg_catalog.lo_open(
                 (SELECT DISTINCT l.loid
                  FROM pg_catalog.pg_largeobject l
                     JOIN pg_catalog.pg_description d
                       ON (l.loid = d.objoid)
                  WHERE d.description = 'Excel File'),
                 262144
              ),
              1000000000
           )
          );

/* delete the large object */
SELECT pg_catalog.lo_unlink(
          (SELECT DISTINCT l.loid
           FROM pg_catalog.pg_largeobject l
              JOIN pg_catalog.pg_description d
                ON (l.loid = d.objoid)
           WHERE d.description = 'Excel File')
       );

It would be easier in Java or any other language ...

Yours,
Laurenz Albe

ramasubramanian, 27.05.2009 08:42:
> How to insert or update a file in a table using the query in postgres
> CREATE TABLE excel_file_upload
> (
>   user_id integer,
>   excel_file bytea
> }
>
> example
> insert into excel_file_upload values(1,file1)
>
> file1 can be any file *.doc,*.xls
>     How i can do this(with out using java or any other language)
> using query?

If you are restricted to psql then I gues the only way is the solution show by Albe - but I guess that only works if
thefile is on the server. 

Some of the GUI SQL tools can handle blob "uploads" from the client. So if you are willing to switch to a different SQL
client,this could be done without programming.  

My own tool available at http://www.sql-workbench.net can either do that through a GUI dialog or as part of an extended
SQLsyntax that is of course specific to that application. 

Thomas