postgresSql 笔记

[Ch-msm]

创建索引：

1. 常用字段上创建gin索引

   CREATE INDEX if not exists 索引名称 ON 表名 USING gin ((j #>'{dept,id}'));

2. 范围查询 btree索引

   CREATE INDEX if not exists 索引名称 ON 表名 USING btree((j #>'{dept,id}'));

删除索引：

DROP INDEX 表名

查看索引大小：

select pg_indexes_size('表名');

创建brin索引 占用空间小 适用于时序pages_per_range 为精度参数 默认128

create index ts_test_4 on test_4 using brin (ts) with (pages_per_range = 128,autosummarize=on );

[Ch-msm]

触发器：

创建函数

#更新关键字文档函数
create or replace function update_tsv_content() RETURNS TRIGGER AS $$
BEGIN
 update asset set tsv_content = concat(j->>'location_text',j->>'name',j->>'status',j->>'identify',j#>>'{asset_group,name}',j#>>'{dept,name}',j#>>'{dept,parent_department,name}',j#>>'{qs_owner,fullname}',j#>>'{model_type,produce_model}',j#>>'{user,name}') WHERE j->>'id' = NEW.j->>'id';
RETURN NEW;
END;
$$ language plpgsql; 

创建触发器

CREATE TRIGGER asset_update_tsv_content AFTER INSERT OR UPDATE OF "j"
ON asset FOR EACH ROW EXECUTE PROCEDURE update_tsv_content();

删除触发器

drop TRIGGER asset_update_tsv_content on asset

[Ch-msm]

日期时间相关

时间戳转日期

to_char(to_timestamp(cast(j ->> 'timestamp' as bigint) / 1000),'YYYY-MM-DD hh24:mi:ss') AS date

生成日期序列

select  date(t) as day from generate_series('2021-11-01'::date,'2021-12-30','1 days') as t

[Ch-msm]

回收磁盘空间

VACUUM full system_log;