Vietnamese Full-Text Search on PostgreSQL

Tuan DoTuan Do
1 min read

Table of contents

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'));
0
Subscribe to my newsletter

Read articles from Tuan Do directly inside your inbox. Subscribe to the newsletter, and don't miss out.

PostgreSQLfull text search

Written by

Tuan Do
Tuan Do

I am a dedicated software engineer with a deep passion for security and a commitment to developing robust and scalable solutions. With over three years of hands-on experience in the .NET ecosystem, I have built, maintained, and optimized various software applications, demonstrating my ability to adapt to diverse project needs. In addition to my expertise in .NET, I have six months of specialized experience working with Spring Boot and ReactJS, further broadening my skill set to include full-stack development and modern web technologies. My professional journey includes deploying small to medium-sized systems to cloud platforms and on-premises environments, where I have ensured reliability, scalability, and efficient resource utilization. This combination of skills and experience reflects my versatility and commitment to staying at the forefront of the ever-evolving tech landscape.