PostgreSQL 常见语句

文本处理

注 依托于 pg_jieba 插件
注 插件安装需要依赖 sudo apt install postgresql-server-dev-12 libpq-dev

-- 添加 pg_jieba 插件
create extension pg_jieba;  

-- 查看 append 并行化状态
show enable_parallel_append ;  

-- 创建新闻表
CREATE TABLE public.tbl_news (
    "index" text,
    "date" text,
    title text,
    "content" text,
    tcsv tsvector
);
create index idx_tbl_news_title_content on public.tbl_news using gin(tcsv);

-- 创建分级配置
CREATE FUNCTION func_news_trigger() RETURNS trigger AS $$
begin
  new.tcsv :=
     setweight(to_tsvector('jiebacfg', coalesce(new.title,'')), 'A') ||
     setweight(to_tsvector('jiebacfg', coalesce(new.content,'')), 'D');
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_tsvector_update BEFORE INSERT OR UPDATE
    ON public.tbl_news FOR EACH ROW EXECUTE PROCEDURE func_news_trigger();

-- 计算欧式距离
CREATE OR REPLACE FUNCTION euc_distance(l float8[], r float8[]) RETURNS float8 AS $$  
DECLARE  
  s float8 := 0;  -- 中间结果   
  x float8;       -- LOOP中的数组元素值   
  i int := 1;     -- 数组下标   
  r_len int := array_length(r,1);    -- 右边数组的长度   
  l_len int := array_length(l,1);    -- 左边数组的长度   
BEGIN  
  if l_len >= r_len then  
    foreach x in array l LOOP  
      s := s + ( (x - case when i<=r_len then r[i] else 0 end) ^ 2 );  
      i := i+1;  
    END LOOP;  
  else  
    foreach x in array r LOOP  
      s := s + ( (x - case when i<=l_len then l[i] else 0 end) ^ 2 );  
      i := i+1;  
    END LOOP;  
  end if;  
  RETURN |/ s;  
END;  
$$ LANGUAGE plpgsql;
   
-- 更新当前 tcsv 字段数据内容
update public.tbl_news set tcsv = to_tsvector('jiebacfg', coalesce(title, '') || coalesce(content, ''))

CREATE or REPLACE VIEW view_news as select *,to_tsvector('jiebacfg', content) as tsv FROM public.tbl_news;

-- 创建文档统计物化视图, 计算整体文档中所有单词及其出现过的文档数及频次
CREATE MATERIALIZED VIEW m_view_word_doc_count as SELECT * FROM ts_stat('SELECT tsv FROM public.view_news') ;

-- 查询2个字符以上的单词词频统计信息
select * from m_view_word_doc_count where LENGTH(word) > 1 ORDER BY nentry DESC, ndoc DESC, word

-- 定期更新物化视图
REFRESH MATERIALIZED VIEW m_view_word_doc_count;

select * from public.tbl_news a where a.tcsv @@ to_tsquery('能源 & (电力 | 风能)');

select * from public.tbl_news a where a.tcsv @@ '能源 & (电力 | 风能)';

select * from public.tbl_news a where a.tcsv @@ '能源:D & (电力 | 风能)' and a."date" > '20210101' order by a."date" desc ;

SELECT word, nentry  FROM ts_stat('select tcsv from public.tbl_news where tcsv @@ ''能源 & (电力 | 风能)'' limit 100') where LENGTH(word) > 1 ORDER BY nentry DESC, ndoc DESC, word ;

-- 精确查询，中间间隔字符数量，带距离 搜索
select * from public.tbl_news a where a.tcsv @@ '能源 <1> 电力'::tsquery;
select * from public.tbl_news a where a.tcsv @@ '能源 & 电力'::tsquery;

select * from public.tbl_news a where a."content" % '风能';

select * from public.tbl_news a where a.tcsv @@ '能源 & (电力 | 风能)' order by similarity(a.title, '能源开发潜力') desc;

select * from public.tbl_news a where a."content" like '%能源%' and (a."content" like '%电力%' or a."content" like '%风能%');