Thread: best strategy for searching large text fields

<div class="WordSection1"><p class="MsoNormal">PostgreSQL 9.3.x<p class="MsoNormal">I am looking for really good
documentationon how to do the following.<p class="MsoNormal"> <p class="MsoNormal">Use Case<p class="MsoNormal">I have
aweb application that will be storing into a table large blocks of HTML web content.  Users will then want to search
thesefields for phrases.  What is the best strategy for handling this in PostgreSQL?  <p class="MsoNormal"> <p
class="MsoNormal">Whatwould the SQL look like?  <p class="MsoNormal">What data type should the web content field be in
thetable?  <p class="MsoNormal">Is there a way to set up the SQL so that matches are best match to least accurate
match?<pclass="MsoNormal">Etc…<p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal">Thanks,<p class="MsoNormal"> <p class="MsoNormal"><a
href="http://illinois.edu/person/lance">LanceCampbell</a><p class="MsoNormal">Software Architect<p
class="MsoNormal">WebServices at Public Affairs<p class="MsoNormal">217-333-0382 <p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><ahref="http://illinois.edu/"><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:blue;text-decoration:none"><imgalt="University of
Illinoisat Urbana-Champaign logo" border="0" height="25" id="Picture_x0020_1" src="cid:image001.png@01CFF4F1.3F732480"
width="150"/></span></a><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:blue"></span><p
class="MsoNormal"> <pclass="MsoNormal"> </div> 

On Fri, Oct 31, 2014 at 02:59:48PM +0000, Campbell, Lance wrote:
> PostgreSQL 9.3.x
> I am looking for really good documentation on how to do the following.
>
> Use Case
> I have a web application that will be storing into a table large blocks of HTML web content.  Users will then want to
searchthese fields for phrases.  What is the best strategy for handling this in PostgreSQL? 
>
> What would the SQL look like?
> What data type should the web content field be in the table?
> Is there a way to set up the SQL so that matches are best match to least accurate match?
> Etc...
>

FTS is the way to go:
http://www.postgresql.org/docs/9.3/static/textsearch-intro.html

>
>
>
> Thanks,
>
> Lance Campbell<http://illinois.edu/person/lance>
> Software Architect
> Web Services at Public Affairs
> 217-333-0382
> [University of Illinois at Urbana-Champaign logo]<http://illinois.edu/>
>
>



--
Julien Cigar
Belgian Biodiversity Platform (http://www.biodiversity.be)
PGP fingerprint: EEF9 F697 4B68 D275 7B11  6A25 B2BB 3710 A204 23C0
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.