Match String to Array of Strings in Postgres Database Column

Question

I have a Django app which uses a Postgres database. I am creating a temp table by doing the following:

cursor.execute("""CREATE TEMP TABLE temp_table (pub_id INTEGER, pub_title TEXT, pub_tags TEXT[])""")

Notice that the last column (pub_tags) of temp_table contains an array of strings.

For reference, my next line of code inserts data from existing tables into the temp table, and works fine.

cursor.execute("""INSERT INTO temp_table(pub_id, pub_title, pub_tags) SELECT...etc.

For the last step, I'd like to get the pub_titles from the temp_table where, in the pub_tags column, there is a match to a string that I am entering.

For example, I'd like to get all the pub_titles where the pub_tag array contains the string "men." I'd imagine the syntax would be something like:

 cursor.execute("""SELECT pub_title FROM temp_table WHERE '%men%' IN (pub_tags)""")

Which is not correct and throws a syntax error, but hopefully describes what I am trying to do. I'm just not sure how to indicate that pub_tags is an array in this context.

I have been referred to some postgres docs, for example:

http://www.postgresql.org/docs/current/static/functions-array.html, and http://www.postgresql.org/docs/current/interactive/functions-comparisons.html#AEN18030

but no matter what I try I can't get anything to work here.

Answer 1

from postgres documentation it looks like the syntax might be

SELECT pub_title FROM temp_table WHERE 'men' = ANY (pub_tags)