Re: How to store text files in the postgresql?

On 06/06/2009 11:41, DimitryASuplatov wrote:

> My task is to store a lot (10^5) of small ( <10 MB) text files in the
> database with the ability to restore them back to the hard drive on
> demand.
>
> That means that I need two functions. First - grab file from the
> directory, store it in the database and delete from the disk; second -
> recreate in back to the disk.
>
>
> 1/ Is it possible?

Yes. You can do this in your favourite scripting language - PHP, Perl,
etc etc.

> 2/ Could you give me some quick tips on how to manage it from the start
> so that I knew what to look for in the manual?

You haven't given much detail on your needs, but something like this
should do you:

create table the_files (
file_id serial,
file_data text,
...[any other metatdata you need]...
constraint files_pk primary key (file_id)
);

The "serial" type is a pseudo-type which creates a sequence, which in
turn supplies incrementing integers, while "text" allows you to store
strings of arbitrary length. - Look them up in the docs under "Data types".

Really, though, I think your question is more about how to write the
script which reads the files from the filesystem and restores them there
- this depends on what language you use.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------