本文分享一些pg操作
-
查看版本
select version();
PostgreSQL 11.11 -
查看安装的插件
select * from pg_available_extensions; -
查看分词效果
- select ‘我爱北京天安门,天安门上太阳升’::tsvector;
‘天安门上太阳升’:2 ‘我爱北京天安门’:1 - select to_tsvector(‘我爱北京天安门,天安门上太阳升’)
‘天安门上太阳升’:2 ‘我爱北京天安门’:1 - select to_tsvector(‘我爱北京天安门’)
‘我爱北京天安门’:1
- select ‘我爱北京天安门,天安门上太阳升’::tsvector;
-
分词查询
- select ‘I will be back’::tsvector @@ ‘we & back’::tsquery;
给’I will be back’分词,并判断结果中是否既包含’we’,也包含’back’。 - select ‘I will be back’::tsvector @@ ‘we | back’::tsquery;
给’I will be back’分词,并判断结果中是否包含’we’ | 包含’back’。
- select ‘I will be back’::tsvector @@ ‘we & back’::tsquery;
-
增加tsvector列
alter table table_name add column tscontent tsvector; -
更新content成tsvector类型
update table_name set tscontent=to_tsvector(content); -
创建索引
- create index index_name on table using gin(tscontent);
- create unique index index_name on table using btree(id);
-
查看表上的索引
select * from pg_indexes where tablename=‘table_name’; -
查看表字段名称、类型、非空、注释
SELECT a.attname as 字段名,format_type(a.atttypid,a.atttypmod) as 类型, a.attnotnull as 非空, col_description(a.attrelid,a.attnum) as 注释
FROM pg_class as c,pg_attribute as a
where a.attrelid = c.oid and a.attnum>0 and c.relname = 'table_name';
- PG模糊查询 like 字段类型与索引匹配关系
- 根据数据类型设置索引的operator,对于text,varchar和char分别设置和text_pattern_ops,varchar_pattern_ops和bpchar_pattern_ops。
- 查询计划
EXPLAIN (analyze, verbose, BUFFERS, COSTS, TIMING, SUMMARY, FORMATtext) select * from table_name;
选项- analyze:执行语句并显示真正的运行时间和其它统计信息,会真正执行SQL语句;
- verbose:显示额外的信息,尤其是计划树中每个节点的字段列表,schema识别表和函数名称。总是打印统计数据中显示的每个触发器的名字;
- costs:包括每个计划节点的启动成本预估和总成本的消耗,也包括行数和行宽度的预估;
- buffers:使用信息,特别包括共享块命中、读、脏和写的次数,本地块命中、读、脏和写,临时块读和写的次数;
- timing:在输出中包含实际启动时间和每个节点花费的时间,重复读系统块在某些系统上会显著的减缓查询的速度,只在ANALYZE也启用的时候使用;
- format:声明输出格式,可以为TEXT、XML、JSON 或 YAML,默认 text;
- EXPLAIN(ANALYZE) 获取执行时间
- 获取geometry中获取点的坐标
select st_x(geometry),st_y(geometry) from table_name;
- 获取中心点,然后获取坐标
select ST_x(st_centroid(geometry)),st_y(st_centroid(geometry)) as polygon from table_name;
- geometry转polygon
select ST_AsText(geometry) as polygon from table_name;