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);
创建多字段唯一索引
CREATE UNIQUE INDEX name_uniq ON tbl_name (date,hour,code,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. 获取时间并格式化
1. 时间格式化
to_date('2018-03-08','yyyy-MM-dd')
to_char(timestamp, text);
to_char(interval, text);
to_timestamp(text, text);
to_timestamp(double);
to_number(text, text);
2. 获取时间
select now()
select current_timestamp
select localtimestamp
select clock_timestamp()
3. 获取日期
select current_date
select current_time
select localtime
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的，100500的，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 备份
//导出sql文件
pg_dump -U postgres fhadmin_a>D:\fhadmin_a.sql
//导出backup文件
pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f "D:/fhadmin_b.backup" -d fhadmin_a
pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f "D:/zwoa.backup" -d jeecg-boot-0506
pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f "D:/fhadmin_wp.backup" -d fhadmin_wp
pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f "D:/diit_jzhsxt.backup" -d diit_jzhsxt_0608
1. 单表导出(-t tablename)
pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f "C:/DeveloperFolder/vscode/zwoa.backup" -d jeecg_boot -t sys_depart
28.postgresql恢复
pg_restore -h 192.168.1.105 -p 5432 -U postgres -W -d fhadmin_a -v "D:/fhadmin_b.backup"
pg_restore -h 192.168.0.105 -p 5432 -U postgres -W -d zwoa_0506 -v "E:/项目资料/伊犁州oa/zwoa.backup"
pg_restore -h localhost -p 5432 -U postgres -W -d diit_jzhsxt_0325 -v "E:/项目资料/统一时点更新集中会审/diit_jzhsxt.backup"
pg_restore -h localhost -p 5432 -U postgres -W -d fhadmin_wp_20200721 -v "E:\项目资料\卫片执法系统\wpzf-20200721.backup"
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,',')
35.获取字符在字符串中的位置
postgres=# select position('aa' in 'abcd');
position ----------
0 (1 row)
postgres=# select position('ab' in 'abcd');
position ----------
1 (1 row)
postgres=# select position('ab' in 'abcdab');
position ----------
1 (1 row)
36.lpad(String text,length int [,fill text])
通过填充字符填充（默认情况下的空间）将字符串填充到长度长度。如果不足某一长度，则在左边自动补上指定的字符串，直至达到指定长度，可同时指定多个自动填充的字符。如果字符串已经长于长度，那么它被截断（在右边）。
lpad('82538', 6, '0') 082538
select lpad( CAST(nextval('fwh_seq') as VARCHAR) ,6,'0');
37.替换字符串方法
replace(a,'aaa','0')
//把a字段里面的‘aaa’字符串替换成0
38.生成uuid
//安装 uuid_generate_v4() 扩展函数；
create extension "uuid-ossp"
//生成结果
select uuid_generate_v4()
38.psql命令输出查询结果到指定文件
---打开cmd并指定目录到C:\Program Files\PostgreSQL\10\bin
psql -h localhost -U postgres tyhcjzpt
\o D:/sql.sql
select * from tyhcjzpt.gg_jzdk;
---执行结果会自动输出到D:/sql.sql文件中
