PostgreSQL 常用函数（一）

函数（function）是一些预定义好的代码模块，可以将输入进行计算和处理，最终输出一个结果值。PostgreSQL函数可以分为两类：标量函数（scalar function）和聚合函数（aggregation function）。标量函数针对每个输入都会返回相应的结果，聚合函数针对一组输入汇总出一个结果。为了便于学习，可以将常见的系统函数分为以下类别：数学函数、字符函数、日期时间函数以及类型转换函数。除了可以使用这些系统内置的函数之外，PostgreSQL也支持创建自定义的函数（UDF）。

数学函数

数学函数和运算符用于执行算术运算，输入和输出通常都是数字类型。

绝对值

abs(x)函数用于计算x的绝对值。例如：

select abs(-2.2) --2.2

取整函数

ceil(dp)/ceiling(dp)函数用于计算大于或等于dp的最小整数；floor(dp)函数用于计算小于或等于dp的最大整数；round(dp)函数四舍五入为整数；trunc(dp)函数向零取整。 

SELECT ceil(-42.8),--大于 -42.8 的最小整数
       floor(-42.8),--小于 -42.8 的最小整数
       round(12.45), --12 四舍五入取整数
       trunc(12.8), --12 截取整数
       round(12.488, 2), --12.49 保留2位小数
       trunc(12.488, 2);-- 12.48取2位小数，直接截取，不是四舍五入

乘方与开方

power(a, b)函数计算a的b次方；sqrt(dp)函数计算dp的平方根；cbrt(dp)函数计算dp的立方根

SELECT power(2, 3),--8
       sqrt(4), --2
       cbrt(27)  --3.0000000000000004

指数与对数

exp(dp)函数计算以自然常数e为底的指数，ln(dp)函数计算以自然常数e为底数的对数，log(dp)/log10(dp)函数计算以10为底的对数，log(b, x)函数计算以b为底的对数。

       SELECT exp(1.0), -- 2.7182818284590452
              ln(2.718281828459045), -- 0.9999999999999999
              log(100), --2
              log(2.0, 16.0);--4

整数商和余数

div(y, x)函数计算y除以x的整数商，mod(y, x)函数计算y除以x的余数。

              SELECT div(9,4), -- 2
                     mod(9,4); --1

弧度与角度

degrees(dp)函数用于将弧度转为角度，radians(dp)函数用于将角度转弧度。

SELECT degrees(1.57),--89.95437383553924
       radians(90.0); --1.5707963267948966

常量π

pi()函数用于返回常量“π”的值。

SELECT pi();--3.141592653589793

符号函数

sign(dp)函数返回参数的正负号，可能的结果为-1、0、+1

SELECT sign(8.4) --1
     , sign(-2)  ---1
     , sign(0.4) --1
     , sign(0.000) --0

生成随机数

PostgreSQL提供了用于返回一个随机数的函数random()。

SELECT random(), --0.007556789489307825
       random() ;-- 0.6077545699183775

setseed(dp)函数可以为随后一次运行的random()函数设置种子数，范围：-1.0 <= dp <= 1.0

SELECT setseed(0);
SELECT random()

字符串函数

字符串连接

concat(str, ...)函数用于连接字符串，并且忽略其中的NULL参数；concat_ws(sep, str, ...)函数使用指定分隔符sep连接字符串。

SELECT concat(11, NULL, 22),--1122
       concat_ws(' and ', 2, NULL, 22);-- 2 and 22

两个竖杠（||）也可以用于连接字符串，但是NULL参数将会返回NULL。

 SELECT 'Post'||'greSQL',-- PostgreSQL
        'Post'||NULL||'greSQL' ---null;

字符串长度

bit_length(string)函数用于计算字符串包含的比特数；length(string)、char_length(string)、character_length(string)函数计算字符串包含的字符数；octet_length(string)函数计算字符串包含的字节数。

SELECT bit_length('jose'),--32
       length('jose'), --4
       octet_length('jose'); --4
SELECT bit_length('笑傲'),--48
       length('江湖'), --2
       octet_length('哈哈'); --6

大小写转换

lower(string)函数将字符串转换为小写形式，upper(string)函数将字符串转换为大写形式，initcap(string)函数将每个单词的首字母大写，其他字母小写

SELECT lower('Jack'), --jack
       upper('ali'),  --ALI
       initcap('Ali BaBa'); --Ali Baba

子串查找与替换

substring(string [FROM] [for])函数用于提取从位置FROM开始的for个字符子串，位置从1开始计算。substr(string, FROM[, count])的作用相同。

SELECT substring('nihao' FROM 2 for 3),--iha
         substr('xiaofeng',2, 3); --iao

translate(string , FROM, to)函数将字符串string中出现在FROM中的字符串替换成to中相应位置的字符。如果FROM长度大于to，在to中没有对应值的字符将被删除

SELECT translate('12345', '143', 'ax');--a2x5

字符串拆分

 regexp_split_to_table(string, pattern[, flags])函数用于拆分字符串，使用POSIX正则表达式作为分隔符。函数的返回类型是text集合 

SELECT regexp_split_to_table('hello 晓枫', '\s+');
hello
晓枫

split_part(string, delimiter, field)函数使用delimiter拆分字符串，并返回指定项（从1开始计数）

SELECT split_part('abc#~def#~ghi', '#~', 2);--def

字符串反转

reverse(str)函数用于将字符串反转

SELECT reverse('读书好') --好书读