Search Page using Postgres Full-Text Search feature

Full-Text Search

In text retrieval, full-text search refers to techniques for searching a single computer-stored document or a collection in a full-text database. Full-text search is distinguished from searches based on metadata or on parts of the original texts represented in databases (such as titles, abstracts, selected sections, or bibliographical references).

-- Wikipedia

It is enough for initial level searching page and thus I chose this for my web app.

Postgres

I write a very simple SQL to implement this.

SELECT id, heading, lang, user
FROM code_lang
WHERE to_tsvector(heading) @@ 
to_tsquery('" + key + "') and private=false;

Here I am selecting id, heading, language and user from table code_lang. But the where clause is the catch. tsvector value is a sorted list of distinct lexemes an on the other hand tsquery value stores lexemes that are to be searched for.

Now to_tsvector reduces the document to tsvector and similarly, to_tsquery normalizes word to tsquery. Combining these two with @@ operator we get whether the queried string(key) is matched or not with the current row and making sure that the matched row is not the private code. Since we can't show it to the search results.

This is how the most basic full-text search is implemented in PostgreSQL.

Here is the index of all the post regarding this series of snipcode developemnt

Part I: App Idea
Part II: App prototype
Part III: Local Development
Part IV: G for Git
Part V: Data Modeling
Part VI: Data & Auth APIs
Part VII: Basic Functionalities
Part VIII: App Screen 1
Part IX: App Screen 2
Part X: App Screen 3
Part XI: User Reviews
Part X: Final Submission

Liked my post?