[转] PostgreSQL学习手册(函数和操作符)

时间:2023-03-08 16:48:47

一、逻辑操作符:

常用的逻辑操作符有:ANDORNOT。其语义与其它编程语言中的逻辑操作符完全相同。

二、比较操作符:

下面是PostgreSQL中提供的比较操作符列表:

操作符 描述
< 小于
> 大于
<= 小于或等于
>= 大于或等于
= 等于
!= 不等于

比较操作符可以用于所有可以比较的数据类型。所有比较操作符都是双目操作符,且返回boolean类型。除了比较操作符以外,我们还可以使用BETWEEN语句,如:
    a BETWEEN x AND y 等效于 a >= x AND a <= y    
    a NOT BETWEEN x AND y 等效于 a < x OR a > y

三、 数学函数和操作符:

下面是PostgreSQL中提供的数学操作符列表:

操作符 描述 例子 结果
+ 2 + 3
- 2 - 3 -1
* 2 * 3
/ 4 / 2
% 5 % 4
^ 2.0 ^ 3.0
|/ 平方根 |/ 25.0
||/ 立方根 ||/ 27.0
! 阶乘 5 !
!! 阶乘 !! 5
@ 绝对值 @ -5.0
& 按位AND 91 & 15
| 按位OR 32 | 3
# 按位XOR 17 # 5
~ 按位NOT ~1 -2
<< 按位左移 1 << 4
>> 按位右移 8 >> 2

按位操作符只能用于整数类型,而其它的操作符可以用于全部数值数据类型。按位操作符还可以用于位串类型bit和bit varying,

下面是PostgreSQL中提供的数学函数列表,需要说明的是,这些函数中有许多都存在多种形式,区别只是参数类型不同。除非特别指明,任何特定形式的函数都返回和它的参数相同的数据类型。

函数 返回类型
描述 例子
结果
abs(x)   绝对值 abs(-17.4) 17.4
cbrt(double)   立方根 cbrt(27.0)
ceil(double/numeric)   不小于参数的最小的整数 ceil(-42.8) -42
degrees(double)   把弧度转为角度 degrees(0.5) 28.6478897565412
exp(double/numeric)   自然指数 exp(1.0) 2.71828182845905
floor(double/numeric)   不大于参数的最大整数 floor(-42.8) -43
ln(double/numeric)   自然对数 ln(2.0) 0.693147180559945
log(double/numeric)   10为底的对数 log(100.0)
log(b numeric,x numeric)   numeric指定底数的对数 log(2.0, 64.0) 6.0000000000
mod(y, x)   取余数 mod(9,4)
pi() double "π"常量 pi() 3.14159265358979
power(a double, b double) double 求a的b次幂 power(9.0, 3.0)
power(a numeric, b numeric) numeric 求a的b次幂 power(9.0, 3.0)
radians(double) double 把角度转为弧度 radians(45.0) 0.785398163397448
random() double 0.0到1.0之间的随机数值 random()  
round(double/numeric)   圆整为最接近的整数 round(42.4)
round(v numeric, s int) numeric 圆整为s位小数数字 round(42.438,2) 42.44
sign(double/numeric)   参数的符号(-1,0,+1) sign(-8.4) -1
sqrt(double/numeric)   平方根 sqrt(2.0) 1.4142135623731
trunc(double/numeric)   截断(向零靠近) trunc(42.8)
trunc(v numeric, s int) numeric 截断为s小数位置的数字 trunc(42.438,2) 42.43

    三角函数列表:

函数 描述
acos(x) 反余弦
asin(x) 反正弦
atan(x) 反正切
atan2(x, y) 正切 y/x 的反函数
cos(x) 余弦
cot(x) 余切
sin(x) 正弦
tan(x) 正切

四、字符串函数和操作符:

下面是PostgreSQL中提供的字符串操作符列表:

函数 返回类型 描述 例子 结果
string || string text 字串连接 'Post' || 'greSQL' PostgreSQL
bit_length(string) int 字串里二进制位的个数 bit_length('jose')
char_length(string) int 字串中的字符个数 char_length('jose')
convert(string using conversion_name) text 使用指定的转换名字改变编码。 convert('PostgreSQL' using iso_8859_1_to_utf8) 'PostgreSQL'
lower(string) text 把字串转化为小写 lower('TOM') tom
octet_length(string) int 字串中的字节数 octet_length('jose')
overlay(string placing string from int [for int]) text 替换子字串 overlay('Txxxxas' placing 'hom' from 2 for 4) Thomas
position(substring in string) int 指定的子字串的位置 position('om' in 'Thomas')
substring(string [from int] [for int]) text 抽取子字串 substring('Thomas' from 2 for 3) hom
substring(string from pattern) text 抽取匹配 POSIX 正则表达式的子字串 substring('Thomas' from '...$') mas
substring(string from pattern for escape) text 抽取匹配SQL正则表达式的子字串 substring('Thomas' from '%#"o_a#"_' for '#') oma
trim([leading | trailing | both] [characters] from string) text 从字串string的开头/结尾/两边/ 删除只包含characters(缺省是一个空白)的最长的字串 trim(both 'x' from 'xTomxx') Tom
upper(string) text 把字串转化为大写。 upper('tom') TOM
ascii(text) int 参数第一个字符的ASCII码 ascii('x')
btrim(string text [, characters text]) text 从string开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串 btrim('xyxtrimyyx','xy') trim
chr(int) text 给出ASCII码的字符 chr(65) A
convert(string text, [src_encoding name,] dest_encoding name) text 把字串转换为dest_encoding convert( 'text_in_utf8', 'UTF8', 'LATIN1') 以ISO 8859-1编码表示的text_in_utf8
initcap(text) text 把每个单词的第一个子母转为大写,其它的保留小写。单词是一系列字母数字组成的字符,用非字母数字分隔。 initcap('hi thomas') Hi Thomas
length(string text) int string中字符的数目 length('jose')
lpad(string text, length int [, fill text]) text 通过填充字符fill(缺省时为空白),把string填充为长度length。 如果string已经比length长则将其截断(在右边)。 lpad('hi', 5, 'xy') xyxhi
ltrim(string text [, characters text]) text 从字串string的开头删除只包含characters(缺省是一个空白)的最长的字串。 ltrim('zzzytrim','xyz') trim
md5(string text) text 计算给出string的MD5散列,以十六进制返回结果。 md5('abc')  
repeat(string text, number int) text 重复string number次。 repeat('Pg', 4) PgPgPgPg
replace(string text, from text, to text) text 把字串string里出现地所有子字串from替换成子字串to。 replace('abcdefabcdef', 'cd', 'XX') abXXefabXXef
rpad(string text, length int [, fill text]) text 通过填充字符fill(缺省时为空白),把string填充为长度length。如果string已经比length长则将其截断。 rpad('hi', 5, 'xy') hixyx
rtrim(string text [, character text]) text 从字串string的结尾删除只包含character(缺省是个空白)的最长的字 rtrim('trimxxxx','x') trim
split_part(string text, delimiter text, field int) text 根据delimiter分隔string返回生成的第field个子字串(1 Base)。 split_part('abc~@~def~@~ghi', '~@~', 2) def
strpos(string, substring) text 声明的子字串的位置。 strpos('high','ig')
substr(string, from [, count]) text 抽取子字串。 substr('alphabet', 3, 2) ph
to_ascii(text [, encoding]) text 把text从其它编码转换为ASCII。 to_ascii('Karel') Karel
to_hex(number int/bigint) text 把number转换成其对应地十六进制表现形式。 to_hex(9223372036854775807) 7fffffffffffffff
translate(string text, from text, to text) text 把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。 translate('12345', '14', 'ax') a23x5

五、位串函数和操作符:

对于类型bit和bit varying,除了常用的比较操作符之外,还可以使用以下列表中由PostgreSQL提供的位串函数和操作符,其中&、|和#的位串操作数必须等长。在移位的时候,保留原始的位串的的长度。

操作符 描述 例子 结果
|| 连接 B'10001' || B'011'
& 按位AND B'10001' & B'01101'
| 按位OR B'10001' | B'01101'
# 按位XOR B'10001' # B'01101'
~ 按位NOT ~ B'10001'
<< 按位左移 B'10001' << 3
>> 按位右移 B'10001' >> 2

除了以上列表中提及的操作符之外,位串还可以使用字符串函数:length, bit_length, octet_length, position, substring。此外,我们还可以在整数和bit之间来回转换,如:
    MyTest=# SELECT 44::bit(10);
        bit
    ------------
     0000101100
    (1 row)
    MyTest=# SELECT 44::bit(3);
     bit
    -----
     100
    (1 row)
    MyTest=# SELECT cast(-44 as bit(12));
         bit
    --------------
     111111010100
    (1 row)
    MyTest=# SELECT '1110'::bit(4)::integer;
     int4
    ------
       14
    (1 row)
    注意:如果只是转换为"bit",意思是转换成bit(1),因此只会转换成整数的最低位。

该博客中提供的所有信息均源自PostgreSQL官方文档,编写该篇博客的主要目的是便于今后的查阅,特此声明。

六、模式匹配:

PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式。
    1. LIKE:
    string LIKE pattern [ ESCAPE escape-character ]
    string NOT LIKE pattern [ ESCAPE escape-character ]

每个pattern定义一个字串的集合。如果该string包含在pattern代表的字串集合里,那么LIKE表达式返回真。和我们想象的一样,如果
LIKE返回真,那么NOT
LIKE表达式返回假,反之亦然。在pattern里的下划线(_)代表匹配任何单个字符,而一个百分号(%)匹配任何零或更多字符,如:
    'abc' LIKE 'abc'     true
    'abc' LIKE 'a%'     true
    'abc' LIKE '_b_'    true
    'abc' LIKE 'c'        false  
    要匹配文本的下划线或者百分号,而不是匹配其它字符,在pattern里相应的字符必须前导转义字符。缺省的转义字符是反斜杠,但是你可以用ESCAPE子句指定一个。要匹配转义字符本身,写两个转义字符。我们也可以通过写成ESCAPE ''的方式有效地关闭转义机制,此时,我们就不能关闭下划线和百分号的特殊含义了。
    关键字ILIKE可以用于替换LIKE,令该匹配就当前的区域设置是大小写无关的。这个特性不是SQL标准,是PostgreSQL的扩展。操作符~~等效于LIKE, 而~~*对应ILIKE。还有!~~!~~*操作符分别代表NOT LIKENOT ILIKE。所有这些操作符都是PostgreSQL特有的。

2. SIMILAR TO正则表达式:
    SIMILAR TO根据模式是否匹配给定的字符串而返回真或者假。
    string SIMILAR TO pattern [ESCAPE escape-character]
    string NOT SIMILAR TO pattern [ESCAPE escape-character]
    它和LIKE非常类似,支持LIKE的通配符('_''%')且保持其原意。除此之外,SIMILAR TO还支持一些自己独有的元字符,如:    
    1). | 标识选择(两个候选之一)。
    2). * 表示重复前面的项零次或更多次。
    3). + 表示重复前面的项一次或更多次。
    4). 可以使用圆括弧()把项组合成一个逻辑项。
    5). 一个方括弧表达式[...]声明一个字符表,就像POSIX正则表达式一样。
    见如下示例:
    'abc' SIMILAR TO 'abc'           true
    'abc' SIMILAR TO 'a'              false
    'abc' SIMILAR TO '%(b|d)%'  true
    'abc' SIMILAR TO '(b|c)%'     false
    带三个参数的substring,substring(string
from pattern for
escape-character),提供了一个从字串中抽取一个匹配SQL正则表达式模式的子字串的函数。和SIMILAR
TO一样,声明的模式必须匹配整个数据串,否则函数失效并返回NULL。为了标识在成功的时候应该返回的模式部分,模式必须出现后跟双引号(")的两个转
义字符。匹配这两个标记之间的模式的字串将被返回,如:
    MyTest=# SELECT substring('foobar' from '%#"o_b#"%' FOR '#'); --这里#是转义符,双引号内的模式是返回部分。
     substring
    -----------
     oob
    (1 row)
    MyTest=# SELECT substring('foobar' from '#"o_b#"%' FOR '#');  --foobar不能完全匹配后面的模式,因此返回NULL。
     substring
    -----------

(1 row)

七、数据类型格式化函数:

PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating
point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数,它们都遵循一个公共的调用习
惯:第一个参数是待格式化的值,而第二个是定义输出或输出格式的模板。

函数 返回类型 描述 例子
to_char(timestamp, text) text 把时间戳转换成字串 to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text) text 把时间间隔转为字串 to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text) text 把整数转换成字串 to_char(125, '999')
to_char(double precision, text) text 把实数/双精度数转换成字串 to_char(125.8::real, '999D9')
to_char(numeric, text) text 把numeric转换成字串 to_char(-125.8, '999D99S')
to_date(text, text) date 把字串转换成日期 to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text) timestamp 把字串转换成时间戳 to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double) timestamp 把UNIX纪元转换成时间戳 to_timestamp(200120400)
to_number(text, text) numeric 把字串转换成numeric to_number('12,454.8-', '99G999D9S')

1. 用于日期/时间格式化的模式:

模式 描述
HH 一天的小时数(01-12)
HH12 一天的小时数(01-12)
HH24 一天的小时数(00-23)
MI 分钟(00-59)
SS 秒(00-59)
MS 毫秒(000-999)
US 微秒(000000-999999)
AM 正午标识(大写)
Y,YYY 带逗号的年(4和更多位)
YYYY 年(4和更多位)
YYY 年的后三位
YY 年的后两位
Y 年的最后一位
MONTH 全长大写月份名(空白填充为9字符)
Month 全长混合大小写月份名(空白填充为9字符)
month 全长小写月份名(空白填充为9字符)
MON 大写缩写月份名(3字符)
Mon 缩写混合大小写月份名(3字符)
mon 小写缩写月份名(3字符)
MM 月份号(01-12)
DAY 全长大写日期名(空白填充为9字符)
Day 全长混合大小写日期名(空白填充为9字符)
day 全长小写日期名(空白填充为9字符)
DY 缩写大写日期名(3字符)
Dy 缩写混合大小写日期名(3字符)
dy 缩写小写日期名(3字符)
DDD 一年里的日子(001-366)
DD 一个月里的日子(01-31)
D 一周里的日子(1-7;周日是1)
W 一个月里的周数(1-5)(第一周从该月第一天开始)
WW 一年里的周数(1-53)(第一周从该年的第一天开始)

2. 用于数值格式化的模板模式:

模式 描述
带有指定数值位数的值
带前导零的值
.(句点) 小数点
,(逗号) 分组(千)分隔符
PR 尖括号内负值
S 带符号的数值
L 货币符号
D 小数点
G 分组分隔符
MI 在指明的位置的负号(如果数字 < 0)
PL 在指明的位置的正号(如果数字 > 0)
SG 在指明的位置的正/负号

八、时间/日期函数和操作符:

1. 下面是PostgreSQL中支持的时间/日期操作符的列表:

操作符 例子 结果
+ date '2001-09-28' + integer '7' date '2001-10-05'
+ date '2001-09-28' + interval '1 hour' timestamp '2001-09-28 01:00'
+ date '2001-09-28' + time '03:00' timestamp '2001-09-28 03:00'
+ interval '1 day' + interval '1 hour' interval '1 day 01:00'
+ timestamp '2001-09-28 01:00' + interval '23 hours' timestamp '2001-09-29 00:00'
+ time '01:00' + interval '3 hours' time '04:00'
- - interval '23 hours' interval '-23:00'
- date '2001-10-01' - date '2001-09-28' integer '3'
- date '2001-10-01' - integer '7' date '2001-09-24'
- date '2001-09-28' - interval '1 hour' timestamp '2001-09-27 23:00'
- time '05:00' - time '03:00' interval '02:00'
- time '05:00' - interval '2 hours' time '03:00'
- timestamp '2001-09-28 23:00' - interval '23 hours' timestamp '2001-09-28 00:00'
- interval '1 day' - interval '1 hour' interval '23:00'
- timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' interval '1 day 15:00'
* interval '1 hour' * double precision '3.5' interval '03:30'
/ interval '1 hour' / double precision '1.5' interval '00:40'

2. 日期/时间函数:

函数 返回类型 描述 例子 结果
age(timestamp, timestamp) interval 减去参数,生成一个使用年、月的"符号化"的结果 age('2001-04-10', timestamp '1957-06-13') 43 years 9 mons 27 days
age(timestamp) interval 从current_date减去得到的数值 age(timestamp '1957-06-13') 43 years 8 mons 3 days
current_date date 今天的日期    
current_time time 现在的时间    
current_timestamp timestamp 日期和时间    
date_part(text, timestamp) double 获取子域(等效于extract) date_part('hour', timestamp '2001-02-16 20:38:40')
date_part(text, interval) double 获取子域(等效于extract) date_part('month', interval '2 years 3 months')
date_trunc(text, timestamp) timestamp 截断成指定的精度 date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00+00
extract(field from timestamp) double 获取子域 extract(hour from timestamp '2001-02-16 20:38:40')
extract(field from interval) double 获取子域 extract(month from interval '2 years 3 months')
localtime time 今日的时间    
localtimestamp timestamp 日期和时间    
now() timestamp 当前的日期和时间(等效于 current_timestamp)    
timeofday() text 当前日期和时间    

3. EXTRACT,date_part函数支持的field:

描述 例子 结果
CENTURY 世纪 EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
DAY (月分)里的日期域(1-31) EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40');
DECADE 年份域除以10 EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40');
DOW 每周的星期号(0-6;星期天是0) (仅用于timestamp) EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
DOY 一年的第几天(1 -365/366) (仅用于 timestamp) EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40');
HOUR 小时域(0-23) EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40');
MICROSECONDS 秒域,包括小数部分,乘以 1,000,000。 EXTRACT(MICROSECONDS from TIME '17:12:28.5');
MILLENNIUM 千年 EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40');
MILLISECONDS 秒域,包括小数部分,乘以 1000。 EXTRACT(MILLISECONDS from TIME '17:12:28.5');
MINUTE 分钟域(0-59) EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40');
MONTH 对于timestamp数值,它是一年里的月份数(1-12);对于interval数值,它是月的数目,然后对12取模(0-11) EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40');
QUARTER 该天所在的该年的季度(1-4)(仅用于 timestamp) EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');
SECOND 秒域,包括小数部分(0-59[1]) EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40');
WEEK 该天在所在的年份里是第几周。 EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40');
YEAR 年份域 EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40');

4. 当前日期/时间:
    我们可以使用下面的函数获取当前的日期和/或时间∶
    CURRENT_DATE
    CURRENT_TIME
    CURRENT_TIMESTAMP
    CURRENT_TIME (precision)
    CURRENT_TIMESTAMP (precision)
    LOCALTIME
    LOCALTIMESTAMP
    LOCALTIME (precision)
    LOCALTIMESTAMP (precision)

    该博客中提供的所有信息均源自PostgreSQL官方文档,编写该篇博客的主要目的是便于今后的查阅,特此声明。

九、序列操作函数:

序列对象(也叫序列生成器)都是用CREATE SEQUENCE创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。下面序列函数,为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法。

函数 返回类型 描述
nextval(regclass) bigint 递增序列对象到它的下一个数值并且返回该值。这个动作是自动完成的。即使多个会话并发运行nextval,每个进程也会安全地收到一个唯一的序列值。
currval(regclass) bigint 在当前会话中返回最近一次nextval抓到的该序列的数值。(如果在本会话中从未在该序列上调用过 nextval,那么会报告一个错误。)请注意因为此函数返回一个会话范围的数值,而且也能给出一个可预计的结果,因此可以用于判断其它会话是否执行过nextval。
lastval() bigint 返回当前会话里最近一次nextval返回的数值。这个函数等效于currval,只是它不用序列名为参数,它抓取当前会话里面最近一次nextval使用的序列。如果当前会话还没有调用过nextval,那么调用lastval将会报错。
setval(regclass, bigint) bigint 重置序列对象的计数器数值。设置序列的last_value字段为指定数值并且将其is_called字段设置为true,表示下一次nextval将在返回数值之前递增该序列。
setval(regclass, bigint, boolean) bigint 重置序列对象的计数器数值。功能等同于上面的setval函数,只是is_called可以设置为truefalse。如果将其设置为false,那么下一次nextval将返回该数值,随后的nextval才开始递增该序列。

对于regclass参数,仅需用单引号括住序列名即可,因此它看上去就像文本常量。为了达到和处理普通SQL对象一样的兼容性,这个字串将被转换成小写,除非该序列名是用双引号括起,如:
    nextval('foo')     --操作序列号foo
    nextval('FOO')    --操作序列号foo
    nextval('"Foo"')   --操作序列号Foo
    SELECT setval('foo', 42);    --下次nextval将返回43
    SELECT setval('foo', 42, true);   
    SELECT setval('foo', 42, false);   --下次nextval将返回42

十、条件表达式:

1. CASE:
    SQL CASE表达式是一种通用的条件表达式,类似于其它语言中的if/else语句。
    CASE WHEN condition THEN result
        [WHEN ...]
        [ELSE result]
    END

condition是一个返回boolean的表达式。如果为真,那么CASE表达式的结果就是符合条件的result。如果结果为假,那么以相同方式
搜寻随后的WHEN子句。如果没有WHEN
condition为真,那么case表达式的结果就是在ELSE子句里的值。如果省略了ELSE子句而且没有匹配的条件,结果为NULL,如:
    MyTest=> SELECT * FROM testtable;
     i
    ---
     1
     2
     3
    (3 rows)
    MyTest=> SELECT i, CASE WHEN i=1 THEN 'one'
    MyTest->                         WHEN i=2 THEN 'two'
    MyTest->                         ELSE 'other'
    MyTest->                END
    MyTest-> FROM testtable;
     i | case
    ---+-------
     1 | one
     2 | two
     3 | other
    (3 rows)
    注:CASE表达式并不计算任何对于判断结果并不需要的子表达式。
    
    2. COALESCE:
    COALESCE返回它的第一个非NULL的参数的值。它常用于在为显示目的检索数据时用缺省值替换NULL值。
    COALESCE(value[, ...])
    和CASE表达式一样,COALESCE将不会计算不需要用来判断结果的参数。也就是说,在第一个非空参数右边的参数不会被计算。
    
    3. NULLIF:
    当且仅当value1和value2相等时,NULLIF才返回NULL。否则它返回value1。
    NULLIF(value1, value2)
    MyTest=> SELECT NULLIF('abc','abc');
     nullif
    --------
    
    (1 row)   
    MyTest=> SELECT NULLIF('abcd','abc');
     nullif
    --------
     abcd
    (1 row)

4. GREATEST和LEAST:
    GREATEST和LEAST函数从一个任意的数字表达式列表里选取最大或者最小的数值。列表中的NULL数值将被忽略。只有所有表达式的结果都是NULL的时候,结果才会是NULL。
    GREATEST(value [, ...])
    LEAST(value [, ...])
    MyTest=> SELECT GREATEST(1,3,5);
     greatest
    ----------
            5
    (1 row) 
    MyTest=> SELECT LEAST(1,3,5,NULL);
     least
    -------
         1
    (1 row)
    
十一、数组函数和操作符:

1. PostgreSQL中提供的用于数组的操作符列表:

操作符 描述 例子 结果
= 等于 ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] t
<> 不等于 ARRAY[1,2,3] <> ARRAY[1,2,4] t
< 小于 ARRAY[1,2,3] < ARRAY[1,2,4] t
> 大于 ARRAY[1,4,3] > ARRAY[1,2,4] t
<= 小于或等于 ARRAY[1,2,3] <= ARRAY[1,2,3] t
>= 大于或等于 ARRAY[1,4,3] >= ARRAY[1,4,3] t
|| 数组与数组连接 ARRAY[1,2,3] || ARRAY[4,5,6] {1,2,3,4,5,6}
|| 数组与数组连接 ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] {{1,2,3},{4,5,6},{7,8,9}}
|| 元素与数组连接 3 || ARRAY[4,5,6] {3,4,5,6}
|| 元素与数组连接 ARRAY[4,5,6] || 7 {4,5,6,7}

2. PostgreSQL中提供的用于数组的函数列表:

函数 返回类型 描述 例子 结果
array_cat(anyarray, anyarray) anyarray 连接两个数组 array_cat(ARRAY[1,2,3], ARRAY[4,5]) {1,2,3,4,5}
array_append(anyarray, anyelement) anyarray 向一个数组末尾附加一个元素 array_append(ARRAY[1,2], 3) {1,2,3}
array_prepend(anyelement, anyarray) anyarray 向一个数组开头附加一个元素 array_prepend(1, ARRAY[2,3]) {1,2,3}
array_dims(anyarray) text 返回一个数组维数的文本表示 array_dims(ARRAY[[1,2,3], [4,5,6]]) [1:2][1:3]
array_lower(anyarray, int) int 返回指定的数组维数的下界 array_lower(array_prepend(0, ARRAY[1,2,3]), 1) 0
array_upper(anyarray, int) int 返回指定数组维数的上界 array_upper(ARRAY[1,2,3,4], 1) 4
array_to_string(anyarray, text) text 使用提供的分隔符连接数组元素 array_to_string(ARRAY[1, 2, 3], '~^~') 1~^~2~^~3
string_to_array(text, text) text[] 使用指定的分隔符把字串拆分成数组元素 string_to_array('xx~^~yy~^~zz', '~^~') {xx,yy,zz}

十二、系统信息函数:

1. PostgreSQL中提供的和数据库相关的函数列表:

名字 返回类型 描述
current_database() name 当前数据库的名字
current_schema() name 当前模式的名字
current_schemas(boolean) name[] 在搜索路径中的模式名字
current_user name 目前执行环境下的用户名
inet_client_addr() inet 连接的远端地址
inet_client_port() int 连接的远端端口
inet_server_addr() inet 连接的本地地址
inet_server_port() int 连接的本地端口
session_user name 会话用户名
pg_postmaster_start_time() timestamp postmaster启动的时间
user name current_user
version() text PostgreSQL版本信息

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

名字 描述 可用权限
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

    注:以上函数均返回boolean类型。要评估一个用户是否在权限上持有赋权选项,给权限键字附加 WITH GRANT OPTION;比如 'UPDATE WITH GRANT OPTION'。
    3. 模式可视性查询函数:
    那些判断一个对象是否在当前模式搜索路径中可见的函数。 如果一个表所在的模式在搜索路径中,并且没有同名的表出现在搜索路径的更早的地方,那么就说这个表视可见的。 它等效于表可以不带明确模式修饰进行引用。

名字 描述 应用类型
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

    注:以上函数均返回boolean类型,所有这些函数都需要对象 OID 标识作为检查的对象。
    postgres=# SELECT pg_table_is_visible('testtable'::regclass);
     pg_table_is_visible
    ---------------------
     t
    (1 row)

4. 系统表信息函数:

名字 返回类型 描述
format_type(type_oid,typemod) text 获取一个数据类型的SQL名称
pg_get_viewdef(view_oid) text 为视图获取CREATE VIEW命令
pg_get_viewdef(view_oid,pretty_bool) text 为视图获取CREATE VIEW命令
pg_get_ruledef(rule_oid) text 为规则获取CREATE RULE命令
pg_get_ruledef(rule_oid,pretty_bool) text 为规则获取CREATE RULE命令
pg_get_indexdef(index_oid) text 为索引获取CREATE INDEX命令
pg_get_indexdef(index_oid,column_no,pretty_bool) text 为索引获取CREATE INDEX命令, 如果column_no不为零,则是只获取一个索引字段的定义
pg_get_triggerdef(trigger_oid) text 为触发器获取CREATE [CONSTRAINT] TRIGGER
pg_get_constraintdef(constraint_oid) text 获取一个约束的定义
pg_get_constraintdef(constraint_oid,pretty_bool) text 获取一个约束的定义
pg_get_expr(expr_text,relation_oid) text 反编译一个表达式的内部形式,假设其中的任何Vars都引用第二个参数指出的关系
pg_get_expr(expr_text,relation_oid, pretty_bool) text 反编译一个表达式的内部形式,假设其中的任何Vars都引用第二个参数指出的关系
pg_get_userbyid(roleid) name 获取给出的ID的角色名
pg_get_serial_sequence(table_name,column_name) text 获取一个serial或者bigserial字段使用的序列名字
pg_tablespace_databases(tablespace_oid) setof oid 获取在指定表空间(OID表示)中拥有对象的一套数据库的OID的集合

这些函数大多数都有两个变种,其中一个可以选择对结果的"漂亮的打印"。
漂亮打印的格式更容易读,但是缺省的格式更有可能被将来的PostgreSQL版本用同样的方法解释;如果是用于转储,那么尽可能避免使用漂亮打印。
给漂亮打印参数传递false生成的结果和那个没有这个参数的变种生成的结果完全一样。

、系统管理函数:

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

名字 返回类型 描述
current_setting(setting_name) text 当前设置的值
set_config(setting_name,new_value,is_local) text 设置参数并返回新值

current_setting用于以查询形式获取setting_name设置的当前数值。它和SQL命令SHOW是等效的。 比如:
    MyTest=# SELECT current_setting('datestyle');
     current_setting
    -----------------
     ISO, YMD
    (1 row)
    set_config将参数setting_name设置为new_value。如果is_local设置为true,那么新数值将只应用于当前事务。如果你希望新的数值应用于当前会话,那么应该使用false。它等效于SQL命令SET。比如:
    MyTest=# SELECT set_config('log_statement_stats','off', false);
     set_config
    ------------
     off
    (1 row)
   
    2. 数据库对象尺寸函数:

名字 返回类型 描述
pg_tablespace_size(oid) bigint 指定OID代表的表空间使用的磁盘空间
pg_tablespace_size(name) bigint 指定名字的表空间使用的磁盘空间
pg_database_size(oid) bigint 指定OID代表的数据库使用的磁盘空间
pg_database_size(name) bigint 指定名称的数据库使用的磁盘空间
pg_relation_size(oid) bigint 指定OID代表的表或者索引所使用的磁盘空间
pg_relation_size(text) bigint 指定名称的表或者索引使用的磁盘空间。这个名字可以用模式名修饰
pg_total_relation_size(oid) bigint 指定OID代表的表使用的磁盘空间,包括索引和压缩数据
pg_total_relation_size(text) bigint 指定名字的表所使用的全部磁盘空间,包括索引和压缩数据。表名字可以用模式名修饰。
pg_size_pretty(bigint) text 把字节计算的尺寸转换成一个人类易读的尺寸单位

3. 数据库对象位置函数: 

名字 返回类型 描述
pg_relation_filenode(relation regclass) oid 获取指定对象的文件节点编号(通常为对象的oid值)。
pg_relation_filepath(relation regclass) text 获取指定对象的完整路径名。

    mydatabase=# select pg_relation_filenode('testtable');
     pg_relation_filenode
    ----------------------
                    17877
    (1 row)   
    mydatabase=# select pg_relation_filepath('testtable');
                 pg_relation_filepath
    ----------------------------------------------
     pg_tblspc/17633/PG_9.1_201105231/17636/17877
    (1 row)   

    该博客中提供的所有信息均源自PostgreSQL官方文档,编写该篇博客的主要目的是便于今后的查阅,特此声明。