PostgreSQL——函数

一、数学函数

数学函数主要用来处理数值数据，主要的数学函数有：绝对值函数、三角函数（包括正弦函数、余弦函数、正切函数、余切函数等)、对数函数、随机数函数等。当有错误产生时，数学函数
会返回空值NULL。

1.1、绝对值函数ABS(x)和圆周率函数PI()

SELECT ABS(2), ABS(-3.3), ABS(-33);

-- 保留15位有效数字
SELECT PI();

1.2、平方根函数SQRT(x)和求余函数MOD(x,y)

SQRT(x)返回非负数x的二次平方根。

SELECT SQRT(9), SQRT(40);

MOD(x,y)返回x被y除后的余数，MODO对于带有小数部分的数值也起作用，它返回除法运算后的精确余数。

SELECT MOD(31,8), MOD(234, 10), MOD(45.5, 6);

1.3、取整函数CEIL(x)、CEILING(x)和FLOOR(x)

CEIL(x)和CEILING(x)意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT。

SELECT CEIL(-3.35), CEILING(3.35);

FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。

SELECT FLOOR(-3.35), FLOOR(3.35);

1.4、四舍五入函数ROUND(x)和ROUND(x,y)

ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入。

SELECT ROUND(-1.14), ROUND(-1.67), ROUND(1.14), ROUND(1.66);

ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

SELECT ROUND(1.38, 1), ROUND(1.38, 0), ROUND(232.38, -1), ROUND(232.38, -2);

1.5、符号函数SIGN(x)

SIGN(x)返回参数的符号，x的值为负、零或正时，返回结果依次为-1、0或1。

SELECT SIGN(-21), SIGN(0), SIGN(21);

1.6、幂运算函数POW(x,y)、POWER(x,y)和EXP(x)

POW(x,y)或者POWER(x,y)函数返回x的y次乘方的结果值。

SELECT POW(2,2), POWER(2, 2), POW(2, -2), POWER(2, -2);

EXP(x)返回e的x乘方后的值。

SELECT EXP(3), EXP(-3), EXP(0);

1.7、对数运算函数LOG(x)

LOG(x)返回x的自然对数，x相对于基数©的对数。对数定义域不能为负数，否则将会弹出错误信息。

SELECT LOG(3);

1.8、角度与弧度转换函数RADIANS(x)和DEGREES(x)

RADIANS(x)将参数x由角度转化为弧度。

SELECT RADIANS(90), RADIANS(180);

DEGREES(x)将参数x由弧度转化为角度。

SELECT DEGREES(PI()), DEGREES(PI() / 2);

1.9、正弦函数SIN(x)和反正弦函数ASIN(x)

SN(x)返回x的正弦，其中，x为弧度值。

SELECT SIN(1), ROUND(SIN(PI()));

ASN(x)返回x的反正弦，即正弦为x的值。若x不在-1到1的范围之内，则会弹出错误信息：“输入超出范围”。

SELECT ASIN(0.8414709848078965), ASIN(3);

1.10、余弦函数COS(x)和反余弦函数ACOS(x)

COS(x)返回x的余弦，其中x为弧度值。

SELECT COS(0), COS(PI()), COS(1);

ACOS(x)返回x的反余弦，即余弦是x的值。若x不在-1到1的范围之内，则会弹出错误信息。

SELECT ACOS(1), ACOS(0);

1.11、正切函数、反正切函数和余切函数

TAN(x)返回x的正切，其中x为给定的弧度值。

SELECT TAN(0.3), ROUND(TAN(PI() / 4));

ATAN(x)返回x的反正切，即正切为x的值。

SELECT ATAN(0.30933624960962325), ATAN(1);

COT(x)返回x的余切。

SELECT COT(0.3), 1 / TAN(0.3), COT(PI() / 4);

二、字符串函数

2.1、字符串字符数CHAR_LENGTH(str)和字符串长度函数LENGTH(s)

CHAR LENGTH(str)返回值为字符串sr所包含字符的个数。一个多字节字符算作一个单字符。

SELECT CHAR_LENGTH('date'), CHAR_LENGTH('egg');

LENGTH(s)返回值为字符串的字节长度，使用utf8编码字符集时，一个汉字是3个字节，一个数字或字母算作一个字节。

SELECT LENGTH('date'), LENGTH('egg');

可以看到，计算的结果与CHAR LENGTH相同，因为英文字符的个数和所占的字节相同，一个字符占一个字节。

2.2、合并字符串函数CONCAT(s1, s2,…)和CONCAT_WS(x,s1,s2,…)

CONCAT(sI,s2,...)返回结果为连接参数产生的字符串。如果有任何一个参数为NULL,则返回值为NULL:如果所有参数均为非二进制字符串，则结果为非二进制字符串；如果自变量中含有任何一个二进制字符串，则结果为一个二进制字符串。

SELECT CONCAT('PostgreSQL', '9.15'), CONCAT('Postgre', NULL, 'SQL');

CONCAT WS(x,sl,s2,...),其中，CONCAT WS代表CONCAT With Separator,是CONCATO的特殊形式。第一个参数x是其他参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为NULL,则结果为NULL。函数会忽略任何分隔符参数后的NULL值。

SELECT CONCAT_WS('-', '1st', '2nd', '3rd'), CONCAT_WS('*', '1st', NULL, '3rd');

2.3、获取指定长度的字符串函数LEFT(s,n)和RIGHT(s,n)

LEFT(s,n)返回字符串s开始的最左边n个字符。

SELECT LEFT('football', 5);

RIGHT(s,n)返回字符串s最右边n个字符。

SELECT RIGHT('football', 4);

2.4、填充字符串函数LPAD(s1,len,s2)和RPAD(s1,len,s2)

LPAD(sl,len,s2)返回字符串sl,其左边由字符串s2填充，填充至len字符长度。假如sl的长度大于len,则返回值被缩短至len字符。

SELECT LPAD('hello', 4, '?'), LPAD('hello', 10, '?');

RPAD(sl,len,s2)返回字符串sl,其右边被字符串s2填充至len字符长度。假如sl的长度大于len,则返回值被缩短到与len字符相同长度。

SELECT RPAD('hello', 4, '?'), RPAD('hello', 10, '?');

2.5、删除空格函数LTRIM(s)、RTRIM(s)和TRIM(s)

LTRIM(s)返回字符串s,字符串左侧空格字符被删除。

SELECT '( book )', CONCAT('(', LTRIM(' book '), ')');

RTRIM(s)返回字符串s,字符串右侧空格字符被删除.

SELECT '( book )', CONCAT('(', RTRIM(' book '), ')');

TRIM(s)刑除字符串s两侧的空格。

SELECT '( book )', CONCAT('(', TRIM(' book '), ')');

3.6、删除指定字符串函数TRIM(s1 FROM s)

TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，别除空格。

SELECT TRIM('xy' FROM 'xyboxyokxyxy');

3.7、重复生成字符串函数REPEAT(s,n)

REPEAT(s,n)返回一个由重复的字符串s组成的字符串，n表示重复生成的次数。若n<=0,则返回一个空字符串。若s或n为NULL,则返回NULL。

SELECT REPEAT('PostgreSQL', 3);

3.8、替换函数REPLACE(s, s1, s2)

REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。

SELECT REPLACE('xxx.PostgreSQL.com', 'x', 'w');

3.9、获取子串函数SUBSTRING(s,n,len)

SUBSTRING(s,n,len)表示从字符串s返回一个长度为len的子字符串，起始于位置n。也可能对n使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符。

SELECT SUBSTRING('breakfast', 5) AS col1,SUBSTRING('breakfast', 5, 3) AS col2,SUBSTRING('luch', -3) AS col3;

2.10、匹配子串开始位置函数POSITION(str1 IN str)

POSITION(strl IN str)函数的作用是返回子字符串strl在字符串str中的开始位置。

SELECT POSITION('ball' IN 'football');

2.11、字符串逆序函数REVERSE(s)

REVERSE(s)将字符串s反转，返回的字符串的顺序和s字符顺序相反。

SELECT REVERSE('abc');

三、日期和时间函数

3.1、获取当前日期函数CURRENT_DATE和获取当前时间函数CURRENT_TIME、LOCALTIME

CURRENT_DATE函数的作用是将当前日期按照YYYY-MM-DD’格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。

SELECT CURRENT_DATE;

CURRENT_TIME函数的作用是将当前时间以HH:MM:SS’的格式返回，具体格式根据函数用在字符串或是数字语境中而定。

SELECT CURRENT_TIME;

LOCALTIME函数的作用是将当前时间以’HH:MM:SS’的格式返回，唯一和CURRENT_TIME函数不同的是，返回的是不带时区的值。

SELECT LOCALTIME;

3.2、获取当前日期和时间的函数

CURRENT_TIMESTAMP、LOCALTIMESTAMP和NOW()3个函数的作用相同，即返回当前日期和时间值，格式为YYYY-MM-DDHH:MM:SS或YYYYMMDDHHMMSS,具体格式根据函数是否用在字符串或数字语境而定。

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, NOW();

可以看到，3个函数返回的日期和时间是相同的。唯一不同的是，LOCALTIMESTAMP函数的返回值不带时区。

3.3、获取日期指定值函数EXTRACT(type FROM d)

EXTRACT(ype FROM date)函数从日期中提取其部分，而不是执行日期运算。

SELECT EXTRACT(DAY FROM TIMESTAMP '2012-09-10 10:18:40'),EXTRACT(MONTH FROM TIMESTAMP '2012-09-10 10:18:40'),EXTRACT(YEAR FROM TIMESTAMP '2012-09-10 10:18:40');

-- 使用EXTRACT函数查询指定日期是一年中的第几天
SELECT EXTRACT(DOY FROM TIMESTAMP '2012-09-10 10:18:40');

-- 使用EXTRACT函数查询指定日期是一周中的星期几
SELECT EXTRACT(DOW FROM TIMESTAMP '2012-09-10 10:18:40');

-- 使用EXTRACT函数查询指定日期是该年的第几季度(1~4)
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2012-09-10 10:18:40');

3.4、日期和时间运算操作

-- 计算指定日期加上间隔天数后的结果
SELECT DATE '2012-09-28' + integer '10';

-- 计算指定日期加上间隔小时后的结果
SELECT DATE '2012-09-28' + interval '3 hour';

-- 计算指定日期加上指定时间后的结果
SELECT DATE '2012-09-28' + time '06:00';

-- 计算指定日期和时间加上间隔时间后的结果
SELECT TIMESTAMP '2012-09-28 02:00:00' + interval '10 hours';

-- 计算指定日期之间的间隔天数
SELECT DATE '2012-11-01' - DATE '2012-09-10';

-- 计算指定日期减去间隔天数后的结果
SELECT DATE '2012-11-01' - INTEGER '10';

-- 计算整数与天数相乘的结果
SELECT 15 * interval '2 day';

-- 计算整数与秒数相乘的结果
SELECT 50 * interval '2 second';

-- 计算小时数与整数相除的结果
SELECT interval '1 hour' / integer '2';

四、条件判断函数

4.1、CASE value WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE m] END

该函数表示，如果value值等于某个vn,则返回对应位置THEN后面的结果：如果与所有值都不相等，则返回ELSE后面的m。

SELECT CASE 2 WHEN 1 THEN 'one'WHEN 2 THEN 'two'ELSE 'more' END;

4.2、CASE WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END

该函数表示，某个vn值为TRUE时，返回对应位置THEN后面的结果：如果所有值都不为TRUE,则返回ELSE后的m。

SELECT CASE WHEN 1 < 0 THEN 'true'ELSE 'false' END;

五、系统信息函数

5.1、获取PostgreSQL版本号VERSION()

VERSIONO返回指示PostgreSQL服务器版本的字符串。这个字符串使用utf8字符集。

SELECT VERSION();

5.2、获取用户名函数USER和CURRENT_USER

USER和CURRENT_USER函数返回当前被PostgreSQL服务器验证的用户名。这个值符合有定当前登录用户存取权限的PostgreSQL账户。一般情况下，这两个函数的返回值是相同的。

SELECT USER, CURRENT_USER;

六、加密函数

6.1、加密函数MD5(str)

MD5(st)为字符串算出一个MD5128比特检查和。该值以32位十六进制数字的二进制字符串的形式返回，若参数为NULL则会返回NULL。

SELECT MD5('mypwd');

6.2、加密函数ENCODE(str, pswd_str)

ENCODE(str,pswd_str)使用pswd_str作为加密编码，来加密str。常见的加密编码包括：base64、hex和escape.

SELECT ENCODE('secret', 'hex'), LENGTH(ENCODE('secret', 'hex'));

6.3、解密函数DECODE(crypt_str, pswd_str)

DECODE(crypt_str,pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str,cypt_str是由ENCODEO返回的字符串。

SELECT DECODE(ENCODE('secret', 'hex'), 'hex');

七、改变数据类型函数CAST(x AS type)

CAST(x,AS type)函数将一个类型的值转换为另一个类型的值。

SELECT CAST(100 AS CHAR(2));

可以看到，CAST(100 AS CHAR(2)将整数数据100转换为带有2个显示宽度的字符串类型，结果为10。