Thread: How to use the full text index feature on PostgreSQL 8.x
Hello everyone How to use the full text index feature to search a lot of text on PostgreSQL 8.x? thank you~!
aBBISh wrote: > Hello everyone > > How to use the full text index feature to search a lot of text on > PostgreSQL 8.x? You need to install & setup tsearch2. I have a small article about how to do that here: http://www.designmagick.com/article/27/ -- Postgresql & php tutorials http://www.designmagick.com/
Chris 写道: > aBBISh wrote: >> Hello everyone >> >> How to use the full text index feature to search a lot of text on >> PostgreSQL 8.x? > > You need to install & setup tsearch2. > > I have a small article about how to do that here: > > http://www.designmagick.com/article/27/ > That article so good,thank you. Can you send some article for how to use tsearch2 ? What about tsearch2 for japanese or chinese multibyte character search?
aBBISh wrote: > Chris 写道: >> aBBISh wrote: >>> Hello everyone >>> >>> How to use the full text index feature to search a lot of text on >>> PostgreSQL 8.x? >> >> You need to install & setup tsearch2. >> >> I have a small article about how to do that here: >> >> http://www.designmagick.com/article/27/ >> > > That article so good,thank you. > > Can you send some article for how to use tsearch2 ? http://www.designmagick.com/article/27/page/3 has some examples. > What about tsearch2 for japanese or chinese multibyte character search? Not sure about that one, but the tsearch2 site might have something: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ .. just found a link there actually: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2j.html -- Postgresql & php tutorials http://www.designmagick.com/
Chris wrote: > You need to install & setup tsearch2. > > I have a small article about how to do that here: > > http://www.designmagick.com/article/27/ Nice article, very clear and concise, however one small nit. At the end of page I don't think you need the vacuum full, vacuum may or may not be useful, vacuum full is certainly not needed, and the analyze command will update the stats just fine all by it self. The part of the article in question: CREATE INDEX newscontent_fti_idx ON newsitem USING gist(newscontent_fti); When that's finished, we need to update postgresql statistics: VACUUM FULL ANALYZE;
Chris wrote: > aBBISh wrote: > You need to install & setup tsearch2. > > I have a small article about how to do that here: > > http://www.designmagick.com/article/27/ Also on page 3 you say, "(normal indexes will only index the first 255 characters of a 'text' field)." Is that true?
Hi, there is a feature that I would be glad if it was supported by PostgreSQL: I want to add a new column (attribute) to a pre-existed table, but instead of filling the values of this column with a default value (e.g. null) I would like to copy them from a file stored in my filesystem. I didn't find any example of mixing "copy" and "alter table ... add column" in the Documentation, however I send this mail to check it out. Thanks.
On Fri, Aug 11, 2006 at 07:35:29PM +0300, Ioannis Theoharis wrote: > > > Hi, there is a feature that I would be glad if it was supported by > PostgreSQL: > > I want to add a new column (attribute) to a pre-existed table, but instead > of filling the values of this column with a default value (e.g. null) I > would like to copy them from a file stored in my filesystem. > > I didn't find any example of mixing "copy" and "alter table ... add > column" in the Documentation, however I send this mail to check it out. That's because it doesn't make any sense. How does the alter table know which row in the copy matches which row in the table? It's means you need to do a join, at which point it would be easier to just copy into a temp table and do an update to set the values. alter table doesn't really have any magic in it w.r.t. setting the default value. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Matthew T. O'Connor wrote: > Chris wrote: >> aBBISh wrote: >> You need to install & setup tsearch2. >> >> I have a small article about how to do that here: >> >> http://www.designmagick.com/article/27/ > > Also on page 3 you say, "(normal indexes will only index the first 255 > characters of a 'text' field)." > > Is that true? I thought it was the first 8k? There is also an article here: http://www.devx.com/opensource/Article/21674/1954?pf=true Joshua D. Drake > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Joshua D. Drake wrote: > Matthew T. O'Connor wrote: > >Chris wrote: > >>aBBISh wrote: > >>You need to install & setup tsearch2. > >> > >>I have a small article about how to do that here: > >> > >>http://www.designmagick.com/article/27/ > > > >Also on page 3 you say, "(normal indexes will only index the first 255 > >characters of a 'text' field)." > > > >Is that true? > > I thought it was the first 8k? There is also an article here: > > http://www.devx.com/opensource/Article/21674/1954?pf=true Neither is true. If you create an index on a text column, and that column contains a row above the size limit, an error will result. There's no mechanism in place to truncate what's indexed in order to make it fit the maximum index tuple size; you can do it yourself using an expressional index if you want, of course. The maximum is a bit above 2kB (assuming 8kB pages), but keep in mind that some stuff is compressed before being indexed, so the actual data length may be higher. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > Joshua D. Drake wrote: >> Matthew T. O'Connor wrote: >>> Chris wrote: >>>> aBBISh wrote: >>>> You need to install & setup tsearch2. >>>> >>>> I have a small article about how to do that here: >>>> >>>> http://www.designmagick.com/article/27/ >>> Also on page 3 you say, "(normal indexes will only index the first 255 >>> characters of a 'text' field)." >>> >>> Is that true? >> I thought it was the first 8k? There is also an article here: >> >> http://www.devx.com/opensource/Article/21674/1954?pf=true > > Neither is true. If you create an index on a text column, and that > column contains a row above the size limit, an error will result. > There's no mechanism in place to truncate what's indexed in order to > make it fit the maximum index tuple size; you can do it yourself using > an expressional index if you want, of course. > > The maximum is a bit above 2kB (assuming 8kB pages), but keep in mind > that some stuff is compressed before being indexed, so the actual data > length may be higher. > Thanks for the tips. I've fixed them up in the article :) -- Postgresql & php tutorials http://www.designmagick.com/