Regex and textmatching issue

Question

I am doing some basic text matching in Postgres 9.3.5.0.
Here is my code so far:

  Select text from eightks
  WHERE other_events = true and 
  keywordRegexs = [\y(director and member \s+  and resigned)\y/ix];

I am getting the following errors

psql:test3.sql:3: invalid command \y(director
psql:test3.sql:5: ERROR:  syntax error at or near "["
LINE 3:  keywordRegexs = [

I am trying to find documents which contain those exact phrases.

As well as Erwin's point ... regular expressions are literals and must be quoted as such. — Craig Ringer, Sep 11 '14 at 05:53

score 1 · Accepted Answer · answered Sep 11 '14 at 05:39

1

The regular expression match operator in Postgres is ~.
The case insensitive variant is ~*.
Branches are enclosed in ().

SELECT text
FROM   eightks
WHERE  other_events = true
AND    keywordregexs ~* '(\y(director | member \s+ |resigned)\y)';

The meaning of "those exact phrases" is not clear in the question.
Details in the manual.

answered Sep 11 '14 at 05:39

thanks @Erwinbrandstetter. I should have been more clear about the exact phrases. What i mean is want to documents which have the words, director and member and resigned. – CISCO Sep 11 '14 at 05:54
@wazza2013 In that case, regular expression syntax supports word-boundary markers; you should probably use those. – Craig Ringer Sep 11 '14 at 05:55

1 Answers1