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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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