PostgreSQL 10中PL/pgSQL动态查询unaccent对希腊字母无效

Let's break down why this is happening and walk through the fixes:

The Root Cause #

The default unaccent dictionary bundled with the PostgreSQL unaccent extension is built primarily for Latin-script languages. It doesn’t include rules to handle Greek diacritics (like ά, έ, ή, etc.), which is why those characters aren’t being stripped properly when you run your query.

Solution 1: Create a Custom Unaccent Dictionary for Greek #

This is the cleanest, most maintainable long-term approach. Here’s how to set it up:

1. Locate your PostgreSQL text search data directory
   First, find where PostgreSQL stores text search rule files. Run this query to get the path:

   SELECT setting FROM pg_settings WHERE name = 'shared_directory';
   

   Typically, it’s something like /usr/share/postgresql/10/tsearch_data/ (adjust the path to match your installation).

2. Create a Greek unaccent rules file
   In that directory, create a file named unaccent_greek.rules with these mappings (covers all common Greek accented characters):

   ά α
   έ ε
   ή η
   ί ι
   

ϊ ι
ΐ ι
ό ο
ύ υ
ϋ υ
ΰ υ
ώ ω
Ά Α
Έ Ε
Ή Η
Ί Ι
Ό Ο
Ύ Υ
Ώ Ω

Make sure the file is owned by the `postgres` user and has read permissions (run `chown postgres:postgres unaccent_greek.rules` if needed).

3. **Register the custom dictionary**
Run this SQL command to add your new dictionary to PostgreSQL:
```sql
CREATE TEXT SEARCH DICTIONARY unaccent_greek (
    TEMPLATE = unaccent,
    RULES = 'unaccent_greek'
);

4. Update your PL/pgSQL function
   Modify your whereText line to use the custom dictionary instead of the default one:

   whereText := 'lower(unaccent(''unaccent_greek'', place.name)) LIKE lower(unaccent(''unaccent_greek'', $1))';
   

   Note the escaped single quotes ('') — these are necessary because we’re defining a string literal inside PL/pgSQL.

Solution 2: Quick Fix Without Custom Dictionaries #

If you don’t want to set up a custom dictionary, you can use Unicode normalization to decompose accented characters into base letters + diacritics, then strip the diacritics manually:

Replace your whereText line with this:

whereText := 'lower(translate(unicode_normalize(''NFD'', place.name), ''̓΄΅ΆΈΉΊΌΎΏάέήίϊΐόύϋΰώ'', '''')) LIKE lower(translate(unicode_normalize(''NFD'', $1), ''̓΄΅ΆΈΉΊΌΎΏάέήίϊΐόύϋΰώ'', ''''))';

This works by converting accented characters (like ά) into their decomposed Unicode form (α + ́), then removing all diacritic symbols from the string.

Double-Check Your Database Encoding #

Just to be safe, confirm your database is using UTF-8 (required for proper Greek character handling):

SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database();

If it’s not UTF-8, you’ll need to recreate the database with UTF-8 encoding (changing the encoding of an existing database isn’t feasible).

内容的提问来源于stack exchange，提问作者slevin