PostgresSQL高级应用

1.替换

UPDATE 表名 SET 字段名=REPLACE(a,b,c)   -- 将字段（a）里的字符(b)批量替换为字符（c）

2.截取–返回从字符串左边开始指定个数的字符

（1）从左边截取LEFT(character,integer) 参数1：要截取的字符串，参数2：截取字符个数

update demo set publish_time=LEFT(publish_time,10);

（2）从右边截取

update demo set publish_time=RIGHT(publish_time,10);

（3）从中间截取SUBSTRING(character,start,length)

update demo set publish_time=SUBSTRING(publish_time,10,20);

3.正则替换

Update "表名" set 字段=REGEXP_REPLACE(keyword, '^(doc;)|^(pdf;)|(;doc&)|(;pdf&)|^(nbsp;)|(;nbsp$)', '', 'g');
update "BasicData" set keyword=REGEXP_REPLACE(keyword, ';[\d.]*;', ';', 'g');
update "BasicData" set keyword=REGEXP_REPLACE(keyword, '(^[A-Za-z0-9.%_]+)+;', '', 'g');
update "BasicData" set keyword=REGEXP_REPLACE(keyword, '(;[A-Za-z0-9.%_]+)+$', '', 'g');
update "BasicData" set keyword=REGEXP_REPLACE(keyword, '(;[A-Za-z0-9.%_]+)+;', '', 'g');

4.分组合并多行数据（字段必须是字符串类型）

select id,string_agg(字段,'分隔符') from "fff" GROUP BY id;
select class_type, array_to_string(array_agg(name),'-') as names from user_account group by class_type;

5.拼接

update "ttt" set con=concat("ttt".con ,';',"fff".con) from "fff" where "ttt".file_name="fff".file_name and  "fff".id="ttt".id  and "ttt".id= 25;

6.整行去重

select * from fff union select * from fff;

7.字符串转数字

update "PartyRules" set release_year=cast(left(implement_date,4) as int) where release_year is null;

8.字符串转日期

update tables_basicdata set publish_date=to_date(release_date,'yyyy-MM-dd') where 

9.日期转数字

update rules set years= cast(left(to_char(publish_date,'YYYY-MM-DD'),4) as int)

10.匹配返回多行

regexp_matches(string, pattern[, flags ])
SELECT id,regexp_matches(con, '(《[^》]+《)','g') as f FROM "ttt" ORDER BY id;

11.差集、并集、交集

（1） Union 获取两个表的合集并且自动过滤重复数据

Select * from a
Union
Select * from b

（2）Union all 获取两个表的合集并且不过滤重复数据

Select * from a
Union all
Select * from b

（3）Intersect 获取两个表的交集

Select * from a
Intersect
Select * from b

（4）except 获取第1个表中存在，而第2个表中不存在的数据

Select * from a
Except
Select * from b