I would like to be able to query for documents containing @foo without matching the naked word foo. I'm using the simple dictionary but I notice @ characters are stripped:

SELECT to_tsquery('simple', '@foo');  -- produces 'foo'

Is there a way to force Postgres to treat the @ as significant?


The problem is the default text parser, in that it splits out the '@' symbol

You can see it here:

  ts_lexize('simple', '@foo'), -- {@foo}
  ts_parse('default', '@foo'); -- { (12,@), (1,foo) }

lexize is fine and keeps the @, but the parser splits it out

You'd need to write your own parser or change the source, which isn't fun if you don't know C!

One work-around would be to change @foo to something like ATSYMBOLfoo using some wrapper function for to_tsvector and plainto_tsquery

  • I was afraid this would be the answer. I'm fine with C but this is a hosted database with limited access (and I don't really want a customized postgres). Preprocessing the input data will work though - thanks! – stickfigure Jul 20 '17 at 20:57

perhaps you can use the CHR function:

SELECT some_text
    FROM testing_stuff t
WHERE t.some_text LIKE '%' || chr(64) || '%' ;

this will return any rows where the specified column contains an '@' character. have a look here for another method

  • 1
    Thanks, but LIKE is not really a substitute for FTS :-( – stickfigure Jul 20 '17 at 20:53

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.