I am building a search functionality and need help with a postgres query. My use case is - When a string is an input, what is the best (optimized) way in postgres to get all records where each words in string exists on any of the columns in a table ?

Sample Table: (The table I am working with has 40 columns)

Case 1: Given a string 'John Doctor', In this case it would return both the records.

Output:

Case 2: Given a string 'John Engineer', it would only return 1 row

Output:

Case 3: Given a string 'Carlos', it would return 1 row

Output:

CodePudding user response：

Basically, you want to do following:

SELECT FName, Occupation
FROM yourtable
WHERE 
'John' IN (FName, Occupation) OR
'Doctor' IN (FName, Occupation);

I don't know if this is already a sufficient answer for you because it's unclear if the logic to fetch the different names from your "search string" must be written as SQL query, too. I think that's a much better task for your application.

If this must also be done in pure SQL, you could use UNNEST to split your string. Something like this:

WITH sub AS
(SELECT UNNEST(STRING_TO_ARRAY('John Doctor', ' ')) AS searchNames)
SELECT 
DISTINCT y.FName, y.Occupation 
FROM yourtable y, sub 
WHERE
sub.searchNames IN (y.FName, y.Occupation);

This will split your string by spaces into the different names, i.e. you need to provide a search string in the form you have mentioned, with a space between the names. This will produce the correct results according to your description.

We can verify this here: db<>fiddle1

This can be extended for as many column as needed. Let's for example add a further column col and search Test3 in this column, then the query will be like this:

SELECT FName, Occupation,col
FROM yourtable
WHERE 'John' IN (FName, Occupation, col)
OR 'Doctor' IN (FName, Occupation, col)
OR 'Test3' IN (FName, Occupation, col);

Or again with UNNEST like this:

WITH sub AS
(SELECT UNNEST(STRING_TO_ARRAY('John Doctor Test3', ' ')) AS searchNames)
SELECT 
DISTINCT y.FName, y.Occupation, y.col 
FROM yourtable y, sub 
WHERE
sub.searchNames IN (y.FName, y.Occupation, y.col);

Try this here: db<>fiddle2

CodePudding user response：

Use regexp match operator (case insensitive) and any to find the records that contain at least one of the words in the list.

select * 
from the_table t
where t::text ~* any(string_to_array(the_words_list, ' '));

DB Fiddle demo