PostgreSQL中含@符号的Slack用户名全文检索问题咨询
Hey there! Let's figure out why your full-text search queries aren't catching those @myname Slack mentions, and fix it.
Why Your Current Queries Aren't Working
PostgreSQL's default full-text search parsers treat characters like @ as non-alphanumeric separators or noise words. When you run to_tsquery('@myname') or plainto_tsquery('@myname'), the @ gets stripped out entirely—so you're actually searching for just myname, which matches any comment containing that word, regardless of the @.
Solutions (Ordered by Simplicity)
1. Use LIKE (Quickest & Most Straightforward)
If you just need to find exact instances of @myname, skip full-text search entirely and use LIKE:
SELECT * FROM x WHERE comment LIKE '%@myname%';
For case-insensitive matches (e.g., @MyName or @MYNAME), use ILIKE:
SELECT * FROM x WHERE comment ILIKE '%@myname%';
2. Use Regular Expressions (More Flexible for Patterns)
If you need to match any Slack mention (not just @myname) or enforce word boundaries (to avoid matching @myname123 when you want only @myname), use PostgreSQL's regex operators:
- Match any
@followed by a username (letters/numbers/underscores):
SELECT * FROM x WHERE comment ~ '@\w+';
- Match exactly
@myname(ensuring no extra characters follow it):
SELECT * FROM x WHERE comment ~ '@myname\M';
\M is PostgreSQL's word-end boundary marker—better than \b here because \b would treat @ as a word boundary itself.
3. Adjust Full-Text Search Configuration (For Advanced Use Cases)
If you need to combine mention matching with other full-text features (like stemming or ranking), you can tweak how PostgreSQL handles the @ symbol. One simple workaround is to modify the text before generating the tsvector to preserve the @:
SELECT * FROM x WHERE to_tsvector('english', replace(comment, '@', '@_')) @@ to_tsquery('english', '@_myname');
By replacing @ with @_, we make the parser treat @_myname as a single word, so the full-text search will only match comments containing the exact @myname pattern.
If you want a more permanent solution (requires superuser access), you could create a custom text search configuration that classifies @ as an alphanumeric character, but that's overkill for most scenarios where the above methods work perfectly.
内容的提问来源于stack exchange，提问作者Rémi Desgrange
