Thread: How to use the full text index feature on PostgreSQL 8.x

How to use the full text index feature on PostgreSQL 8.x

From
aBBISh
Date:
Hello everyone

How to use the full text index feature to search a lot of text on
PostgreSQL 8.x?

thank you~!

Re: How to use the full text index feature on PostgreSQL

From
Chris
Date:
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/

Re: How to use the full text index feature on PostgreSQL

From
aBBISh
Date:
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?


Re: How to use the full text index feature on PostgreSQL

From
Chris
Date:
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/

Re: How to use the full text index feature on PostgreSQL

From
"Matthew T. O'Connor"
Date:
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;

Re: How to use the full text index feature on PostgreSQL

From
"Matthew T. O'Connor"
Date:
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?

Add column

From
Ioannis Theoharis
Date:

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.

Re: Add column

From
Martijn van Oosterhout
Date:
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

Re: How to use the full text index feature on PostgreSQL

From
"Joshua D. Drake"
Date:
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/



Re: How to use the full text index feature on PostgreSQL

From
Alvaro Herrera
Date:
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.

Re: How to use the full text index feature on PostgreSQL

From
Chris
Date:
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/