Обсуждение: 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.