Vietnamese Full-Text Search on PostgreSQL
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.
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'));