Opened 18 years ago

Last modified 14 months ago

#3254 new New feature

full text search support for postgres, oracle and mssql

Reported by: Ronny Pfannschmidt Owned by:
Component: Database layer (models, ORM) Version:
Severity: Normal Keywords: oracle fulltext search postgresql postgres mysql
Cc: sam@…, johann.queuniet@…, hv@…, rboumart@…, dan.fairs@…, twidi@…, Matt Boersma, justinlilly@…, James Pic, robinchew@…, niwi@…, slav0nic@…, mpessas@…, tomi.kyostila@…, Matt Goldman Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Ramiro Morales)

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

Attachments (3)

fts-pstgres-oracle-mssql.patch (1.8 KB ) - added by Ronny Pfannschmidt <ronny.pfannschmidt@…> 18 years ago.
fts-pstgres-oracle-mssql-corrected.patch (1.8 KB ) - added by… 17 years ago.
Corrected patch
fts-pstgres.patch (711 bytes ) - added by rachid 16 years ago.
corrected Patch (only for postgres)

Download all attachments as: .zip

Change History (46)

by Ronny Pfannschmidt <ronny.pfannschmidt@…>, 18 years ago

comment:1 by Simon G. <dev@…>, 17 years ago

Summary: [patch] experimental fts support for postgres, oracle and mssql[patch] experimental fulltext search support for postgres, oracle and mssql
Triage Stage: UnreviewedDesign decision needed

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.

comment:2 by anonymous, 17 years ago

Cc: sam@… added

comment:3 by…, 17 years ago

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

by…, 17 years ago

Corrected patch

comment:4 by James Bennett, 17 years ago

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

comment:5 by…, 17 years ago

Patches attached to this ticket do not work correctly with PostgreSQL, please use patch in ticket #4676 (which also describes the correct database set-up by the way).

comment:6 by Jacob, 17 years ago

Component: MetasystemDatabase wrapper
Needs documentation: set
Needs tests: set
Patch needs improvement: set
Triage Stage: Design decision neededAccepted

comment:7 by anonymous, 17 years ago

Cc: johann.queuniet@… added

comment:8 by Jacob, 17 years ago

Reporter: changed from Ronny Pfannschmidt <ronny.pfannschmidt@…> to Ronny Pfannschmidt

comment:9 by anonymous, 16 years ago

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

in reply to:  9 comment:10 by anonymous, 16 years ago

Replying to anonymous:

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

comment:11 by Thomas Güttler, 16 years ago

Cc: hv@… added

comment:12 by Thomas Güttler, 16 years ago

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

comment:13 by Thomas Güttler, 16 years ago

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

by rachid, 16 years ago

Attachment: fts-pstgres.patch added

corrected Patch (only for postgres)

comment:14 by rachid, 16 years ago

Cc: rboumart@… added

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

comment:15 by Dan Fairs, 16 years ago

Cc: dan.fairs@… added

comment:16 by anonymous, 16 years ago

Cc: twidi@… added

comment:17 by Matt Boersma, 15 years ago

Cc: Matt Boersma added

comment:18 by Matt Boersma, 15 years ago

Keywords: oracle fulltext search added

comment:19 by Ramiro Morales, 15 years ago

Description: modified (diff)


comment:20 by Justin Lilly, 15 years ago

Cc: justinlilly@… added

comment:21 by Łukasz Rekucki, 13 years ago

Severity: normalNormal
Type: enhancementNew feature

comment:22 by Aymeric Augustin, 12 years ago

UI/UX: unset

Change UI/UX from NULL to False.

comment:23 by Aymeric Augustin, 12 years ago

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:24 by Stefano Crosta <stefano@…>, 12 years ago

can this be useful?

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

comment:25 by James Pic, 12 years ago

Cc: James Pic added

comment:26 by Robin, 12 years ago

Cc: robinchew@… added

comment:27 by Andrei Antoukh, 11 years ago

Cc: niwi@… added

If in the future, are going to implement support for postgresql text search, you can have this as a reference implementation:

comment:28 by Sergey Maranchuk, 11 years ago

Cc: slav0nic@… added

comment:29 by Apostolis Bessas, 11 years ago

Cc: mpessas@… added

comment:30 by Apostolis Bessas, 11 years ago

This is definitely something I would like to see in Django and could help with (see for another package that supports FTS in PostgreSQL).

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

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

comment:31 by Marc Tamlyn, 10 years ago

Owner: changed from nobody to Marc Tamlyn
Status: newassigned

comment:32 by Malik A. Rumi, 10 years ago

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?

comment:33 by Dan Loewenherz, 10 years ago

Keywords: postgresql postgres mysql added

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.

comment:34 by Tim Graham, 9 years ago

Summary: [patch] experimental fulltext search support for postgres, oracle and mssqlfull text search support for postgres, oracle and mssql

comment:35 by Tomi Kyöstilä, 9 years ago

Cc: tomi.kyostila@… added

comment:36 by Marc Tamlyn, 9 years ago

The first parts of a FTS implementation in contrib.postgres are available for review at, 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.

comment:37 by Marc Tamlyn <marc.tamlyn@…>, 8 years ago

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.

comment:38 by Tim Graham, 8 years ago

Has patch: unset
Needs documentation: unset
Needs tests: unset
Owner: Marc Tamlyn removed
Patch needs improvement: unset
Status: assignednew

in reply to:  34 comment:39 by seenureddy, 5 years ago

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

comment:40 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In 3d62ddb0:

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

Direct usage of the @@ operator is perfectly allowed.

comment:41 by Claude Paroz, 3 years ago

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

comment:42 by Carlton Gibson, 3 years ago

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 — 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.

comment:43 by Matt Goldman, 14 months ago

Cc: Matt Goldman added
Note: See TracTickets for help on using tickets.
Back to Top