PostgreSQL Full Text Search Spanish character Ñ

Question

I am facing an issue when doing full text search with PostgreSQL on text that contains de Spanish character 'Ñ'

When I try to tokenize the Spanish word 'AÑO' (year) I get the following results depending on if input is upper or lower case:

SELECT to_tsvector('spanish','AÑO'),to_tsquery('spanish','año')
"to_tsvector"   "to_tsquery"
"'aÑo':1"   "'año'"

As you can see result is not the same and it is case sensitive, so it makes my application full text search queries case sensitive if they contain this character.

Is there any way to overcome this issue? I have been searching on PostgreSQL documentation about full text search, and I don't know how to change this behaviour on installed dictionaries.

Thank you so much. Martí

Answer 1

The ability for to_tsvector to convert Ñ into ñ depends on the locale, and specifically on lc_ctype. Presumably your database is using an LC_CTYPE such as C whose knowledge is limited to US-ASCII.

Example with an LC_CTYPE compatible with Unicode:

test=> show lc_ctype;
  lc_ctype   
-------------
 fr_FR.UTF-8
(1 row)

test=> SELECT to_tsvector('spanish','AÑO'),to_tsquery('spanish','año');
 to_tsvector | to_tsquery 
-------------+------------
 'año':1     | 'año'
(1 row)

Note that the downcasing is what you expect.

Opposite example with C:

creation:

CREATE DATABASE cc lc_ctype 'C' template template0;

Note the lack of downcasing, as in the question:

cc=> show lc_ctype ;
 lc_ctype 
----------
 C
(1 row)

cc=> SELECT to_tsvector('spanish','AÑO'),to_tsquery('spanish','año');
 to_tsvector | to_tsquery 
-------------+------------
 'aÑo':1     | 'año'
(1 row)