postgresql常见数值,字符,日期类型常见函数总结

一.字符函数

 

函数	描述	例子
char_length(string)	获取字符串中字符的个数	test=> select char_length('abc123苏'); char_length  -------------           7
length(string)	获取字符的数目.经过测试好像和char_length没有区别.	test=> select length('abc123中国'); length  --------       8
convert()	使用指定的转换名改变编码	test=> select convert('aa','utf8','gbk'); convert  --------- \x6161
lower/upper(strings)	将字符串转换为小写/大写	test=> select lower('ABC'),upper('abc'); lower | upper  -------+------- abc  | ABC
initcap(string)	将字符串的第一个字母转换为大写,其它保留小写	test=> select initcap('abc'); initcap  --------- Abc
octet_length(string)	返回字符串中的字节数	test=> select octet_length('abc中国'),octet_length('abcdef'); octet_length | octet_length  --------------+--------------             9 |            6
position(strings in string)	返回指定字符串的位置,如果没有则为0	test=> select position('a'  in '123abc'); position  ----------         4
strpos(str,str)	获取指定字符串的位置,和position类似,不过参数顺序不一致	test=> select strpos('abcdefgh','c'); strpos  --------       3
substr(string,int,int)	抽取字符串中的指定位置的字符	test=> select substr('abcdef',2,5); substr  -------- bcdef (1 row)
substring	抽取更多规则的子字符串,支持正则表达式,例如抽取字符串最后一个字符	test=> select substring('abcdef','.$'); substring  ----------- f
trim(string,string)	在字符串两边截取指定字符,可以是多个字符.默认为空字符	test=> select trim('xyxtrimyyx', 'xyt'); btrim  ------- rim
rtrim,ltrim	在字符串的右边/左边删除指定的字符串	test=> select rtrim('abcdefa','a'),ltrim('abcdefa','a'); rtrim  | ltrim   --------+-------- abcdef | bcdefa
btrim(string,string)	在字符串的两边删除指定的字符串,直到遇到一个不是指定的字符串为止.注意与trim的区别.	经过测试和trim好像是一样的
lpad(string,int,string)/rpad	在字符串的左边/右边填充字符串,使得字符串的长度达到指定长度.	test=> select lpad('abc',10,'x'),rpad('abc',10,'x');     lpad    |    rpad     ------------+------------ xxxxxxxabc | abcxxxxxxx
ascii(str)/chr(int)	将字符串转换为ascii码,将ascii码转换成字符串	test=> select ascii('a'),chr(97); ascii | chr  -------+-----     97 | a
decode/encode	将encode编码的strings转换为二进制/将二进制的编码转换为strings.注意和oracle中decode的区别.	test=> select decode('b3NkYmEAAQ==','base64');       decode       ------------------ \x6f73646261000
md5(string)	获取字符串的md5值	test=> select md5('brent');               md5                 ---------------------------------- fddd76411252e465ab9edde8ebed3d0a
quote_ident(string)	返回适合于sql语句的标识符形式只有在必要的才会添加引号.一般用在有特殊字符的地方	test=> select quote_ident('"ss'),quote_ident('abc'),quote_ident('abc\'); quote_ident | quote_ident | quote_ident  -------------+-------------+------------- """ss"      | abc        | "abc\"
quote_literal(string)	返回适用于sql语句中的文本形式	test=> select quote_literal('"ss'),quote_literal('abc'),quote_literal('abc\'); quote_literal | quote_literal | quote_literal  ---------------+---------------+--------------- '"ss'        | 'abc'        | E'abc\\'
regxp_replace(strings,pattern,strings)	匹配替换posix正则表达式的字符串	test=> select regexp_replace('abc123efg','..[1-9]+','#'); regexp_replace  ---------------- a#efg
repeat(strings,int)	将字符串重复N次	test=> select repeat('abc',3);   repeat   ----------- abcabcabc
replace(str,str)	将字符串替换成指定字符串.注意这里的字符串是连续的字符串,而不是按照字符进行替换	test=> select replace('aaabbbccc','b','23');   replace     -------------- aaa232323ccc
translate(str,str)	将字符串中的字符转换成指定字符,注意这里的是进行字符串的转换,和replace是不同的.	test=> select translate('aaabbbccc','ab','xy'),replace('aaabbbccc','ab','xy'); translate |  replace   -----------+----------- xxxyyyccc | aaxybbccc 这里的例子中,translate只是将a转换成x,b转换成y,和replace完全不同
split_part(str,delimiter,int)	根据delimiter截取,获取指定的part,有点像shell中的awk命令	test=> select split_part('brent,no.1,93',',',2); split_part  ------------ no.1 (1 row)
to_hex(int)	将number转换为16进制	test=> select to_hex(1000); to_hex  -------- 3e8 (1 row)

 

 

 

 

二.数值函数

 

函数	描述	例子
ceil	取整数,取大值	test=> select ceil(35.7); ceil  ------   36 (1 row)
floor	取整数,取小值	test=> select floor(35.7); floor  -------     35
round(numeric,int)	四舍五入.后面的值为精度,如果后面的精度没有则取整	test=> select round(36.12),round(36.567,2); round | round  -------+-------     36 | 36.57
trunc(numeric,int)	截断,后面的为精度,如果不设置精度则为取整,类似于floor	test=> select trunc(32.567),trunc(32.567,2); trunc | trunc  -------+-------     32 | 32.56
random()	0.0到1.0之间的随机数	test=> select random();       random       ------------------- 0.679839549586177

 

 

 

 

三.日期函数

 

 

函数	描述	例子
age(timstamp,timestamp)	返回一个时间差,例如年龄,第一个时间减去第二个时间.如果第一个参数不写,那么默认是当前时间,第二个参数需要格式为timestamp类型	test=>  select age(timestamp '1987-10-15'),age('2018-09-16','1987-10-16');           age          |      age         ------------------------+------------------ 30 years 11 mons 1 day | 30 years 11 mons
clock_timestamp()	实时时钟的当前时间戳.和current_timestamp的区别是,当开启一个事务后,current_timestamp是不变的表示事务的开启时间,而clock_timestamp()表示时钟时间,所以在一个sql语句中的clock时间也有可能是不同的.	test=> begin; BEGIN test=> select current_timestamp,clock_timestamp();       current_timestamp      |        clock_timestamp         -------------------------------+------------------------------- 2018-09-16 10:45:53.653818+08 | 2018-09-16 10:46:04.311372+08 (1 row) test=> select current_timestamp,clock_timestamp();       current_timestamp      |        clock_timestamp         -------------------------------+------------------------------- 2018-09-16 10:45:53.653818+08 | 2018-09-16 10:46:11.469315+08 (1 row) test=> end; COMMIT
statement_timestamp()	当前语句执行的系统时间,在一个sql中的时间是一致的.可以看这个例子,同一个sql中,三个时间都不一致	test=> select clock_timestamp(),clock_timestamp(),statement_timestamp();         clock_timestamp        |        clock_timestamp        |      statement_timestamp       -------------------------------+-------------------------------+------------------------------- 2018-09-16 11:33:59.982234+08 | 2018-09-16 11:33:59.982235+08 | 2018-09-16 11:33:59.982161+08
timeofday()	等同于clock_timestamp(),但是返回的是一个text字符串	test=> select timeofday();               timeofday               ------------------------------------- Sun Sep 16 10:51:58.989842 2018 CST
current_timestamp	当前事务的开启时间戳.在一个事务内时间保持不变.带时区.还可以接一个参数,指定精度,该精度导致结果的秒数四舍五入到小数位.	test=> select current_timestamp(0),current_timestamp;   current_timestamp    |      current_timestamp       ------------------------+------------------------------- 2018-09-16 11:27:19+08 | 2018-09-16 11:27:19.112954+08
current_date	当前事务的日期,在一个事务内日期不变
current_time	当前事务的时间,在一个事务内时间不变
localtime	等同于current_time,不带时区
localtimestamp	等同于current_timestamp,不带时区.还可以接一个参数,指定精度,该精度导致结果的秒数四舍五入到小数位.	test=> select localtimestamp,localtimestamp(0);       localtimestamp      |  localtimestamp     ----------------------------+--------------------- 2018-09-16 11:26:37.935849 | 2018-09-16 11:26:38
now()	等同于current_timestamp
transaction_timestamp()	等同于current_timestamp
extract()	获取子域	test=> select now(),extract(year from now()),extract(month from now()),extract(day from now()),extract(minute from now()),extract(second from now());               now              | date_part | date_part | date_part | date_part | date_part  -------------------------------+-----------+-----------+-----------+-----------+----------- 2018-09-16 11:08:25.431863+08 |      2018 |        9 |        16 |        8 | 25.431863
date_part(text,timestamp)	获取子域,等同于extract	test=> select date_part('hour',now()),date_part('minute',now()),date_part('month',now()); date_part | date_part | date_part  -----------+-----------+-----------         11 |        10 |        9
date_trunc(text,timestamp)	截断指定的精度	test=> select date_trunc('day',current_timestamp),date_trunc('hour',current_timestamp);       date_trunc      |      date_trunc       ------------------------+------------------------ 2018-09-16 00:00:00+08 | 2018-09-16 11:00:00+08

EXTRACT和date_part函数的参数

值	说明	示例
century	世纪
year	年
decade	年份/10
millennium	第几个千年,0-1000是1,1000-2000是2,2000-3000是3
quarter	第几季度
month	月
week	第几个星期
dow	星期几,0是星期天...
day	本月的第几天
doy	本年的第几天
hour	得到时间中的小时
minute	得到时间中的分钟
second	得到时间中的秒
epoch	相对于1970-01-01 00:00:00以来的时间.	test=> select date_part('epoch',now());     date_part     ------------------ 1537069799.45484
milliseconds	秒域/毫秒数,即秒数乘以1000	test=> select current_timestamp,extract(milliseconds from current_timestamp);       current_timestamp      | date_part  -------------------------------+----------- 2018-09-16 11:55:58.041867+08 | 58041.867
microseconds	秒域/微秒数,即秒数乘以1000	test=> select current_timestamp,extract(microseconds from current_timestamp);       current_timestamp      | date_part  -------------------------------+----------- 2018-09-16 11:56:26.533713+08 |  26533713
timezone	时区偏移量(秒).+8就是8*3600=28800	test=> select extract(timezone from current_timestamp); date_part  -----------     28800
timezone_hour	时区偏移量(小时),+8就是8	test=> select extract(timezone_hour from current_timestamp); date_part  -----------         8
timezone_minute	时区偏移量的小时部分,整数都返回0	test=> select extract(timezone_minute from current_timestamp); date_part  -----------         0

 

总结:

1.如果是取事务时间,如果需要时区,一般用current_timestamp或者now(),current_timestamp的好处是可以去精度

2.如果是取事务时间,如果不需要时区,一般用localtimestamp.localtimestamp也可以取精度

3.如果是取每个语句执行的时间,则使用statement_timestamp()