postgresql常用技术（一、函数运算符）

1.数学运算符

运算符

描述

例子

结果

+

加

2 + 3

5

-

减

2 - 3

-1

*

乘

2 * 3

6

/

除 （两个整数相除的结果如果不是整数，会将结果的小数部分去掉，只保留整数部分）

（1）4 / 2

（2）3／2

（3）2／4

（1）2

（2）1

（3）0

%

模除 （求余）

5 % 4

1

^

幂（指数运算）

2.0 ^ 3.0

8

|/

平方根

|/ 25.0

5

||/

立方根

||/ 27.0

3

!

阶乘

5 !

120

!!

阶乘 （前缀运算符）

!! 5

120

@

绝对值

@ -5.0

5

&

按位 AND

91 & 15

11

|

按位OR

32 | 3

35

#

按位XOR

17 # 5

20

~

按位NOT

~1

-2

<< 

按位左移

1 << 4

16

>> 

按位右移

8 >> 2

2

2.1数学函数

函数

返回值类型

描述

abs(x)

和x类型相同

绝对值

cbrt(dp)

Dp

立方根

ceil(dp 或者 numeric)

与输入相同

不小于参数的最小的整数

ceiling(dp or numeric)

与输入相同

不小于参数的最小整数（ceil 的别名）

degrees(dp)

dp

把弧度转为角度

exp(dp 或 numeric)

与输入相同

自然指数

floor(dp 或 numeric)

与输入相同

不大于参数的最大整数

ln(dp 或 numeric)

与输入相同

自然对数

log(dp 或 numeric)

与输入相同

10 为底的对数

log(b numeric, x numeric)

numeric

指定底数的对数

mod(y, x)

和参数类型相同

除法 y/x 的余数（模）

pi()

Dp

"π" 常量

power(a dp, b dp)

Dp

求a的 b 次幂

power(a numeric, b numeric)

numeric

求a的 b 次幂

radians(dp)

Dp

把角度转为弧度

random()

Dp

0.0 到 1.0 之间的随机数值

round(dp 或者 numeric)

与输入相同

约为最接近参数的整数

round(v numeric, s int)

Numeric

约为最接近参数的有s位小数的数字

setseed(dp)

Int

为以后被调用的 random()函数设置种子

sign(dp 或者 numeric)

和输入相同

参数的符号（-1, 0, +1）

sqrt(dp 或者 numeric)

和输入相同

平方根

trunc(dp 或者 numeric)

和输入相同

去掉参数的小数位

trunc(v numeric, s int)

Numeric

将参数截断为含有 s位小数的数字

width_bucket(op numeric, b1 numeric, b2 numeric, count in)

Int

将b1和b2平分成count个取值区间，取值区间的编号从1开始。

 

如果b1>b2，则编号为0的取值区间表示的范围是（b1,正无穷大),编号为count+1的取值区间表示的范围是(负无穷大，b2)。

 

如果b1<b2，则编号为0的取值区间表示的范围是(负无穷大,b1),编号为count+1的取值区间表示的范围是(b2,负无穷大)。

 

若op落在某个取值区间内，则返回该取值区间的编号。

width_bucket(op dp, b1 dp, b2 dp, count int)

Int

函数功能同上

2.2.数学函数实例

例子

结果

abs(-17.4)

17.4

cbrt(27.0)

3

ceil(-42.8)

-42

ceiling(-95.3)

-95

degrees(0.5)

28.6478897565412

exp(1.0)

2.71828182845905

floor(-42.8)

-43

ln(2.0)

0.693147180559945

log(100.0)

2

log(2.0, 64.0)

6.0000000000

mod(9,4)

1

pi()

3.14159265358979

power(9.0, 3.0)

729

power(9.0, 3.0)

729

radians(45.0)

0.785398163397448

random()

每次调用的结果是随机的，例如0.453876388259232

(1)round(42.4)

(2)round(42.8)

（1）42

（2）43

(1)round(42.4382, 2)

(2)round(42.4322,2)

(1)42.44

(2)42.43

setseed(0.54823)

1177314959

sign(-8.4)

-1

sqrt(2.0)

1.4142135623731

trunc(42.8)

42

(1)trunc(42.4382, 2)

(2)trunc(42,2)

(1)42.43

(2)42.00

(1)width_bucket(0.7,1,4,2);

 

(2)width_bucket(10,1,4,2);

 

(3)width_bucket(10,4,1,2);

 

(3)width_bucket(3,4,1,2);

(1)0

 

(2)3

 

(3)0

 

(4)1

width_bucket(0.35, 0.024, 10.06, 5)

1

3.1字符串函数和运算符

     函数或运算符

返回值类型

描述

String || string

text

连接两个字符串

String || non-string   或

 non-string || string

text

连接一个字符串和另一个非字符串类型的值

bit_length(string)

int

字符串包含的二进制位的个数

char_length(string) 或character_length(string)

int

字符串包含的字符的个数

lower(string)

text

将字符串转换成小写的格式

octet_length(string)

int

字符串包含的字节的个数

overlay(string placing string from int [for int])

text

替换字符串中的子串

position(substring in string)

int

查找子串在字符串中出现的位置

substring(string [from int] [for int])

text

从字符串中找出指定的子串。from int表示子串开始的位置，默认从1开始，例如from 2表示子串从string的第二个字符开始。for int表示子串的长度，默认取string从子串开始位置到string的末尾的所有子串，例如for 3表示子串的长度是3。

substring(string from pattern)

text

从字符串中找出匹配POSIX正则表达式的子串，参见第7.7.3节获取模式匹配的详细信息。

substring(string from pattern for escape)

text

从字符串中找出匹配正则表达式的子串，参见第7.7.3节获取模式匹配的详细信息。

trim([leading | trailing | both] [characters] from string)

text

从字符串string的开始、末尾或者开始和末尾删除只包含指定的字符串characters 中的字符的最长的字符串。

如果没有指定参数characters，则它的值默认是空格。

leading表示只删除字符串头部匹配的子串。

trailing 表示只删除字符串尾部匹配的子串。

both表示同时删除字符串头部和尾部匹配的子串。

upper(string)

text

将字符串转换成大写的格式

3.2字符串函数和运算符实例

例子

结果

'Post' || 'greSQL'

PostgreSQL

'Value: ' || 42

Value: 42

bit_length('jose')

32

char_length('jose')

4

lower('TOM')

tom

octet_length('jose')

4

Overlay('Txxxxas' placing 'hom' from 2 for 4)

Thomas

position('om' in 'Thomas')

3

(1)substring('Thomas' from 2 for 3)

(2)substring('Thomas' from 1 for 1)

(3)substring('Thomas' from 2)

(1)hom

(2)T

(3)homas

substring('Thomas' from '...$')

mas

substring('Thomas' from '%#"o_a#"_' for '#')

oma

(1)trim(both 'x' from 'xTomxx')

 

(2)trim(both 'xf' from 'xTomxxf');

 

(3)trim(both 'gxf' from 'xgTxTomxxf');

 

(4)trim(both  from '  xgTxTomxxf  ');

 

(5)trim(leading 'xf' from 'xTomxxf')

(1)Tom

(2)Tom

(3)TxTom

(4)xgTxTomxxf

(5)Tomxxf

upper('tom')

TOM

4.1其它字符串函数

函数

返回值类型

描述

ascii(string)

int

参数的第一个字符的ASCII编码。对于UTF8类型的字符串，返回它的第一个字符的UTF-8编码。对于其它的多字节编码类型的字符串，参数的第一个字符必须是ASCII类型的字符。

 

btrim(string text [, characterstext])

text

从字符串string的开始和末尾删除只包含指定的字符串characters 中的字符的最长的字符串。如果没有指定参数characters，则它的值默认是空格。

chr(int)

text

返回指定的编码值对应的字符。参数的值不能是0。参数必须是合法的ASCII编码值或UTF8编码值。

convert(string bytea, src_encoding name, dest_encoding name)

bytea

将用 src_encoding编码的字符串转换成用dest_encoding编码的字符串。数据库中必须存在src_encoding到dest_encoding的编码转换函数。表7-10列出了数据库中内置的合法的编码转换组合。

如果数据库中不存在 src_encoding到est_encoding的编码转换函数，可以使用命令 CREATE CONVERSION创建一个。

convert_from(string bytea, src_encoding name)

text

将用 src_encoding编码的字符串转换成用数据库当前的编码类型编码的字符串。

convert_to(string text, dest_encoding name)

bytea

将字符串转换成以dest_encoding编码的格式。

decode(string text, type text)

bytea

从指定的格式的字符串中解码出二进制字符串。格式包括base64、hex和escape，详细信息参考下面的encode函数。

encode(data bytea, type text)

text

将二进制字符串转换成指定的格式字符串。一共有三种格式：base64、hex和escape。关于base64请参考RFC2045，hex是十六进制格式。escape只是用/000来表示字节0，用两个反斜杠来表示一个反斜杠。

initcap(string)

text

首先将字符串用非字母和数字字符分割成多个子串，然后将每个子串的第一个字符大写，剩下的字符都小写。

 

length(string)

int

返回字符串中字符的个数

length(string bytea, encoding name )

int

返回字符串 bytea中的字符的个数，bytea的编码类型必须是encoding指定的。

 

lpad(string text, length int [, fill text])

text

 

用指定的字符串fill将字符串string填充成长度为 length的字符串。如果string的长度已经超过length，则将string截断成长度为length的字符串。

如果没有指定fill的值，则fill的值默认是空格。填充的字符串放在string的头部。

ltrim(string text [, characterstext])

text

从字符串string的头部删除只包含指定的字符串characters 中的字符的最长的字符串。

如果没有指定参数characters，则它的值默认是空格。

md5(string)

text

计算字符串string的MD5哈希值。结果用十六进制的形式表示。

pg_client_encoding()

name

返回客户端的当前字符编码类型名称。

quote_ident(string text)

text

返回字符串string作为合法的SQL标识符的表示形式。

quote_literal(string text)

text

将字符串string转换成一个合法的SQL语句字符串常量的形式。

quote_literal(value anyelement)

text

将 value转换成字符串常量,value必须是一个数值。

regexp_matches(string text, pattern text [, flags text])

setof text[]

返回所有匹配指定的POSIX正则表达式的子串。参见第7.7.3节。

regexp_replace(string text, pattern text, replacement text [, flags text])

text

用字符串 replacement替换所有匹配指定的POSIX正则表达式的子串。参见第7.7.3节。

regexp_split_to_array(string text, pattern text [, flags text ])

text[]

使用POSIX正则表达式作为分割符来分割字符串。参见第7.7.3节。

regexp_split_to_table(string text, pattern text [, flags text])

setof text

使用POSIX正则表达式作为分割符来分割字符串。参见第7.7.3节。

repeat(string text, number int)

text

将字符串string重复指定的次数。

replace(string text, from text, to text)

text

将字符串string中的所有子串from用子串to代替。

rpad(string text, length int [, fill text])

text

用指定的字符串fill将字符串string填充成长度为 length的字符串。如果string的长度已经超过length，则将string截断成长度为length的字符串。

如果没有指定fill的值，则fill的值默认是空格。填充的字符串放在string的尾部。

rtrim(string text [, characterstext])

text

从字符串string的尾部删除只包含指定的字符串characters 中的字符的最长的字符串。

如果没有指定参characters，则它的值默认是空格。

split_part(string text, delimiter text, field int)

text

将字符串string用分割符delimiter分成多个域后，返回field指定的域（域的编号从1开始）。delimiter可以是一个字符串。

strpos(string, substring)

int

返回substring在string中的位置。

substr(string, from [, count] int)

text

从字符串string中取出指定的子串，如果没有指定参数count，则取出从from到字符串结尾这部分子串。与函数substring(string from from for count) 的功能相同。

to_ascii(string text [, encodingtext])

text

将用 ASCII表示的字符串转换成其它编码类型的字符串(只支持LATIN1, LATIN2, LATIN9和WIN1250)。

to_hex(number int or bigint)

text

将数字转换成十六进制的表示形式。

translate(string text, from text, to text)

text

如果字符串string中的某个字符匹配字符串from的某个字符，则string中的这个字符将用字符串to中和from中的匹配字符对应的字符代替。字符串to和from的长度应该相等，如果不相等，参见例子中的处理方式。

4.2其它字符串函数实例

例子

结果

(1)ascii('x')

(2)ascii('yz')

(3)ascii('我是')

(1)120

(2)121

(3)25105

(1)btrim('xyxtrimyyx', 'xy')

(2)btrim('xgTomxxf','gxf');

(1)trim

(2)Tom

(1)chr(65)

(2)chr(25105)

(3)chr(25104)

(1)A

(2)我

(3)成

convert('text_in_utf8', 'UTF8', 'LATIN1')

用ISO 8859-1格式表示的字符串text_in_utf8

convert_from('text_in_utf8', 'UTF8')

用数据库当前的编码类型表示的字符串text_in_utf8

convert_to('some text', 'UTF8')

用UTF8编码的字符串

(1)decode(‘MTIzAAE=', 'base64')

(2)decode('3132330001','hex')

(1)123/000/001

(2)123/000/001

(1)encode(E'123//000//001', 'base64')

(2)encode(E'123//000//001', 'hex')

(1)MTIzAAE=

(2)3132330001

(1)initcap('hi THOMAS')

(2)initcap('hh+jj');

(1)Hi Thomas

(2)Hh+Jj

(1)length('jose')

(2)length('我是谁')

(1)4

(2)3

length('jose', 'UTF8')

4

(1)lpad('hi', 5, 'xy')

(2)lpad('gggggg',4,'h');

(3)lpad('gg',4,'h');

(4)lpad('gg',4);

(1)xyxhi

(2)gggg

(3)hhgg

(4)  gg，注意gg前面是两个空格。

ltrim('zzzytrim', 'xyz')

trim

md5('abc')

900150983cd24fb0 d6963f7d28e17f72

pg_client_encoding()

 GB18030

(1)quote_ident('Foo bar')

(2)quote_ident('abc')

(3)quote_ident('abc''');

(1)"Foo bar"

(2)abc

(3) "abc'"

(1)quote_literal('O/'Reilly')

(2)quote_literal('abc//');

(1)'O''Reilly'

(2) E'abc//'

quote_literal(42.5)

'42.5'

regexp_matches('foobarbequebaz', '(bar)(beque)')

{bar,beque}

regexp_replace('Thomas', '.[mN]a.', 'M')

ThM

regexp_split_to_array('hello world', E'//s+')

{hello,world}

regexp_split_to_table('hello world', E'//s+')

hello

world

(2 rows)

repeat('Pg', 4)

PgPgPgPg

replace('abcdefabcdef', 'cd', 'XX')

abXXefabXXef

rpad('hi', 5, 'xy')

hixyx

rtrim('trimxxxx', 'x')

trim

(1)split_part('abc~@~def~@~ghi', '~@~', 2)

(2)split_part('abc$de$f','$',3);

(1)def

(2)f

(1)strpos('high', 'ig')

(2)strpos('我是','是')

(1)2

(2)2

substr('alphabet', 3, 2)

ph

to_ascii('Karel')

Karel

to_hex(2147483647)

7fffffff

(1)translate('12345', '14', 'ax')

(2)translate('abcdef','abc','f')

(3)translate('abcdef','abc','fg')

(1)a23x5

(2)fdef

(3)fgdef