postgresql

标签（空格分隔）： postgresql常用语句

---

[toc]

1. 德哥github链接

2. 自动生成序列号

CREATE SEQUENCE peopleinfo_id_seq  
START WITH 1  
INCREMENT BY 1  
NO MINVALUE  
NO MAXVALUE  
CACHE 1;  

alter table peopleinfo alter column id set default nextval('peopleinfo_id_seq'); 

3. 建表

create table test_indexscan(id int, info text);

4. 插入数据

按序插入：
insert into test_indexscan select generate_series(1,5000000),md5(random()::text); 
无序插入：
insert into test_indexscan select (random()*5000000)::int,md5(random()::text) from generate_series(1,100000);

5. 创建索引

1. B-TREE索引

create table test_indexscan(id int, info text);
create index idx_test_indexscan_id on test_indexscan using btree(id);  

2. gist索引（GiST直接构建在空间列上，对性能影响最大。）

create unlogged table test_gist (pos geometry);
create index idx_test_gist_1 on test_gist using gist (pos);

3. BRIN索引(brin直接构建在空间列上，对性能影响最小。)

create unlogged table test_brin (pos geometry);
create index idx_test_brin_1 on test_brin using brin(pos); 

6. 删除索引

drop index idx_test_indexscan_id ;

7. 查看表或索引占用数据块

select relpages from pg_class where relname='test_indexscan'; 

8. 生成一个数值序列，从start 到stop，步进为1

generate_series(start, stop) 

9. md5生成

select md5('test123456');

10. 随机数生成

select random()::text

11. 从现有的表删除完整的数据

truncate test_indexscan;

12. 统计与字段值的物理行序和逻辑行序有关

统计值范围从-1到1, 趋向于-1表示逆向相关, 趋向于1表示正向相关, 趋向于0表示不相关。

select correlation from pg_stats where tablename='test_indexscan' and attname='id'; 

13. ctid： 表示数据记录的物理行当信息，指的是 一条记录位于哪个数据块的哪个位移上面

select ctid,id from test_indexscan limit 10;

14. random_page_cost、seq_page_cost

SHOW seq_page_cost;  //顺序读页代价
SHOW random_page_cost;//随机读页代价

15.关闭seqscan

set enable_seqscan =off;

16. 关闭indexscan;

set enable_indexscan=false;

17.关闭enable_bitmapscan

set enable_bitmapscan=on;

18.序列操作

SELECT nextval('seq_glxt_phones');--下一个值
SELECT setval('seq_glxt_phones',1);---设置序列当前值

19. 判断字段是否为空，空给默认值，否则取该字段是什么函数

select COALESCE(D.wk_ptn_cd, '00') as wk_ptn_cd

20.case类似于if else

select name,case when sex = 'm' then '男' else '女' end as sex from tbl_test;

21. 获取日期并格式化

to_char(timestamp, text);
to_char(interval, text);
to_timestamp(text, text);
to_timestamp(double);
to_number(text, text);

22. 为表指定用户

ALTER TABLE public.id_role OWNER to jlxt;

23. 为表备注

COMMENT ON TABLE public.id_role IS '角色';

24. 为字段备注

COMMENT ON COLUMN public.id_role.name IS '名称';

25. 现在要进行统计，小于100的，100_500的，5001000的，1000以上的，这各个区间的id数

select t.tag, count(*) from 
   (select case  
   when id < 2 then 'less 2' 
   when id >=2 and id< 5 then '2to5' 
   when id>=5 and id<8 then '5to8' else 'great 8' end as "tag", id from generate_series(1, 10) as id) as t group by t.tag;

26.修改字段类型

alter table "member" alter  COLUMN  imgfileid  type int ;

26. postgresql直播与资料下载

https://m.aliyun.com/yunqi/articles/688691

27. postgresql 备份

pg_dump -U postgres fhadmin_a>D:\fhadmin_a.sql

28.获取表字段名

SELECT col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull   
FROM pg_class as c,pg_attribute as a where c.relname = '表名' and a.attrelid = c.oid and a.attnum>0

29.分组排序查询

select * from (select s.*,  row_number() over(partition by EXECUTION_ID_) as row from act_hi_taskinst s order by start_time_ desc) t

30.聚合函数array_to_string，ARRAY_AGG,string_agg

示例

select string_agg(jcbh,',') from wp_dktb where basedataid is null and xzqdm = '650102'

结果

65,64,65,65,63,64,64,64,64

31.空间清理

VACUUM (VERBOSE, ANALYZE) jlxt_gj;

32.保留指定的小数位数（四舍五入）

cast((sum(dkarea)/0.0015) as decimal(10,1))

33.子字符串获取

substring('topmars' from 3 for 3)

34.将字符串转换为数组

select regexp_split_to_table(t.xzqdm,',')