Question 90243 in PostgreSQL Question posted in
The official documentation can be found here.

Get all records where each words in string exists on any of the columns in a table – Postgresql

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)

FName Occupation
John Engineer
Carlos Doctor

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

Output:

FName Occupation
John Engineer
Carlos Doctor

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

Output:

FName Occupation
John Engineer

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

Output:

FName Occupation
Carlos Doctor
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search