- 1机器翻译的新波：探索零 shot和一 shot翻译方法
- 2前端开发攻略---封装calendar日历组件，实现日期多选。可根据您的需求任意调整，可玩性强。
- 3【个人总结】大二这一年我都干了啥？_字节日常实习简历全部都挂掉了为什么
- 4数据结构（一）：数组及面试常考的算法_数据结构常考算法
- 5selenium-语法_selenium版块官方文档
- 6深度强化学习笔记——基本方法分类与一般思路_深度强化学习分类
- 7分布式锁其实很简单，6行代码教你实现redis分布式锁，千万不要再用redisTemplate写redis分布式锁代码实现
- 8【整理】BIOS、BootLoader、uboot对比
- 9java可以开发人工智能吗_java能做智能产品吗
- 10STM32 HAL库F103系列之DAC实验（一）
PostgreSQL 基本数据类型及常用SQL 函数操作_postsql 当前行 函数
赞
踩
数据类型
名字 | 别名 | 描述 |
bigint | 有符号的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和bitvarying，除了常用的比较操作符之外，还可以使用以下列表中由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
------
14
六、模式匹配：
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。还有!~~和!~~*操作符分别代表NOTLIKE和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、floatingpoint和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 GRANTOPTION'。
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=# selectpg_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)
