full text search support for postgres, oracle and mssql

[Ronny Pfannschmidt]

I didn't like it was missing.

oracle, mssql : add a FulltextIndex for the Fields

postgresql: for each FulltextField add a extra tsvector Field named like "%(fieldname)_tsv"

Problems with sqlite fts:

• they stated a match statement may only be used once in a query
• it needs a very specific syntax to search multiple fields
• i have no idea how to get that into the ORM

[Simon G. <dev@…>]

This is a very simple little patch - if accepted it'll need some docs. Shouldn't be too hard to implement MySQL fulltext searching too, but this will require a special index to be created.

[djangoproject.com@…]

Typo in patch: plainto_tsquery in psycoph2/base.py should be to_tsquery
Corrected patch attached.

[djangoproject.com@…]

Corrected patch

[James Bennett]

#4676 was a duplicate, and has an alternate implementation for PostgreSQL.

[djangoproject.com@…]

Patches attached to this ticket do not work correctly with PostgreSQL, please use patch ​http://code.djangoproject.com/attachment/ticket/4676/sql.diff in ticket #4676 (which also describes the correct database set-up by the way).

[anonymous]

Postgres 8.3 now has full-text searching. Not sure if this changes anything here or not...

[anonymous]

Replying to anonymous:

Postgres 8.3 now has full-text searching. Not sure if this changes anything here or not...

​http://www.postgresql.org/docs/8.3/static/textsearch.html

[Thomas Güttler]

Both patches (last from june 2006) are broken since [5957]

[Thomas Güttler]

Sorry, I meant june 2007 (The date format of trac is hard to read for germans ...)

[rachid]

corrected Patch (only for postgres)

[rachid]

I added a Patch against 7231. It is only for postgres. But i think it would be straight forward for the other backend.

[Ramiro Morales]

(description)

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Stefano Crosta <stefano@…>]

can this be useful? ​http://barryp.org/blog/entries/postgresql-full-text-search-django/

There's a special "search" method for MySQL but still nothing for PostgreSQL... any specific reason? This interesting ticket seems just abandoned!

[Andrei Antoukh]

If in the future, are going to implement support for postgresql text search, you can have this as a reference implementation:
​https://github.com/niwibe/djorm-ext-pgfulltext/blob/master/djorm_pgfulltext/models.py

[Apostolis Bessas]

This is definitely something I would like to see in Django and could help with (see ​https://github.com/mpessas/django-pg-extensions for another package that supports FTS in PostgreSQL).

Here is a suggestion as how this could be implmented for PostgreSQL:

• Add two extra options in TextField, use_fts=True and fts_config (see ​http://www.postgresql.org/docs/9.3/static/textsearch-intro.html#TEXTSEARCH-INTRO-CONFIGURATIONS), with 'simple' being the default value.
• When the use_fts option is set, create an index (see ​http://www.postgresql.org/docs/9.3/static/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX). The main reasons to prefer an index to a column for the tsvector representation are that this is simpler to set up (no need for columns that are not exposed in Django and no need for triggers or methods to update the column) and that, even if it is a bit slower, most people would not mind; if they care for performance, they can do it manually or use Solr etc. See ​http://www.postgresql.org/docs/9.3/static/textsearch-tables.html for the details of the two approaches.
• The user can do a fts query by using the search field lookup.

I haven't looked into fts for some time now, so I might be forgetting something. In any case, how does this proposal sound?

[Malik A. Rumi]

I was just reading in the documentation that full text search is only available for MySQL, and came over here to find out why. What can I do to help light a fire under this project?

What are people doing in the meantime? Solr? Haystack?

[Dan Loewenherz]

I'd be happy to work on this ticket. I just implemented a very flexible FTS integration with custom fields, and built in support for migrations to automatically create the indices. If it's ok, I'd like to take the reigns on this if no one else has time to.

[Marc Tamlyn]

The first parts of a FTS implementation in contrib.postgres are available for review at ​https://github.com/django/django/pull/4726, as is my plan of attack for the extent of features I wish to implement.

The approach is different to that used by previous implementations of full text search with postgres, staying fairly close to the SQL structures and making heavy use of the new expressions and lookups. As yet I have not researched the potential for similar functionality in other databases, but I expect at least parts of the contrib.postgres implementation to be highly postgres specific. It may be there is a nice generic search API with a cross database set of features which could be implemented. If this is the case though, it will likely make heavy use of the code written for postgres on that backend at least. I don't intend to work on a generic solution at this point.

[Marc Tamlyn <marc.tamlyn@…>]

In 2d877da8:

Refs #3254 -- Added full text search to contrib.postgres.

Adds a reasonably feature complete implementation of full text search
using the built in PostgreSQL engine. It uses public APIs from
Expression and Lookup.

With thanks to Tim Graham, Simon Charettes, Josh Smeaton, Mikey Ariel
and many others for their advice and review. Particular thanks also go
to the supporters of the contrib.postgres kickstarter.

[seenureddy]

Replying to Tim Graham: Is anyone working on the full text search support for oracle?

[Mariusz Felisiak <felisiak.mariusz@…>]

In 3d62ddb0:

Refs #3254 -- Removed unnecessary truth check in SearchVectorExact.as_sql().

Direct usage of the @@ operator is perfectly allowed.

[Claude Paroz]

Is this ticket still open just for Oracle full-text search support?

[Carlton Gibson]

Is this ticket still open just for Oracle full-text search support?

• I'm not sure if the SQLite criticisms in the original description still stand — I see various projects making good use of FTS on SQLite.
• Wagtail just added a Search backend for everything except Oracle as part of GSoC 2021 ​https://wagtail.io/blog/gsoc-2021-wrapup/ — Perhaps there are things to learn from there.
• It would still be good to have SearchVector &co or similar available on the other DBs than PostgreSQL.