Vietnamese Full-Text Search on PostgreSQL

Install extensions

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION vector SCHEMA "public" VERSION 0.7.2;

Use a custom text search configuration

CREATE TEXT SEARCH CONFIGURATION vietnamese (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION vietnamese
ALTER MAPPING FOR asciiword, word
WITH unaccent, simple;

Example query

SELECT staffCode, userName, staffName, phoneNumber, email
FROM public.users_embedding_table
WHERE to_tsvector('vietnamese', 
                  unaccent(COALESCE(staffCode, '') || ' ' || 
                           COALESCE(userName, '') || ' ' || 
                           COALESCE(staffName, '') || ' ' || 
                           COALESCE(phoneNumber, '') || ' ' || 
                           COALESCE(email, '')
                  )
                 ) @@ plainto_tsquery('vietnamese', unaccent('Hòa'));