PostgreSQL基本结构——函数

1.数学操作符

操作符

描述

例子

结果

/

除(整数/整数结果只保留整数位)

31/31

1

%

模

6 % 4

1

^

幂

2.0 ^ 3.1

8

|/

平方根

|/ 25.1

5

||/

立方根

||/ 27.1

3

!

阶乘

6 !

120

!!

阶乘

!! 6

120

@

绝对值

&

按位AND

92 & 15

11

|

按位OR

33 | 3

35

#

按位XOR

18 # 5

20

~

按位NOT

~2

-2

<< 

按位左移

2 << 4

16

>> 

按位右移

9 >> 2

2

2.位串操作符

||

连接

B'10001' || B'012'

10001011

&

按位AND

B'10001' & B'01102'

1

|

按位OR

B'10001' | B'01102'

11101

#

按位XOR

B'10001' # B'01102'

11100

~

按位NOT

~ B'10002'

1110

<< 

按位左移

B'10001' << 4

1000

>> 

按位右移

B'10001' >> 3

100

3.数学函数

abs(x)

绝对值

abs(-17.4)

17.4

cbrt(double)

立方根

cbrt(27.0)

3

ceil(double/numeric)

不小于参数的最小的整数

ceil(-42.8)

-42

degrees(double) 

把弧度转为角度

degrees(0.5)

28.64788976

exp(double/numeric)

自然指数

exp(1.0)

2.718281828

floor(double/numeric)

不大于参数的最大整数

floor(-42.8)

-43

ln(double/numeric)

自然对数

ln(2.0)

0.693147181

log(double/numeric)

10为底的对数

log(100.0)

2

log(b numeric,x numeric)

numeric指定底数的对数

log(2.0, 64.0)

6

mod(y, x)

取余数

mod(9,4)

1

pi() 

"π"常量

pi() 

3.141592654

power(a double, b double)

求a的b次幂

power(9.0, 3.0)

729

power(a numeric, b numeric)

求a的b次幂

power(9.0, 3.0)

729

radians(double)

把角度转为弧度

radians(45.0)

0.785398163

random()

0.0到1.0之间的随机数值

random()

round(double/numeric)

圆整为最接近的整数

round(42.4)

42

round(v numeric, s int)

圆整为s位小数数字

round(42.438,2)

42.44

sign(double/numeric)

参数的符号(-1,0,+1) 

sign(-8.4)

-1

sqrt(double/numeric)

平方根

sqrt(2.0)

1.414213562

trunc(double/numeric)

截断(向零靠近)

trunc(42.8)

42

trunc(v numeric, s int)

截断为s小数位置的数字

trunc(42.438,2)

42.43

acos(x)

反余弦

asin(x)

反正弦

atan(x)

反正切

atan2(y, x)

返回弧度角，X轴与（x，y）组成的角

cos(x)

余弦

cot(x)

余切

sin(x)

正弦

tan(x)

正切

4.字符串函数

string || string

字串连接

'Post' || 'greSQL'

PostgreSQL

[char_/octet_]length（串）

求字符串里字符的个数，三种格式

array_agg（表达式）

把一列换成一行

ascii(text)

参数第一个字符的ASCII码

ascii('x')

120

ASCII（字符串）

返回最左边字符的ASCII码

bit_length(string)

字串里二进制位的个数

bit_length('jose')

32

btrim(string text [, characters text])

从string开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串

btrim('xyxtrimyyx','xy')

trim

char_length(string)

字串中的字符个数

char_length('jose')

4

chr(int)

给出ASCII码的字符

chr(65)

A

concat（串1，串2…）

合并字符串

convert(string text, [src_encoding name,] dest_encoding name)

把字串转换为dest_encoding

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

以ISO 8859-1编码表示的text_in_utf8

convert(string using conversion_name)

使用指定的转换名字改变编码。

convert('PostgreSQL' using iso_8859_1_to_utf8)

'PostgreSQL'

format

格式化

initcap(text)

把每个单词的第一个子母转为大写，其它的保留小写。单词是一系列字母数字组成的字符，用非字母数字分隔。

initcap('hi thomas')

Hi Thomas

left/ right（串，整数）

取字符串的左边/右边整数位

length(string text)

string中字符的数目

length('jose')

4

lower(string)

把字串转化为小写

lower('TOM')

tom

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

通过填充字符fill(缺省时为空白)，把string填充为长度length。如果string已经比length长则将其截断(在右边)。

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

xyxhi

ltrim(string text [, characters text])

从字串string的开头删除只包含characters(缺省是一个空白)的最长的字串。

ltrim('zzzytrim','xyz')

trim

md5(string text)

计算给出string的MD5散列，以十六进制返回结果。

md5('abc')

octet_length(string)

字串中的字节数

octet_length('jose')

4

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

替换子字串

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

Thomas

position(substring in string)

指定的子字串的位置

position('om' in 'Thomas')

3

repeat(string text, number int)

重复string number次。

repeat('Pg', 4)

PgPgPgPg

replace(string text, from text, to text)

把字串string里出现地所有子字串from替换成子字串to。

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

abXXefabXXef

reverse（串）

把字符串转置

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

通过填充字符fill(缺省时为空白)，把string填充为长度length。如果string已经比length长则将其截断。

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

hixyx

rtrim(string text [, character text])

从字串string的结尾删除只包含character(缺省是个空白)的最长的字

rtrim('trimxxxx','x')

trim

split_part(string text, delimiter text, field int)

根据delimiter分隔string返回生成的第field个子字串(1 Base)。

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

def

strpos(string, substring)

声明的子字串的位置。

strpos('high','ig')

2

substr(string, from [, count])

抽取子字串。

substr('alphabet', 3, 2)

ph

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

抽取子字串

substring('Thomas' from 2 for 3)

hom

substring(string from pattern for escape)

抽取匹配SQL正则表达式的子字串

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

oma

substring(string from pattern)

抽取匹配 POSIX 正则表达式的子字串

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

mas

to_ascii(text [, encoding])

把text从其它编码转换为ASCII。

to_ascii('Karel')

Karel

to_hex(number int/bigint)

把number转换成其对应地十六进制表现形式。

to_hex(9223372036854775807)

7fffffffffffffff

translate(string text, from text, to text)

把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。

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

a23x5

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

从字串string的开头/结尾/两边/ 删除只包含characters(缺省是一个空白)的最长的字串

trim(both 'x' from 'xTomxx')

Tom

upper(string)

把字串转化为大写。

upper('tom')

TOM

5.数据类型格式化函数

to_char(timestamp, text)

把时间戳转换成字串

to_char(current_timestamp, 'HH12:MI:SS')

to_char(interval, text)

把时间间隔转为字串

to_char(interval '15h 2m 12s', 'HH24:MI:SS')

to_char(int, text)

把整数转换成字串

to_char(125, '999')

to_char(double precision, text)

把实数/双精度数转换成字串

to_char(125.8::real, '999D9')

to_char(numeric, text)

把numeric转换成字串

to_char(-125.8, '999D99S')

to_date(text, text)

把字串转换成日期

to_date('05 Dec 2000', 'DD Mon YYYY')

to_timestamp(text, text)

把字串转换成时间戳

to_timestamp('05 Dec 2000', 'DD Mon YYYY')

to_timestamp(double)

把UNIX纪元转换成时间戳

to_timestamp(200120400)

to_number(text, text)

把字串转换成numeric

to_number('12,454.8-', '99G999D9S')

6.日期/时间格式化

一天的小时数(01-12)

一天的小时数(01-12)

一天的小时数(00-23)

分钟(00-59)

秒(00-59)

毫秒(000-999)

微秒(000000-999999)

正午标识(大写)

带逗号的年(4和更多位)

年(4和更多位)

年的后三位

年的后两位

年的最后一位

全长大写月份名(空白填充为9字符)

全长混合大小写月份名(空白填充为9字符)

全长小写月份名(空白填充为9字符)

大写缩写月份名(3字符)

缩写混合大小写月份名(3字符)

小写缩写月份名(3字符)

月份号(01-12)

全长大写日期名(空白填充为9字符)

全长混合大小写日期名(空白填充为9字符)

全长小写日期名(空白填充为9字符)

缩写大写日期名(3字符)

缩写混合大小写日期名(3字符)

缩写小写日期名(3字符)

一年里的日子(001-366)

一个月里的日子(01-31)

一周里的日子(1-7；周日是1)

一个月里的周数(1-5)(第一周从该月第一天开始)

一年里的周数(1-53)(第一周从该年的第一天开始)

7.数值格式化

带有指定数值位数的值

带前导零的值

小数点

分组(千)分隔符

尖括号内负值

带符号的数值

货币符号

小数点

分组分隔符

在指明的位置的负号(如果数字 < 1)

在指明的位置的正号(如果数字 > 1)

在指明的位置的正/负号

8.日期/时间操作符

date '2001-09-28' + integer '8'

date '2001-10-06'

date '2001-09-28' + interval '2 hour'

timestamp '2001-09-28 01:01'

date '2001-09-28' + time '03:01'

timestamp '2001-09-28 03:01'

interval '1 day' + interval '2 hour'

interval '1 day 01:01'

timestamp '2001-09-28 01:00' + interval '24 hours'

timestamp '2001-09-29 00:01'

time '01:00' + interval '4 hours'

time '04:01'

- interval '24 hours'

interval '-23:01'

date '2001-10-01' - date '2001-09-29'

integer '4'

date '2001-10-01' - integer '8'

date '2001-09-25'

date '2001-09-28' - interval '2 hour'

timestamp '2001-09-27 23:01'

time '05:00' - time '03:01'

interval '02:01'

time '05:00' - interval '3 hours'

time '03:01'

timestamp '2001-09-28 23:00' - interval '24 hours'

timestamp '2001-09-28 00:01'

interval '1 day' - interval '2 hour'

interval '23:01'

timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:01'

interval '1 day 15:01'

interval '1 hour' * double precision '3.6'

interval '03:31'

interval '1 hour' / double precision '1.6'

interval '00:41'

9.日期/时间函数

age(timestamp, timestamp)

减去参数，生成一个使用年、月的"符号化"的结果

age('2001-04-10', timestamp '1957-06-13')

43 years 9 mons 27 days

age(timestamp)

从current_date减去得到的数值

age(timestamp '1957-06-13')

43 years 8 mons 3 days

current_date

今天的日期

current_time

现在的时间

current_timestamp

日期和时间

date_part(text, timestamp)

获取子域(等效于extract)

date_part('hour', timestamp '2001-02-16 20:38:40')

20

date_part(text, interval)

获取子域(等效于extract)

date_part('month', interval '2 years 3 months')

3

date_trunc(text, timestamp)

截断成指定的精度

date_trunc('hour', timestamp '2001-02-16 20:38:40')

2001-02-16 20:00:00+00

extract(field from timestamp)

获取子域

extract(hour from timestamp '2001-02-16 20:38:40')

20

extract(field from interval)

获取子域

extract(month from interval '2 years 3 months')

3

localtime

今日的时间

localtimestamp

日期和时间

now()

当前的日期和时间(等效于 current_timestamp)

timeofday()

当前日期和时间

10.EXTRACT，date_part函数支持的field

CENTURY

世纪

EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');

20

DAY

(月分)里的日期域(1-31)

EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40');

16

DECADE

年份域除以10

EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40');

200

DOW

每周的星期号(0-6；星期天是0) (仅用于timestamp)

EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');

5

DOY

一年的第几天(1 -365/366) (仅用于 timestamp)

EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40');

47

HOUR

小时域(0-23)

EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40');

20

MICROSECONDS

秒域，包括小数部分，乘以 1,000,000。

EXTRACT(MICROSECONDS from TIME '17:12:28.5');

28500000

MILLENNIUM

千年

EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40');

3

MILLISECONDS

秒域，包括小数部分，乘以 1000。

EXTRACT(MILLISECONDS from TIME '17:12:28.5');

28500

MINUTE

分钟域(0-59)

EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40');

38

MONTH

对于timestamp数值，它是一年里的月份数(1-12)；对于interval数值，它是月的数目，然后对12取模(0-11)

EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40');

2

QUARTER

该天所在的该年的季度(1-4)(仅用于 timestamp)

EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');

1

SECOND

秒域，包括小数部分(0-59[1])

EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40');

40

WEEK

该天在所在的年份里是第几周。

EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40');

7

YEAR

年份域

EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40');

2001

11.数组操作符和函数

=

等于

ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,4]

t

<> 

不等于

ARRAY[1,2,3] <> ARRAY[1,2,5]

t

< 

小于

ARRAY[1,2,3] < ARRAY[1,2,5]

t

> 

大于

ARRAY[1,4,3] > ARRAY[1,2,5]

t

<=

小于或等于

ARRAY[1,2,3] <= ARRAY[1,2,4]

t

>=

大于或等于

ARRAY[1,4,3] >= ARRAY[1,4,4]

t

||

数组与数组连接

ARRAY[1,2,3] || ARRAY[4,5,7]

{1,2,3,4,5,7}

||

数组与数组连接

ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,10]]

{{1,2,3},{4,5,6},{7,8,10}}

||

元素与数组连接

4 || ARRAY[4,5,6]

{3,4,5,7}

||

元素与数组连接

ARRAY[4,5,6] || 8

{4,5,6,8}

array_cat(anyarray, anyarray)

连接两个数组

array_cat(ARRAY[1,2,3], ARRAY[4,5])

{1,2,3,4,5}

array_append(anyarray, anyelement)

向一个数组末尾附加一个元素

array_append(ARRAY[1,2], 3)

{1,2,3}

array_prepend(anyelement, anyarray)

向一个数组开头附加一个元素

array_prepend(1, ARRAY[2,3])

{1,2,3}

array_dims(anyarray)

返回一个数组维数的文本表示

array_dims(ARRAY[[1,2,3], [4,5,6]])

[1:2][1:3]

array_lower(anyarray, int)

返回指定的数组维数的下界

array_lower(array_prepend(0, ARRAY[1,2,3]), 1)

0

array_upper(anyarray, int)

返回指定数组维数的上界

array_upper(ARRAY[1,2,3,4], 1)

4

array_to_string(anyarray, text)

使用提供的分隔符连接数组元素

array_to_string(ARRAY[1, 2, 3], '~^~')

1~^~2~^~3

string_to_array(text, text)

使用指定的分隔符把字串拆分成数组元素

string_to_array('xx~^~yy~^~zz', '~^~')

{xx,yy,zz}

12.系统信息函数

current_database()

当前数据库的名字

current_schema()

当前模式的名字

current_schemas(boolean)

在搜索路径中的模式名字

current_user

目前执行环境下的用户名

inet_client_addr()

连接的远端地址

inet_client_port()

连接的远端端口

inet_server_addr()

连接的本地地址

inet_server_port()

连接的本地端口

session_user

会话用户名

pg_postmaster_start_time()

postmaster启动的时间

user

current_user

version()

PostgreSQL版本信息

has_table_privilege(user,table,privilege)

用户是否有访问表的权限

SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER

允许用户在程序里查询对象访问权限的函数

has_table_privilege(table,privilege)

当前用户是否有访问表的权限

SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER

has_database_privilege(user,database,privilege)

用户是否有访问数据库的权限

CREATE/TEMPORARY

has_database_privilege(database,privilege)

当前用户是否有访问数据库的权限

CREATE/TEMPORARY

has_function_privilege(user,function,privilege)

用户是否有访问函数的权限

EXECUTE

has_function_privilege(function,privilege)

当前用户是否有访问函数的权限

EXECUTE

has_language_privilege(user,language,privilege)

用户是否有访问语言的权限

USAGE

has_language_privilege(language,privilege)

当前用户是否有访问语言的权限

USAGE

has_schema_privilege(user,schema,privilege)

用户是否有访问模式的权限

CREAT/USAGE

has_schema_privilege(schema,privilege)

当前用户是否有访问模式的权限

CREAT/USAGE

has_tablespace_privilege(user,tablespace,privilege)

用户是否有访问表空间的权限

CREATE

has_tablespace_privilege(tablespace,privilege)

当前用户是否有访问表空间的权限

CREATE

pg_table_is_visible(table_oid)

该表/视图是否在搜索路径中可见

regclass

模式可视性查询函数

pg_type_is_visible(type_oid)

该类/视图型是否在搜索路径中可见

regtype

pg_function_is_visible(function_oid)

该函数是否在搜索路径中可见

regprocedure

pg_operator_is_visible(operator_oid)

该操作符是否在搜索路径中可见

regoperator

pg_opclass_is_visible(opclass_oid)

该操作符表是否在搜索路径中可见

regclass

pg_conversion_is_visible(conversion_oid)

转换是否在搜索路径中可见

regoperator

format_type(type_oid,typemod)

获取一个数据类型的SQL名称

系统表信息函数

pg_get_viewdef(view_oid)

为视图获取CREATE VIEW命令

pg_get_viewdef(view_oid,pretty_bool)

为视图获取CREATE VIEW命令

pg_get_ruledef(rule_oid)

为规则获取CREATE RULE命令

pg_get_ruledef(rule_oid,pretty_bool)

为规则获取CREATE RULE命令

pg_get_indexdef(index_oid)

为索引获取CREATE INDEX命令

pg_get_indexdef(index_oid,column_no,pretty_bool)

为索引获取CREATE INDEX命令，如果column_no不为零，则是只获取一个索引字段的定义

pg_get_triggerdef(trigger_oid)

为触发器获取CREATE [CONSTRAINT] TRIGGER

pg_get_constraintdef(constraint_oid)

获取一个约束的定义

pg_get_constraintdef(constraint_oid,pretty_bool)

获取一个约束的定义

pg_get_expr(expr_text,relation_oid)

反编译一个表达式的内部形式，假设其中的任何Vars都引用第二个参数指出的关系

pg_get_expr(expr_text,relation_oid, pretty_bool)

反编译一个表达式的内部形式，假设其中的任何Vars都引用第二个参数指出的关系

pg_get_userbyid(roleid)

获取给出的ID的角色名

pg_get_serial_sequence(table_name,column_name)

获取一个serial或者bigserial字段使用的序列名字

pg_tablespace_databases(tablespace_oid)

获取在指定表空间(OID表示)中拥有对象的一套数据库的OID的集合

13.系统管理函数

current_setting(setting_name)

当前设置的值

查询以及修改运行时配置参数的函数

set_config(setting_name,new_value,is_local)

设置参数并返回新值

pg_tablespace_size(oid)

指定OID代表的表空间使用的磁盘空间

数据库对象尺寸函数

pg_tablespace_size(name)

指定名字的表空间使用的磁盘空间

pg_database_size(oid)

指定OID代表的数据库使用的磁盘空间

pg_database_size(name)

指定名称的数据库使用的磁盘空间

pg_relation_size(oid)

指定OID代表的表或者索引所使用的磁盘空间

pg_relation_size(text)

指定名称的表或者索引使用的磁盘空间。这个名字可以用模式名修饰

pg_total_relation_size(oid)

指定OID代表的表使用的磁盘空间，包括索引和压缩数据

pg_total_relation_size(text)

指定名字的表所使用的全部磁盘空间，包括索引和压缩数据。表名字可以用模式名修饰。

pg_size_pretty(bigint)

把字节计算的尺寸转换成一个人类易读的尺寸单位

pg_relation_filenode(relationregclass)

获取指定对象的文件节点编号(通常为对象的oid值)。

数据库对象位置函数

pg_relation_filepath(relationregclass)

获取指定对象的完整路径名。

14.系列号操作函数

nextval(regclass)

递增序列对象到它的下一个数值并且返回该值。这个动作是自动完成的。即使多个会话并发运行nextval，每个进程也会安全地收到一个唯一的序列值。

currval(regclass)

在当前会话中返回最近一次nextval抓到的该序列的数值。(如果在本会话中从未在该序列上调用过 nextval，那么会报告一个错误。)请注意因为此函数返回一个会话范围的数值，而且也能给出一个可预计的结果，因此可以用于判断其它会话是否执行过nextval。

lastval()

返回当前会话里最近一次nextval返回的数值。这个函数等效于currval，只是它不用序列名为参数，它抓取当前会话里面最近一次nextval使用的序列。如果当前会话还没有调用过nextval，那么调用lastval将会报错。

setval(regclass, bigint)

重置序列对象的计数器数值。设置序列的last_value字段为指定数值并且将其is_called字段设置为true，表示下一次nextval将在返回数值之前递增该序列。

setval(regclass, bigint, boolean)

重置序列对象的计数器数值。功能等同于上面的setval函数，只是is_called可以设置为true或false。如果将其设置为false，那么下一次nextval将返回该数值，随后的nextval才开始递增该序列。