使用PostgreSQL注意事项
一、大小写特别敏感
大写字段需要用“”引号（pg字段名使用“”，MySQL字段名使用``）
******表名以及字段名如果是小写但是为关键字，比如name，则也需使用""；
二、分页
limit ${limit} offset ${start}
三、判断条件等号前后字段类型需要一致
类型转换相关函数
pg使用文档：https://www.runoob.com/postgresql/postgresql-functions.html
|
函数 |
返回类型 |
描述 |
实例 |
|
to_char(timestamp, text) |
text |
将时间戳转换为字符串 |
to_char(current_timestamp, 'HH12:MI:SS') |
|
to_char(interval, text) |
text |
将时间间隔转换为字符串 |
to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
|
to_char(int, text) |
text |
整型转换为字符串 |
to_char(125, '999') |
|
to_char(double precision, text) |
text |
双精度转换为字符串 |
to_char(125.8::real, '999D9') |
|
to_char(numeric, text) |
text |
数字转换为字符串 |
to_char(-125.8, '999D99S') |
|
to_date(text, text) |
date |
字符串转换为日期 |
to_date('05 Dec 2000', 'DD Mon YYYY') |
|
to_number(text, text) |
numeric |
转换字符串为数字 |
to_number('12,454.8-', '99G999D9S') |
|
to_timestamp(text, text) |
timestamp |
转换为指定的时间格式 time zone convert string to time stamp |
to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
|
to_timestamp(double precision) |
timestamp |
把UNIX纪元转换成时间戳 |
to_timestamp(1284352323) |
四、日期格式转换（格式跟oracle类似）
eg：
时间转字符串：to_char(now(),'yyyy-mm-dd hh24:mi:ss')；
|
占位符 |
描述 |
|
HH |
一天的小时数(01-12) |
|
HH12 |
一天的小时数(01-12) |
|
HH24 |
一天的小时数(00-23) |
|
MI |
分钟(00-59) |
|
SS |
秒(00-59) |
|
MS |
毫秒(000-999) |
|
US |
微秒(000000-999999) |
|
AM |
正午标识(大写) |
|
Y,YYY |
带逗号的年(4和更多位) |
|
YYYY |
年(4和更多位) |
|
YYY |
年的后三位 |
|
YY |
年的后两位 |
|
Y |
年的最后一位 |
|
MONTH |
全长大写月份名(空白填充为9字符) |
|
Month |
全长混合大小写月份名(空白填充为9字符) |
|
month |
全长小写月份名(空白填充为9字符) |
|
MON |
大写缩写月份名(3字符) |
|
Mon |
缩写混合大小写月份名(3字符) |
|
mon |
小写缩写月份名(3字符) |
|
MM |
月份号(01-12) |
|
DAY |
全长大写日期名(空白填充为9字符) |
|
Day |
全长混合大小写日期名(空白填充为9字符) |
|
day |
全长小写日期名(空白填充为9字符) |
|
DY |
缩写大写日期名(3字符) |
|
Dy |
缩写混合大小写日期名(3字符)dy缩写小写日期名(3字符) |
|
DDD |
一年里的日子(001-366) |
|
DD |
一个月里的日子(01-31) |
|
D |
一周里的日子(1-7；周日是1) |
|
W |
一个月里的周数(1-5)(第一周从该月第一天开始) |
|
WW |
一年里的周数(1-53)(第一周从该年的第一天开始) |
转时间格式
to_date( #{req_tm},'yyyy-mm-dd')
select now()::varchar(19);
select cast( '2018-03-22 06:15:16'as timestamp)
|
函数 |
返回类型 |
描述 |
例子 |
|
to_char(timestamp, text) |
text |
把时间戳转换成字串 |
to_char(current_timestamp, 'HH12:MI:SS') |
|
to_char(interval, text) |
text |
把时间间隔转为字串 |
to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
|
to_char(int, text) |
text |
把整数转换成字串 |
to_char(125, '999') |
|
to_char(double precision, text) |
text |
把实数/双精度数转换成字串 |
to_char(125.8::real, '999D9') |
|
to_char(numeric, text) |
text |
把numeric转换成字串 |
to_char(-125.8, '999D99S') |
|
to_date(text, text) |
date |
把字串转换成日期 |
to_date('05 Dec 2000', 'DD Mon YYYY') |
|
to_timestamp(text, text) |
timestamp |
把字串转换成时间戳 |
to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
|
to_timestamp(double) |
timestamp |
把UNIX纪元转换成时间戳 |
to_timestamp(200120400) |
|
to_number(text, text) |
numeric |
把字串转换成numeric |
to_number('12,454.8-', '99G999D9S') |
五、实现MySQL中的GROUP_CONCAT()方法
使用：array_to_string(GROUP_CONCAT(DISTINCT cgu.group_id),',')
创建自定义聚合函数
CREATE AGGREGATE group_concat(anyelement)
(
sfunc = array_append, -- 每行的操作函数，将本行append到数组里
stype = anyarray, -- 聚集后返回数组类型
initcond = '{}' -- 初始化空数组
);
六、null替换
coalesce(pmodule_id,'')
七、模糊查询
SELECT * FROM public."user"
WHERE UPPER(username) LIKE UPPER('%' || #{username} || '%');
SELECT * FROM public."user"
WHERE CONCAT(username) LIKE CONCAT('%', #{username}, '%');
SELECT * FROM public."user"
WHERE username LIKE '%' || #{username} || '%';
八、实现MySQL中的主键自增功能
为字段添加自增功能时需要创建序列
--创建序列字段自增
CREATE SEQUENCE imp_item_status_map_seq --序列名
START WITH 200 --起始值
INCREMENT BY 1 --增量
NO MINVALUE
NO MAXVALUE
CACHE 1;
--删除序列
DROP SEQUENCE imp_item_status_map_seq cascade;
--将表中字段关联序列
--mapping_id 需要关联表中的字段
alter table imp_item_status_map alter column mapping_id set default nextval('imp_item_status_map_seq');
九、
--创建类似UUID（）函数方法
create or replace function random_string(integer)
returns text as
$body$
select array_to_string(array(select substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' FROM (ceil(random()*62))::int FOR 1) FROM generate_series(1, $1)), '');
$body$
language sql volatile;
