PostgreSQL 运算符(操作符)
PostgreSQL 运算符
运算符是一种告诉编译器执行特定的数学或逻辑操作的符号。
PostgreSQL 运算符是一个保留关键字或字符,一般用在 WHERE 语句中,作为过滤条件。
常见的运算符有:
- 算术运算符
- 比较运算符
- 逻辑运算符
- 按位运算符
算术运算符
假设变量 a 为 2,变量 b 为 3,则:
运算符 | 描述 | 实例 |
---|---|---|
+ | 加 | a + b 结果为 5 |
- | 减 | a - b 结果为 -1 |
* | 乘 | a * b 结果为 6 |
/ | 除 | b / a 结果为 1 |
% | 模(取余) | b % a 结果为 1 |
^ | 指数 | a ^ b 结果为 8 |
|/ | 平方根 | |/ 25.0 结果为 5 |
||/ | 立方根 | ||/ 27.0 结果为 3 |
! | 阶乘 | 5 ! 结果为 120 |
!! | 阶乘(前缀操作符) | !! 5 结果为 120 |
实例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | lhrpgdb=# select 2+3; ?column? ---------- 5 (1 row) lhrpgdb=# select 2*3; ?column? ---------- 6 (1 row) lhrpgdb=# select 10/5; ?column? ---------- 2 (1 row) lhrpgdb=# select 12%5; ?column? ---------- 2 (1 row) lhrpgdb=# select 2^3; ?column? ---------- 8 (1 row) lhrpgdb=# select |/ 25.0; ?column? ---------- 5 (1 row) lhrpgdb=# select ||/ 27.0; ?column? ---------- 3 (1 row) lhrpgdb=# select 5 !; ?column? ---------- 120 (1 row) lhrpgdb=# select !!5; ?column? ---------- 120 (1 row) |
比较运算符
假设变量 a 为 10,变量 b 为 20,则:
运算符 | 描述 | 实例 |
---|---|---|
= | 等于 | (a = b) 为 false。 |
!= | 不等于 | (a != b) 为 true。 |
<> | 不等于 | (a <> b) 为 true。 |
> | 大于 | (a > b) 为 false。 |
< | 小于 | (a < b) 为 true。 |
>= | 大于等于 | (a >= b) 为 false。 |
<= | 小于等于 | (a <= b) 为 true。 |
实例
创建 COMPANY 表,数据内容如下:
1 2 3 4 5 6 7 8 9 10 11 | lhrpgdb=# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows) |
读取 SALARY 字段大于 50000 的数据:
1 2 3 4 5 6 | lhrpgdb=# SELECT * FROM COMPANY WHERE SALARY > 50000; id | name | age |address | salary ----+-------+-----+-----------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (2 rows) |
读取 SALARY 字段等于 20000 的数据:
1 2 3 4 5 6 | lhrpgdb=# SELECT * FROM COMPANY WHERE SALARY = 20000; id | name | age | address | salary -----+-------+-----+-------------+-------- 1 | Paul | 32 | California | 20000 3 | Teddy | 23 | Norway | 20000 (2 rows) |
读取 SALARY 字段不等于 20000 的数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | lhrpgdb=# SELECT * FROM COMPANY WHERE SALARY != 20000; id | name | age | address | salary ----+-------+-----+-------------+--------- 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 (5 rows) lhrpgdb=# SELECT * FROM COMPANY WHERE SALARY <> 20000; id | name | age | address | salary ----+-------+-----+------------+--------- 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 (5 rows) |
读取 SALARY 字段大于等于 65000 的数据:
1 2 3 4 5 6 | lhrpgdb=# SELECT * FROM COMPANY WHERE SALARY >= 65000; id | name | age | address | salary ----+-------+-----+-----------+--------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (2 rows) |
逻辑运算符
PostgreSQL 逻辑运算符有以下几种:
序号 | 运算符 & 描述 |
---|---|
1 | AND逻辑与运算符。如果两个操作数都非零,则条件为真。PostgresSQL 中的 WHERE 语句可以用 AND 包含多个过滤条件。 |
2 | NOT逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。PostgresSQL 有 NOT EXISTS, NOT BETWEEN, NOT IN 等运算符。 |
3 | OR逻辑或运算符。如果两个操作数中有任意一个非零,则条件为真。PostgresSQL 中的 WHERE 语句可以用 OR 包含多个过滤条件。 |
SQL 使用三值的逻辑系统,包括 true、false 和 null,null 表示"未知"。
a | b | a AND b | a OR b |
---|---|---|---|
TRUE | TRUE | TRUE | TRUE |
TRUE | FALSE | FALSE | TRUE |
TRUE | NULL | NULL | TRUE |
FALSE | FALSE | FALSE | FALSE |
FALSE | NULL | FALSE | NULL |
NULL | NULL | NULL | NULL |
a | NOT a |
---|---|
TRUE | FALSE |
FALSE | TRUE |
NULL | NULL |
实例
创建 COMPANY 表(下载 COMPANY SQL 文件 ),数据内容如下:
1 2 3 4 5 6 7 8 9 10 11 | lhrpgdb=# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+--------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows) |
读取 AGE 字段大于等于 25 且 SALARY 字段大于等于 6500 的数据:
1 2 3 4 5 6 7 8 | lhrpgdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500; id | name | age | address | salary ----+-------+-----+-----------------------------------------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (4 rows) |
读取 AGE 字段大于等于 25 或 SALARY 字段大于 6500 的数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | lhrpgdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 6500; id | name | age | address | salary ----+-------+-----+-------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows) |
读取 SALARY 字段不为 NULL 的数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | lhrpgdb=# SELECT * FROM COMPANY WHERE SALARY IS NOT NULL; id | name | age | address | salary ----+-------+-----+-------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows) |
位运算符
位运算符作用于位,并逐位执行操作。&、 | 和 ^ 的真值表如下所示:
p | q | p & q | p | q |
---|---|---|---|
0 | 0 | 0 | 0 |
0 | 1 | 0 | 1 |
1 | 1 | 1 | 1 |
1 | 0 | 0 | 1 |
假设如果 A = 60,且 B = 13,现在以二进制格式表示,它们如下所示:
1 2 3 4 5 6 7 8 | A = 0011 1100 B = 0000 1101 ---------------- A&B = 0000 1100 A|B = 0011 1101 A^B = 0011 0001 ~A = 1100 0011 |
下表显示了 PostgreSQL 支持的位运算符。假设变量 A 的值为 60,变量 B 的值为 13,则:
运算符 | 描述 | 实例 |
---|---|---|
& | 按位与操作,按二进制位进行"与"运算。运算规则:0&0=0; 0&1=0; 1&0=0; 1&1=1; | (A & B) 将得到 12,即为 0000 1100 |
| | 按位或运算符,按二进制位进行"或"运算。运算规则:0|0=0; 0|1=1; 1|0=1; 1|1=1; | (A | B) 将得到 61,即为 0011 1101 |
# | 异或运算符,按二进制位进行"异或"运算。运算规则:0#0=0; 0#1=1; 1#0=1; 1#1=0; | (A # B) 将得到 49,即为 0011 0001 |
~ | 取反运算符,按二进制位进行"取反"运算。运算规则:~1=0; ~0=1; | (~A ) 将得到 -61,即为 1100 0011,一个有符号二进制数的补码形式。 |
<< | 二进制左移运算符。将一个运算对象的各二进制位全部左移若干位(左边的二进制位丢弃,右边补0)。 | A << 2 将得到 240,即为 1111 0000 |
>> | 二进制右移运算符。将一个数的各二进制位全部右移若干位,正数左补0,负数左补1,右边丢弃。 | A >> 2 将得到 15,即为 0000 1111 |
实例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | lhrpgdb=# select 60 | 13; ?column? ---------- 61 (1 row) lhrpgdb=# select 60 & 13; ?column? ---------- 12 (1 row) lhrpgdb=# select (~60); ?column? ---------- -61 (1 row) lhrpgdb=# select (60 << 2); ?column? ---------- 240 (1 row) lhrpgdb=# select (60 >> 2); ?column? ---------- 15 (1 row) lhrpgdb=# select 60 # 13; ?column? ---------- 49 (1 row) |
字符串函数和操作符
下面是PostgreSQL中提供的字符串操作符列表:
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
string || string | text | 字串连接 | 'Post' || 'greSQL' | PostgreSQL |
bit_length(string) | int | 字串里二进制位的个数 | bit_length('jose') | 32 |
char_length(string) | int | 字串中的字符个数 | char_length('jose') | 4 |
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') | 4 |
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') | 3 |
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 '%#"oa#"' 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') | 120 |
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') | 4 |
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') | 2 |
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 |
模式匹配
PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式。
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 ''的方式有效地关闭转义机制,此时,我们就不能关闭下划线和百分号的特殊含义了。
关键字*ILIK*E可以用于替换LIKE,令该匹配就当前的区域设置是大小写无关的。这个特性不是SQL标准,是PostgreSQL的扩展。操作符~~等效于**LIKE*, 而~~\对应*ILIKE*。还有!和**!*操作符分别代表\NOT LIKE***和*NOT ILIKE****。所有这些操作符都是PostgreSQL特有的。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。为了标识在成功的时候应该返回的模式部分,模式必须出现后跟双引号(")的两个转义字符。匹配这两个标记之间的模式的字串将被返回,如:1234567891011MyTest=# 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') |
- 用于日期/时间格式化的模式:
模式 | 描述 |
---|---|
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. 用于数值格式化的模板模式:
模式 | 描述 |
---|---|
9 | 带有指定数值位数的值 |
0 | 带前导零的值 |
.(句点) | 小数点 |
,(逗号) | 分组(千)分隔符 |
PR | 尖括号内负值 |
S | 带符号的数值 |
L | 货币符号 |
D | 小数点 |
G | 分组分隔符 |
MI | 在指明的位置的负号(如果数字 < 0) |
PL | 在指明的位置的正号(如果数字 > 0) |
SG | 在指明的位置的正/负号 |
序列操作函数
序列对象(也叫序列生成器)都是用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 可以设置为true 或false 。如果将其设置为false ,那么下一次nextval 将返回该数值,随后的nextval 才开始递增该序列。 |
对于regclass参数,仅需用单引号括住序列名即可,因此它看上去就像文本常量。为了达到和处理普通SQL对象一样的兼容性,这个字串将被转换成小写,除非该序列名是用双引号括起,如:
1 2 3 4 5 6 | 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语句。
1 2 3 4 | CASE WHEN condition THEN result [WHEN ...] [ELSE result] END |
condition是一个返回boolean的表达式。如果为真,那么CASE表达式的结果就是符合条件的result。如果结果为假,那么以相同方式搜寻随后的WHEN子句。如果没有WHEN condition为真,那么case表达式的结果就是在ELSE子句里的值。如果省略了ELSE子句而且没有匹配的条件,结果为NULL,如:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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表达式并不计算任何对于判断结果并不需要的子表达式。
COALESCE:
COALESCE返回它的第一个非NULL的参数的值。它常用于在为显示目的检索数据时用缺省值替换NULL值。
COALESCE(value[, ...])
和CASE表达式一样,COALESCE将不会计算不需要用来判断结果的参数。也就是说,在第一个非空参数右边的参数不会被计算。NULLIF:
当且仅当value1和value2相等时,NULLIF才返回NULL。否则它返回value1。
1 2 3 4 5 6 7 8 9 10 11 12 | NULLIF(value1, value2) MyTest=> SELECT NULLIF('abc','abc'); nullif -------- (1 row) MyTest=> SELECT NULLIF('abcd','abc'); nullif -------- abcd (1 row) |
GREATEST和LEAST:
GREATEST和LEAST函数从一个任意的数字表达式列表里选取最大或者最小的数值。列表中的NULL数值将被忽略。只有所有表达式的结果都是NULL的时候,结果才会是NULL。123456789101112GREATEST(value [, ...])LEAST(value [, ...])MyTest=> SELECT GREATEST(1,3,5);greatest----------5(1 row)MyTest=> SELECT LEAST(1,3,5,NULL);least-------1(1 row)
数组函数和操作符:
- 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} |
- 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} |
系统信息函数:
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版本信息 |
允许用户在程序里查询对象访问权限的函数:
名字 | 描述 | 可用权限 |
---|---|---|
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'。
模式可视性查询函数:
那些判断一个对象是否在当前模式搜索路径中可见的函数。 如果一个表所在的模式在搜索路径中,并且没有同名的表出现在搜索路径的更早的地方,那么就说这个表视可见的。 它等效于表可以不带明确模式修饰进行引用。
名字 | 描述 | 应用类型 |
---|---|---|
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 标识作为检查的对象。
1 2 3 4 5 | postgres=# SELECT pg_table_is_visible('testtable'::regclass); pg_table_is_visible --------------------- t (1 row) |
系统表信息函数
名字 | 返回类型 | 描述 |
---|---|---|
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生成的结果和那个没有这个参数的变种生成的结果完全一样。
系统管理函数
- 查询以及修改运行时配置参数的函数:
名字 | 返回类型 | 描述 |
---|---|---|
current_setting(setting_name) | text | 当前设置的值 |
set_config(setting_name,new_value,is_local) | text | 设置参数并返回新值 |
current_setting用于以查询形式获取setting_name设置的当前数值。它和SQL命令SHOW是等效的。 比如:
1 2 3 4 5 | MyTest=# SELECT current_setting('datestyle'); current_setting ----------------- ISO, YMD (1 row) |
set_config将参数setting_name设置为new_value。如果is_local设置为true,那么新数值将只应用于当前事务。如果你希望新的数值应用于当前会话,那么应该使用false。它等效于SQL命令SET。比如:
1 2 3 4 5 | MyTest=# SELECT set_config('log_statement_stats','off', false); set_config ------------ off (1 row) |
数据库对象尺寸函数:
名字 | 返回类型 | 描述 |
---|---|---|
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 | 把字节计算的尺寸转换成一个人类易读的尺寸单位 |
数据库对象位置函数:
名字 | 返回类型 | 描述 |
---|---|---|
pg_relation_filenode(relationregclass) | oid | 获取指定对象的文件节点编号(通常为对象的oid值)。 |
pg_relation_filepath(relationregclass) | text | 获取指定对象的完整路径名。 |
1 2 3 4 5 6 7 8 9 10 | 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) |