postgre数据库与mysql数据库对比

postgre数据库与mysql数据库对比

1. pg数据库group by 前后必须一致，group by 里的字段必须在查询结果中出现；mysql要求没有这么严格
2. 精确匹配 pg:b.name= ANY(String_to_array(a.fname,‘’)) 对应mysql: FIND_IN_SET(b.name,a.fname) 注：a.fname 格式大概是：1,2,3,4
3. 以-分割符取第一个字符串pg:split_part(fcodes,‘‘,1) 对应mysql:SUBSTRING_INDEX(fcodes,’’,1)
4. postgre计算日期之间的天数：date_part(‘day’,CURRENT_DATE::TIMESTAMP - to_date(yourtimes,‘yyyy-MM-dd’)::TIMESTAMP )对应mysql:DATEDIFF(CURDATE(), yourtimes)
5. 类型要求 pg不会自动将varchar转换成int 比如 mysql: between a.score and a.maxscore 对应pg就得这样：between cast(a.score as Integer) and cast(a.maxscore as integer)
6. 查看进程 锁表情况mysql: show processlist //结束进程:kill pid; postgre:SELECT pid, state, backend_start, substr(query, 0, 100) q FROM pg_stat_activity WHERE backend_type = ‘client backend’ ORDER BY backend_start;
   //结束进程
   Select pg_cancel_backend(pid);
   或：select pg_terminate_backend(pid);
7. 待续