Postgresql（一） 致不了解的那些事

Postgresql自诩为the most advanced open source database。当我写完这篇文时，我突然有一点点明白了。

临时表

Pg是轻量级的Oracle,Oracle中就有临时表，并且在很多场景都有不错应用，Pg也支持临时表,并且在update/delete limit 的实现中求很重要。
limit关键在在select 语句中是可行的，但是当我们指定要更新或者是删除指定条数数据的时候却发现在Pg的update/delete中根本就没有这个limit的使用。

with t1 as (select id from t where id between 1 and 1000 limit 10) update t set info='new' where id in (select * from t1);

这里的t1其实就是临时表，是括号里面select产生的，select语句中可以使用limit，所有临时表t1其实就是我们要update的数据，update 的时候直接指定我们更新的数据在临时表中就OK了。
删除

with t1 as (select id from t where id between 1 and 1000 limit 10) delete from t where id in (select * from t1);

使用with模拟必须有PK或者非空UK，否则需要用行号，但是行号来扫就慢了，没走tid scan方法。

with tmp as(select music_id from music limit 10) update music set craw_url = ('http://www.xiami.com/album/'+music_id) where music_id in (select music_id from tmp);

正则表达式

可能正则的使用是我觉得Pg最人性化的地方了，可以匹配字段，表示这感觉有点酷。
在PostgreSQL中有两种正则表达式，一种称为SQL正则表达式模式，一种称为POSIX正则表达式。
POSIX正则表达式就是我们一般在脚本语言中使用的标准正则表达式，而SQL正则表达式首先是遵循SQL语句中的like的语法，如
字符“.”在POSIX正则表达式中代表任意字符，而在SQL表达式中就只能表示自己，而表示任意字符的元字符是“_”，
SQL正则表达式中“％”可以表过任意个字符，而这在POSIX正则表达式中需要用“.*”来表示。
同时在SQL正则表达式中也支持以下语法：
| 表示选择(两个候选之一)，这个在POSIX正则表达式中不支持。
* 表示重复前面的项零次或更多次
+ 表示重复前面的项一次或更多次
() 把项组合成一个逻辑项
[…] 声明一个字符类
similar to 中使用的就是SQL正则表达式，而 “~”使用的是POSIX正则表达式，SQL正则表达式要求是全部匹配才为真，而POSIX表达式中是只要包含就为真。
Pg在使用正则表达式的时候需要使用关键字“~”，表示~关键字之前的内容要和~之后的内容进行匹配，不匹配则在关键”~”之前加！。
若正则表达式中包含了转义字符，则可以在正则前面加入E

select * from user where email ~ '^[A-H]' --匹配email地址以A-H开头的记录  select * from user where email ~* '^[a-h]' --匹配email地址以A-H和a-h开头的记录  select * from music where craw_url ~E'http://xiami.com/d+$.html'

而在substring(string,string)中这两个参数一个是字符串，另外一个则是正则表达式，根据匹配到的部分进行截取，所以这里并不要求完全匹配，使用的是POSIX正则表达式。

select substring('osdba-5-osdba',E'(\\d+)')5

除了有两个参数的substring ,还有三个参数的substring(),所以我感觉大概advanced也是够的着的。
substring(string,string ,string)
这种形式的substring使用SQL正则表达式。第三个参数为指定一个转义字符。

select substring('osdba-5-osdba','%#"[0-9]+#"%','#')

结合上面的update的临时表，我们可以将一个字段中的处于字符串尾部的数字部分截取下来赋给另外一个字段。

with tmp as(select craw_url in music limit 10) update music set music_id = substring(craw_url,E'\\d+$') where craw_url in (select craw_url in tmp);

Pg中的常见函数

Pg中有逻辑操作符、比较操作符、还有数学的各种函数，但是一般情况下我们可能会用的比较少。
用到比较多的可能就是Pg中的字符串函数。
字符串连接 || ‘post’||’gresql’ ‘postgresql’
字符串截取substring() 这个在正则表达式中已经说了一些这个函数和正则表达式的关系。
除了可以使用正则作为一个参数，也可以不用正则，从索引的角度来substring.

    substring('Thomas' from 2 for 3)       'hom'    substring(string,from,for)

string 为需要截取的字符串，from为int,开始截取的位置，for为int,截取的长度。
position

position('om' in 'Thomas')       3position(substring in string)

还有转大小写的

lower(string)upper(string)

替换的

replace('abcdefabcdef', 'cd', 'XX')     'abXXefabXXef'replace(string text, from text, to text)

转换的

translate('12345', '14', 'ax')    'a23x5'translate(string text, from text, to text)

把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符
强大的字符串函数功能，我们可以不通过写脚本，直接利用SQL语句对数据库中的数据做一些简单的处理。

UPSERT

UPSERT的出现真的是所有Pg爱好者的福音。终于在2016年1月7日公布的PostgreSql9.5版本中成为一项特色。
UPSERT是INSERT, ON CONFLICT UPDATE的简写，简而言之就是：插入数据，正常时写入，主键冲突时更新。
当我们常规的进行插入的时候如果主键冲突，就会ERROR: duplicate key value violates unique constraint “customer_pkey”，这里的”customer_pkey “就是主键。
但是我们使用upsert 命令就完全不会出现这个报错，在主键冲突时会自动更新除主键外的字段，这些更新的字段我们可以自己指定。
使用

insert into t values('qqlive','rudy4') ON CONFLICT ON CONSTRAINT website do update set name=EXCLUDED.nameorinsert into t values('qqlive','rudy4') ON CONFLICT (website) do update set name=EXCLUDED.name

values中要提供所有要插入的字段，当主键冲突时，则执行set ,也就是更新除主键之外并且values中提供数据的其他字段了。
或者如果发生主键冲突的时候也可以不update ,保持原有数据就OK了，那么我们则ON CONFLIT(website) do nothing;

可是这个功能只在Postgresql的版本大于9.5的时候才有，那么不符合这个版本要求的我们在主键冲突的时候也想着能够更新，所以就有了下面。
解决方案1

try:     cursor.execute('insert table (columns)values()')except:     cursor.execute('update table set columns=values where 主键1= and 主键2= ...')

我们理论上是想进行插入的，如果插入出现异常也就是主键冲突所带来的异常我们就捕获异常，并转为在等于这个主键的条件下进行更新。
彻底把upsert 分成两个步骤来做，但是这样肯定不是SQL语句直接来，需要写一些脚本，用cursor.execute(“”)来执行。
解决方案2
直接使用SQL语句

with upsert as (update table set name='rudy1' where id=5 returning *) insert into table select 5,'rudy' where not exists (select 1 from upsert where id=5);

可以看出存在时则更新，不存在时则进行插入，因为命令会先执行的update的返回值作为临时表，如果临时表upsert不为空时，则说明存在，在insert时由于where not exits则select 不到，则不再进行插入。
相反也是这样的。
注意id是主键，name是我们要更新的字段。
最后我还要说一说Pg中占位符

Pg占位符

所以我们可以这样

cursor.execute('insert into music (%s,%s)values(%s,%s)',['music_name','album_name','beautiful day','2015薛之谦巡回演唱会'])cursor.execute('update music set (%s,%s)=(%s,%s)',['music_name','album_name','beautiful day','2015薛之谦巡回演唱会'])

Pg转义

Pg用”’转义”’,

select * from music where music_name = 'it do''t matter'//it do't matter

接触了也不是很长的时间，如果有错误，望指教。

END