Thread: storing a text file

I would like to index several documents. They are all text files, I
was wondering what is the best way to do this. I was thinking to have
a column which will have:

*docid (serial)
date (timestamp)
document (varchar)

Eventually, I would like to access these documents via a webbrowser
and I would like to search these documents. Each document is about 10
to 20kb in size.

My question is, what datatype would be ideal for "document" field?
Also, any thoughts and recommendations ?

TIA

On Sun, Feb 14, 2010 at 09:39:13AM -0500, Mag Gam wrote:
> I would like to index several documents. They are all text files, I
> was wondering what is the best way to do this. I was thinking to have
> a column which will have:
>
> *docid (serial)
> date (timestamp)
> document (varchar)
>
> Eventually, I would like to access these documents via a webbrowser
> and I would like to search these documents. Each document is about 10
> to 20kb in size.
>
> My question is, what datatype would be ideal for "document" field?
> Also, any thoughts and recommendations ?

Make it a TEXT type. You can use PostgreSQL's full text search options for
searching across the set of all documents for given words or phrases.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Thanks.

I would like to load an entire text file into a field of a table.Is
there an easy way to do this or do I have to write a script?

TIA



On Sun, Feb 14, 2010 at 2:29 PM, Joshua Tolley <eggyknap@gmail.com> wrote:
> On Sun, Feb 14, 2010 at 09:39:13AM -0500, Mag Gam wrote:
>> I would like to index several documents. They are all text files, I
>> was wondering what is the best way to do this. I was thinking to have
>> a column which will have:
>>
>> *docid (serial)
>> date (timestamp)
>> document (varchar)
>>
>> Eventually, I would like to access these documents via a webbrowser
>> and I would like to search these documents. Each document is about 10
>> to 20kb in size.
>>
>> My question is, what datatype would be ideal for "document" field?
>> Also, any thoughts and recommendations ?
>
> Make it a TEXT type. You can use PostgreSQL's full text search options for
> searching across the set of all documents for given words or phrases.
>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkt4Tx4ACgkQRiRfCGf1UMPaTgCfVuDbj4m4V5DFv8reLC3KwP8L
> XLkAniJqkT7Q/xSrXOdjBG+9s9S6dyk8
> =uuon
> -----END PGP SIGNATURE-----
>
>

On 2010-02-15, Mag Gam <magawake@gmail.com> wrote:
> Thanks.
>
> I would like to load an entire text file into a field of a table.Is
> there an easy way to do this or do I have to write a script?

I was wondering about that yesterday...

the function pg_read_file seems to be one way to do it.
the function needs to be invoked by a database superuser
(eg. postgres), and the file must be readable by the
database system user account (eg. postgres)
and must be visible from a path that starts at your databases data
directory (you could create a symlink...)

it can be wrapped in a function that is owned by a database superuser
and has the "SECUITY DEFINER" attribute.

http://www.postgresql.org/docs/8.4/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE

lo_import (the sql function) is another way to read files and the content can be pulled
from its storage in pg_catalog.pg_largeobject using the right query
with an agregate function to join the parts. but using lo_import you still
have all the constraints of pg_read_file except the path constraint.

So in general "write a script" is actually the best solution, or build
it into some part of your application's UI.