记录一些PostgreSQL操作

        本文分享一些pg操作

1. 查看版本
   select version();
   PostgreSQL 11.11

2. 查看安装的插件
   select * from pg_available_extensions;

3. 查看分词效果

   1. select ‘我爱北京天安门,天安门上太阳升’::tsvector;
      ‘天安门上太阳升’:2 ‘我爱北京天安门’:1
   2. select to_tsvector(‘我爱北京天安门,天安门上太阳升’)
      ‘天安门上太阳升’:2 ‘我爱北京天安门’:1
   3. select to_tsvector(‘我爱北京天安门’)
      ‘我爱北京天安门’:1

4. 分词查询

   1. select ‘I will be back’::tsvector @@ ‘we & back’::tsquery;
      给’I will be back’分词，并判断结果中是否既包含’we’,也包含’back’。
   2. select ‘I will be back’::tsvector @@ ‘we | back’::tsquery;
      给’I will be back’分词，并判断结果中是否包含’we’ | 包含’back’。

5. 增加tsvector列
   alter table table_name add column tscontent tsvector;

6. 更新content成tsvector类型
   update table_name set tscontent=to_tsvector(content);

7. 创建索引

   1. create index index_name on table using gin(tscontent);
   2. create unique index index_name on table using btree(id);

8. 查看表上的索引
   select * from pg_indexes where tablename=‘table_name’;

9. 查看表字段名称、类型、非空、注释

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';

10. PG模糊查询 like 字段类型与索引匹配关系
    1. 根据数据类型设置索引的operator，对于text，varchar和char分别设置和text_pattern_ops，varchar_pattern_ops和bpchar_pattern_ops。
11. 查询计划
    EXPLAIN (analyze, verbose, BUFFERS, COSTS, TIMING, SUMMARY, FORMATtext) select * from table_name；
    选项
    1. analyze：执行语句并显示真正的运行时间和其它统计信息，会真正执行SQL语句；
    2. verbose：显示额外的信息,尤其是计划树中每个节点的字段列表,schema识别表和函数名称。总是打印统计数据中显示的每个触发器的名字；
    3. costs：包括每个计划节点的启动成本预估和总成本的消耗，也包括行数和行宽度的预估；
    4. buffers：使用信息，特别包括共享块命中、读、脏和写的次数，本地块命中、读、脏和写，临时块读和写的次数；
    5. timing：在输出中包含实际启动时间和每个节点花费的时间，重复读系统块在某些系统上会显著的减缓查询的速度，只在ANALYZE也启用的时候使用；
    6. format：声明输出格式，可以为TEXT、XML、JSON 或 YAML，默认 text；
12. EXPLAIN(ANALYZE) 获取执行时间
    
13. 获取geometry中获取点的坐标

select st_x(geometry),st_y(geometry) from table_name;

14. 获取中心点，然后获取坐标

select ST_x(st_centroid(geometry)),st_y(st_centroid(geometry)) as polygon from table_name;

15. geometry转polygon
    select ST_AsText(geometry) as polygon from table_name;