Skip site navigation (1) Skip section navigation (2)

Re: Bytea updation

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "ramasubramanian *EXTERN*" <ramasubramanian(dot)g(at)renaissance-it(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Bytea updation
Date: 2009-05-27 11:23:14
Message-ID: D960CB61B694CF459DCFB4B0128514C202FF6602@exadv11.host.magwien.gv.at (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-performance by date

Next:From: Thomas KellererDate: 2009-05-27 11:43:10
Subject: L
Previous:From: Dimitri FontaineDate: 2009-05-27 09:07:27
Subject: Re: Hosted servers with good DB disk performance?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group