PostgreSQL 基本数据类型及常用SQL 函数操作 - xalion

数据类型

名字	别名	描述
`bigint`	`int8`	有符号的8字节整数
`bigserial`	`serial8`	自动增长的8字节整数
`bit [ (n) ]`		定长位串
`bit varying [ (n) ]`	`varbit`	变长位串
`boolean`	`bool`	逻辑布尔值（真/假）
`box`		平面上的普通方框
`bytea`		二进制数据（“字节数组”）
`character [ (n) ]`	`char [ (n) ]`	定长字符串
`character varying [ (n) ]`	`varchar [ (n) ]`	变长字符串
`cidr`		IPv4或IPv6网络地址
`circle`		平面上的圆
`date`		日历日期（年、月、日）
`double precision`	`float8`	双精度浮点数（8字节）
`inet`		IPv4或IPv6主机地址
`integer`	`int`, `int4`	有符号4字节整数
`interval [ fields ] [ (p) ]`		时间段
`json`		文本 JSON 数据
`jsonb`		二进制 JSON 数据，已分解
`line`		平面上的无限长的线
`lseg`		平面上的线段
`macaddr`		MAC（Media Access Control）地址
`macaddr8`		MAC (Media Access Control) 地址 (EUI-64 格式)
`money`		货币数量
`numeric [ (p, s) ]`	`decimal [ (p, s) ]`	可选择精度的精确数字
`path`		平面上的几何路径
`pg_lsn`		PostgreSQL日志序列号
`point`		平面上的几何点
`polygon`		平面上的封闭几何路径
`real`	`float4`	单精度浮点数（4字节）
`smallint`	`int2`	有符号2字节整数
`smallserial`	`serial2`	自动增长的2字节整数
`serial`	`serial4`	自动增长的4字节整数
`text`		变长字符串
`time [ (p) ] [ without time zone ]`		一天中的时间（无时区）
`time [ (p) ] with time zone`	`timetz`	一天中的时间，包括时区
`timestamp [ (p) ] [ without time zone ]`		日期和时间（无时区）
`timestamp [ (p) ] with time zone`	`timestamptz`	日期和时间，包括时区
`tsquery`		文本搜索查询
`tsvector`		文本搜索文档
`txid_snapshot`		用户级别事务ID快照
`uuid`		通用唯一标识码
`xml`		XML数据

1. 查询服务器当前时间及日期

select now(); 取当前日期及时间

select current_time;

select current_date;

select extract(YEAR from now()); 取当前日期的年

select extract(month from now()); //取当前月

select extract(day from now()); //取当前日

2. 字符串操作

select 'aaaaa'||'bbbbbb' as f1; //字符串相加

select char_length('abcdefgh'); //字符串长度

select position('fgh' in 'abcdefgh'); //查找子串

select substring ( 'abcdefgh' from 5 for 3); //取一段字符串

select lower( 'abCDefgh')||upper('abCDefgh')

3. 日期转字符串

select to_char(now(), 'yyyy-mm-dd hh:mi:ss');

更多的函数和操作见http://www.postgres.cn/docs/11/functions.html

一、逻辑操作符：

常用的逻辑操作符有：AND、OR和NOT。其语义与其它编程语言中的逻辑操作符完全相同。

二、比较操作符：

下面是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	5
-	减	2 - 3	-1
*	乘	2 * 3	6
/	除	4 / 2	2
%	模	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

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

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

函数	返回类型	描述	例子	结果
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.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)	2
log(b numeric,x numeric)		numeric指定底数的对数	log(2.0, 64.0)	6.0000000000
mod(y, x)		取余数	mod(9,4)	1
pi()	double	"π"常量	pi()	3.14159265358979
power(a double, b double)	double	求a的b次幂	power(9.0, 3.0)	729
power(a numeric, b numeric)	numeric	求a的b次幂	power(9.0, 3.0)	729
radians(double)	double	把角度转为弧度	radians(45.0)	0.785398163397448
random()	double	0.0到1.0之间的随机数值	random()
round(double/numeric)		圆整为最接近的整数	round(42.4)	42
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)	42
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')	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 '%#"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')	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

五、位串函数和操作符：

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

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

    除了以上列表中提及的操作符之外，位串还可以使用字符串函数：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
   ------

六、模式匹配：

   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 LIKE和NOT 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. 用于数值格式化的模板模式：

模式	描述
9	带有指定数值位数的值
0	带前导零的值
.(句点)	小数点
,(逗号)	分组(千)分隔符
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')	20
date_part(text, interval)	double	获取子域(等效于extract)	date_part('month', interval '2 years 3 months')	3
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')	20
extract(field from interval)	double	获取子域	extract(month from interval '2 years 3 months')	3
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');	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

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

九、序列操作函数：

序列对象(也叫序列生成器)都是用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对象一样的兼容性，这个字串将被转换成小写，除非该序列名是用双引号括起，如：
   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)

posted on 2019-12-01 11:40 xalion 阅读(3953) 评论(0) 编辑收藏举报

刷新页面返回顶部