5

Im new to the PostgreSQL world so I do apologize in advance for not knowing if there is an obvious answer to these questions.

Basically I'm looking for two things. First a simple "contains" based search where the python equivalent would be something like:

def find_all_containing( input_array=[], target ):
    output=[]
    for i in input_array:
        if target in i:
            output.append(i)
    return(output) 

In the database that I'm currently building, logically, I'm inclined to believe that the SQL command would look a bit like this if I were trying to show all of the snippet titles that contained 'evil' in the description.

SELECT title FROM snippets WHERE 'evil' in description;
/* or */
SELECT title FROM snippets WHERE description CONTAINS 'evil';

I wrote an inline python function that returns a 'TRUE' or 'FALSE' to make the following work.

/* contains function here */
CREATE FUNCTION contains (input_text text, target text) RETURNS text AS $$
if target in input_text:
  return("TRUE")
return("FALSE")
$$ LANGUAGE plpythonu;

/* working command of the script */
SELECT title FROM snippets WHERE contains(description,'Evil')='TRUE';

This works and should stop me from asking, but I would really prefer to learn how the SQL world would solve this because the whole point of learning SQL was to learn how to work the database instead of just finding another way to mold python to recreate a nosql feel.

The second thing is, this isn't the first time I've thrown together some python snippet to bring more of a pythonic mentality to the database. For those who are well dug into the SQL world am I missing out on something by doing this? I get it for speed but if speed is ever an issue, I would assume that I just need to rewrite the implementation in C.

Keeping it as simple as I can has worked out pretty well so far and this works as is for now. But, when I find myself embedding another language into the code for nearly every solution, thats when I start asking if I'm missing a bigger picture.

9

There are two typical ways to express this.

With LIKE infix search:

SELECT title FROM snippets WHERE description LIKE '%evil%';

or with position:

SELECT title FROM snippets WHERE position('evil' in description) > 0;

Note that neither are indexable by default. Search for "infix search index" for more info on that, and look into pg_trgm if you need it.

| improve this answer | |
  • I would +1 if I had the rep here, thanks for the answer this really helps. Why are there '%' signs in the first example? I've seen that before but I didn't understand what the extra '%' means. – codykochmann Oct 10 '15 at 13:11
  • 1
    @codykochmann Wildcards. % and _ are like the unix shell's * and ? respectively or like regexp .* and .. See the PostgreSQL manual on pattern matching. – Craig Ringer Oct 10 '15 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.