Date: Fri, 31 Oct 2014 16:17:10 +0100
From: Julien Cigar <jcigar@ulb.ac.be>
To: "Campbell, Lance" <lance@illinois.edu>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: best strategy for searching large text fields
Message-ID: <20141031151710.GD8131@mordor.lan>
References: 
 <B75CD08C73BD3543B97E4EF3964B7D701FC6EC81@CITESMBX1.ad.uillinois.edu>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
 protocol="application/pgp-signature"; boundary="/3yNEOqWowh/8j+e"
Content-Disposition: inline
In-Reply-To: 
 <B75CD08C73BD3543B97E4EF3964B7D701FC6EC81@CITESMBX1.ad.uillinois.edu>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org


--/3yNEOqWowh/8j+e
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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.
>=20
> Use Case
> I have a web application that will be storing into a table large blocks o=
f HTML web content.  Users will then want to search these fields for phrase=
s.  What is the best strategy for handling this in PostgreSQL?
>=20
> 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...
>=20

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

>=20
>=20
>=20
> Thanks,
>=20
> 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/>
>=20
>=20


--=20
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.

--/3yNEOqWowh/8j+e
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQIcBAABCgAGBQJUU6f2AAoJEAi2KiTKQR5p5C8P+gJhCWPNRgZi++vfzAZOfPuS
gWqcLqX1XdC5rUEjZIZshj0DcjIXPig5kxLZgFgR1+DtaUNECnsqQU8PQkhElPW2
M8HLWmsER1fESwTFJBFs9/W+utCdT+VcI5fbjqlVHpU16geZ3cre2k2mFmTf9a/u
arUoQfFFn0LhwhcAGldkyg2qzMhb0SnSd461VogvAliI8R3M113C85BCYRLSdz0u
kP/7oC5FsbDC8Y3nau9knjwtyL3l/meoQR8GxtG8HbCgD5hfdzo69l4YVXeaSnVh
Ujn5K7qnXghO59RHaPZofCkc7MZdHLyfp4SIfQsADdNq1hlGbWzMr8UW8iSF+z+R
kthCIPtvAkNl5d99GpGP7qTcyfjfFfqWD3SbbT2jRGwpEI7wEjRPj3dI3al/kT1Q
RzZcogRtezqmn+UgiuR4Rd2qz2ZYs4VezNh3maChtUIw8al6UT60rrW2yBspldUr
F3UtWTI2pZisNu6ez/R8QjwXnl6O5U85MkldoaA0lEJhQ+R08B1WPc771G4rJE0D
oHotEKKEls4lQiocpG+AQRBrS35yW1OEhJfnE2dY6szWxo32l3vMxHhbQtMCIcpw
zLdUIJH3pUPIk11f7DtCkiaYxZ5joR67KNHykgLf8FLY0vHlLnrkooguI/0i3S6X
9myRhS4MJKxk1zTKvOQb
=T/g2
-----END PGP SIGNATURE-----

--/3yNEOqWowh/8j+e--