官方文档参考:全文搜索
1.建表
CREATE TABLE "public"."test" (
"id" int4 NOT NULL,
"code" varchar(255) COLLATE "pg_catalog"."default",
"name" varchar(255) COLLATE "pg_catalog"."default",
"tags" varchar(255) COLLATE "pg_catalog"."default",
CONSTRAINT "test_pkey" PRIMARY KEY ("id")
)
;
ALTER TABLE "public"."test"
OWNER TO "postgres";
2.在这个表里,tags将用来存储标签信息,多个标签以“|”隔开,形如 aaa|bbb|ccc ddd|eee
以这个字段作为全文搜索,需要先添加一个类型tsvector的字段
ALTER TABLE work
ADD COLUMN tags_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', coalesce(tags, ''))) STORED;
然后为了加快查询速度ÿ