docker pgsql实现pg_jieba全文检索

安装pg_jieba分词器

安装依赖工具

查看docker运行的所有容器

docker ps

进入pg数据库容器

docker exec -it postgres4postgis bash

安装必要的工具和依赖

apt-get install -y git build-essential cmake libpq-dev postgresql-server-dev-all

安装pg_jieba分词

git clone https://github.com/jaiminpan/pg_jieba.git /pg_jieba
cd /pg_jieba
git submodule update --init --recursive
mkdir build && cd build

因为我这容器是Ubuntu系统，需要指定DPostgreSQL_TYPE_INCLUDE_DIR
#版本可以通过navicat执行SELECT version();确定

cmake -DPostgreSQL_TYPE_INCLUDE_DIR=/usr/include/postgresql/14/server ..
make
make install

加载扩展： 进入 PostgreSQL 命令行，加载 pg_jieba 扩展

psql -U postgres

在 PostgreSQL 提示符下运行以下命令：

CREATE EXTENSION pg_jieba;

#如果报错ERROR: could not open extension control file “/usr/share/postgresql/14/extension/pg_jieba.control”: No such file or directory
查看容器中是不是有多个pgsql版本，将多余的版本卸载
#列出已安装的 PostgreSQL 版本

ls /usr/lib/postgresql

#卸载其他版本

apt-get remove --purge postgresql-16 postgresql-client-16 postgresql-server-dev-16

清理残留文件

rm -rf /usr/lib/postgresql/16
rm -rf /usr/share/postgresql/16
rm -rf /usr/include/postgresql/16

#验证 PostgreSQL 版本

psql --version

用navicat将插件加载到指定数据库

建立索引

添加 tsvector 列
首先，为了存储全文检索数据，需要在表中添加一个 tsvector

ALTER TABLE "news_publish" ADD COLUMN tsv tsvector;

更新 tsvector 字段： 在数据库中创建触发器，当插入或更新记录时自动更新 tsvector 字段。

CREATE OR REPLACE FUNCTION "public"."update_tsvector"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
BEGIN
    NEW.tsv := to_tsvector('jiebacfg', NEW.title || ' ' || NEW.content_txt);
    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON news_publish FOR EACH ROW EXECUTE FUNCTION update_tsvector();

创建 GIN 索引： 为了加快全文检索的速度，可以为 tsvector 列创建一个 GIN 索引。

CREATE INDEX idx_gin_tsv ON news_publish USING GIN(tsv);

sql语句

        SELECT *
        from news_publish a
        WHERE a.delete = 0
        <if test="keyword != null and keyword != ''">
            AND a.tsv @@ to_tsquery('jiebacfg', #{keyword})
        </if>
        order by a.last_modify_time desc