Re: Bytea updation

From: Albe Laurenz
Subject: Re: Bytea updation
Date: ,
Msg-id: D960CB61B694CF459DCFB4B0128514C202FF6602@exadv11.host.magwien.gv.at
(view: Whole thread, Raw)
In response to: Bytea updation  ("ramasubramanian")
List: pgsql-performance

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


pgsql-performance by date:

From: Heikki Linnakangas
Date:
Subject: Re: Improve Query
From: Alan McKay
Date:
Subject: Postgres Clustering