4

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?

2 Answers 2

3

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

You can see it here:

select 
  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

1
  • 1
    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! Jul 20, 2017 at 20:57
0

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
  • 3
    Thanks, but LIKE is not really a substitute for FTS :-( Jul 20, 2017 at 20:53

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .