Full-search với Postgresql

Tính năng search PostgreSQL tương đối ổn áp, với cơ sở dữ liệu ít. Thông thường mình sẽ dùng Melisearch và Manticore. Tuy nhiên, với cơ sở dữ liệu ít thì thử với PostgreSQL lại rất đơn giản.

Bảng sau so sánh giữa mysql, postgreSql và sqlite (chatgpt cung cấp):

Có thể thấy PostgreSQL hỗ trợ rất tốt. Mình đã thử với SQLite và cũng khá ổn. Hai tính năng hay nhất mà PostgreSQL hỗ trợ là tự động trigger để tạo một cột tsvector một lần duy nhất.

Tạo trigger để cập nhật dữ liệu tự động (một lần duy nhất):

CREATE TRIGGER students_vector_update
BEFORE INSERT OR UPDATE ON students
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(
  search_vector,
  'simple',
  name,
  student_code,
  email
);

Nếu full dữ liệu thì dùng SQL command sau:

CREATE TRIGGER students_vector_update
BEFORE INSERT OR UPDATE ON students
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(
  search_vector,
  'simple',
  name,
  student_code,
  email,
  class,
  class_code,
  national_id,
  phone,
  province,
  address
);

Thực hiện truy vấn:

SELECT * FROM students
WHERE search_vector @@ plainto_tsquery('simple', 'Nam Dong');

Tiếp đến mình sẽ khai báo trong file schema dòng sau, và dùng sqlc generate để tạo code Golang.

-- name: SearchStudents :many
SELECT *
FROM students
WHERE search_vector @@ plainto_tsquery('simple', $1)
ORDER BY id ASC;

Phương án chuẩn hơn vì dùng phraseto_tsquery. Đây là SQL:

SELECT * FROM students
WHERE search_vector @@ phraseto_tsquery('simple', 'hà nam');

Còn đây là schema cập nhật.

-- name: SearchStudentsByPhrase :many
SELECT * FROM students
WHERE search_vector @@ phraseto_tsquery('simple', $1) ORDER BY id;

Code sau do sqlc sinh ra:

func (q *Queries) SearchStudents(ctx context.Context, plaintoTsquery string) ([]Student, error) {
	rows, err := q.db.QueryContext(ctx, searchStudents, plaintoTsquery)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Student
	for rows.Next() {
		var i Student
		if err := rows.Scan(
			&i.ID,
			&i.Name,
			&i.StudentCode,
			&i.Gender,
			&i.Dob,
			&i.DobFormat,
			&i.Class,
			&i.ClassCode,
			&i.Ethnic,
			&i.NationalID,
			&i.Phone,
			&i.Email,
			&i.Province,
			&i.Address,
			&i.Notes,
			&i.SearchVector,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

Rồi ngon, cứ thế vác đi xài.