一、大小写特别敏感
大写字段需要用“”引号(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;