Textual search using LIKE is pretty straightforward as doing: SELECTĬourses.title LIKE '%java%' OR scription LIKE '%java%'īut it returned no results, since the LIKE is case-sensitive, which means we have to specify the upcase letter as saved in the table. Now, before going to "full-text search", let's perform a simple textual search as used in many SQL systems: pattern matching Textual search using LIKE and ILIKE ( 'Functional Programming in a nutshell', 'Learn FP in 4 days') Ĭheck the data was properly created: SELECT * FROM courses Ĭool. ( 'Master Chef 3.0', 'Cooking revisited'), ( 'Cooking like a boss', 'Be the next master chef'), ( 'Mastering Git', 'Git history will no longer bother you'), ( 'History in 3 pages', 'Can you learn history in 3 pages?'), ( 'Ruby programming language', 'DDH sales Ruby, but could you buy it?'), ( 'Java in a nutshell', 'Learn Java in 21 days'), ( 'Sales crash course', 'Yet another course on Sales'), ( 'The crash course of Data Science', 'Be a data scientist in 5 weeks'), ( 'Mathematics: a gentle introduction', 'Numbers are easy'), ( 'Natural Sciences the easy way', 'Your guide to understand the world'), ( 'Law 101', 'Have you ever wondered doing some Law?'), ( 'Intro to Computer Science', 'Understant how computers work'), ( 'Improve your sales skills', 'A complete course that will help you to improve your sales skills'), Next, we will populate the table with some dummy data: INSERT INTO courses (title, description) VALUES Those columns will be our "searchable" columns in which we will perform a text search against: CREATE TABLE courses Let's create a table called courses containing only a title and description columns. In order to explain further the fundamentals of textual search, relevance and results ranking, we have to seed our database with real data and compare different search strategies. In this guide, I'll focus on a simpler yet powerful example using only SQL, so if you want to follow me in this adventure, make sure you have PostgreSQL installed.
Then back to 2014 I wrote this article explaining the reasons why I decided to experiment on PG text search as well as showing a practical example in a Ruby application. It requires a lot of patience and memory 🍪. However, managing ElasticSearch deployment is not easy. It's really worth reading, I could get many insights, since I was already using PostgreSQL as my standard database.īy the time, I was comfortable using ElasticSearch for text searching (and if we go even before that back to 2009, I have experience using Apache Lucene, from which ElasticSearch is based on). Context mattersĪ bunch of years ago I read this awesome blogpost called "Postgres full-text search is good enough". WHERE query document OR similarity > 0 ORDER BY rank_title, rank_description, similarity DESC NULLS LASTīut if you need to understand what the heck is the above SQL statement doing, let me explain you a bit of context and FTS (Full-text search) fundamentals in PostgreSQL. SIMILARITY( 'sales', courses.title || scription) similarity NULLIF(ts_rank(to_tsvector(scription), query), 0) rank_description, NULLIF(ts_rank(to_tsvector(courses.title), query), 0) rank_title, To_tsvector(courses.title || scription) document, Spoiler alert: for those curious people looking for a "okay, just show me a full-text search with ranking and fuzzy search in Postgres in less than 20 lines", so here you go: SELECT This blogpost will guide you to understand the fundamental pieces needed to implement a good enough full-text search using PostgreSQL.
0 kommentar(er)
