php - Problems at postgresql full text search with words, containing single quotes -

i need use full text search rows column contains word o'henry

my code:

$word = "o'henry"; $sql = "select * mytable to_tsvector('english', col) @@ to_tsquery(:word) ";  $sth = $db->prepare( $sql ); $sth->execute(     array(":word"=>$word) ); 

i have 2 problems:

1) query matches columns contains word o'henry, matches columns containing, example: "o yes, thierry henry sportsman."

2) if $word begins quotes, example 'henry, got error: syntax error in tsquery: "'henry", in spite of fact search string escaped.

how can solve problem?

the default english dictionary going tokenize data treating ' space. can use ts_debug inspect how postgresql/tsearch treat text:

psql=# select * ts_debug('english','o''henry');    alias   |   description   | token |  dictionaries  |  dictionary  | lexemes  -----------+-----------------+-------+----------------+--------------+---------  asciiword | word, ascii | o     | {english_stem} | english_stem | {o}  blank     | space symbols   | '     | {}             |              |   asciiword | word, ascii | henry | {english_stem} | english_stem | {henri} (3 rows) 

it's clear output if want have postgres treat o'henry single word, going have either:

• create custom dictionary handle words apostrophes
• or strip apostrophes both tsvector , tsquery before use.

i'd second option far easiest:

$sql = "select * mytable plainto_tsvector('english', replace(col, '''','')) @@ to_tsquery(replace(:word,'''',''));"