PostgreSQL – Filtering Special Characters in to_tsquery

full-text-searchpostgresql

This question has been asked before here and here. I want to provide full-text support using postgreSQL via to_tsquery. However, since escaping characters leads to complications, I think it is okay to simply filtering special characters that could provoke errors.

What are the characters I need to remove to use to_tsquery safely. So far, I have identified the following:

Input that contains &, |, : might produce syntax errors
Input with * might not work as expected (to_tsquery('a*b') produces 'b')
quotes need to be escaped

What do you suggest in order to take user input in to_tsquery? What are other special characters I need to remove?

Best Answer

You may want to just leave it as users enter it, since you can safely use strings processed twice via quote_literal. In the to_tsquery documentation, the example for phrases within ts queries is:

SELECT to_tsquery('''supernovae stars'' & !crab');

Adding some junk to the phrase breaks it, unless you pass it twice via quote_literal, which makes any string safe to use:

SELECT to_tsquery(quote_literal(quote_literal('supernovae '':|stars')) || ' & !crab');

Related Solutions

Sql-server – Full-text search in SQL Server on multiple tables fails

Hi we can query multiple tables in a full-text search by using views ,but we some advantages and disadvantages . Here in your case you created view dbo.vw_recipe_search it seems your are not using it in altered procedure when i query it i am getting results as per intended

  SELECT *
        FROM dbo.vw_recipe_search r 
        INNER JOIN FREETEXTTABLE(vw_recipe_search, *, 'salsa peas') kt
            ON r.search_id = kt.[KEY]
        ORDER BY RANK DESC;

The problem in this approach is ranking and weightage of each column , like suppose if u want to give high weight to title column than descrip you have to do a lot of work in order by rank column . We used Levenstein string comparision function to order for better results set

SQL Server – FullTextSearch Indexing and Searching Words with Special Characters

Hmm I'm afraid '#' counts as punctuation and SQL Server Full-Text Indexing was invented before Twitter. There are however a couple of approaches:

1) Pre-processing

Use the full-text functions to fetch most of the data then refine it with Like, eg

SELECT Id
INTO #tmp
FROM dbo.Users
WHERE CONTAINS ( Bio, '#promoter' )

SELECT *
FROM dbo.Users u
WHERE u.Bio Like '%#promoter%'
AND EXISTS ( SELECT * FROM #tmp t WHERE u.Id = t.Id )

Whilst you could use CONTAINSTABLE to do the same thing in one query, you can't necessarily guarantee what order the plan will take - and why would you, T-SQL is after all declarative. I prefer to be explicit, but if you find performance acceptable and/or data volumes are low, combine the two, eg

SELECT *
FROM dbo.Users u
    INNER JOIN CONTAINSTABLE ( Users, Bio, '"#promoter"' ) ct ON u.Id = ct.[key]
WHERE u.Bio Like '%#promoter%'

I did some simple tests on a 1 million row table and it seemed to perform ok. YMMV.

2) Tokenised version of column

Store an alternate version, eg a computed column where '#' is replaced with the literal text 'hashtag', something like this:

-- Add computed column
ALTER TABLE dbo.Users ADD cleanBio AS REPLACE ( Bio, '#', 'hashtag' )

-- Add the computed column to full-text index
ALTER FULLTEXT INDEX ON dbo.Users ADD ( cleanBio ) 
GO

SELECT * FROM dbo.Users
WHERE CONTAINS ( cleanBio, 'hashtagpromoter' )

Obviously there is some storage overhead here.

Consider trying these approaches with your data and let me know how you get on.