理解 PostgreSQL 数据类型
2019-08-11 16:15:02 
阿炯
1、数值类型：整数类型、浮点数类型、任意精度类型
PostgreSQL支付多种数值类型，数值类型主要有用来存储数字。不同的数值类型有不同的取值范围，取值范围越大，所需的存储空间也越大。PostgreSQL提供的数值类型主要包括：整数类型、浮点数类型、任意精度类型和序号类型。
1.整数类型
整数类型包括：SMALLINT、INT(INTEGER)、BIGINT三种，三者在取值空间和存储范围上有所不同，不同的存储空间也决定了不同的查询效率。应根据实际需要选择最适合的类型，以在查询效率和存储空间上有所平衡。
2.浮点数类型
实际工作中很多地方需要用到带小数的数值，PostgreSQL使用浮点数来表示小数。浮点数类型有两种：REAL和DOUBLE PRECISION。PostgreSQL也支持使用标准的SQL语法，即：float和float(p)来声明非精确的数值类型，p表示可接受的精度。REAL类型对应float(1)~float(24)，DOUBLE PRECISION对应float(25)~float(53)，未声明精度时将被当作DOUBLE PRECISION处理。
3.任意精度类型
NUMERIC表示任意精度类型，PostgreSQL中任意精度类型可存储最多1000位精度的数字并且准确地进行计算，非常适合用于货币金额和其它要求计算准确的数据，但是，NUMERIC类型的运算速度要比整数类型或者浮点数类型要慢很多。使用格式为：NUMERIC(M,N)。其中，M称为精度，表示总位数；N称为标度，表示小数位。M和N决定了NUMERIC的取值范围，当用户数据的精度超出指定精度时，会进行四舍五入处理。
4.序号类型
序号类型SERIAL和BIGSERIAL并不是真正的类型， 只是为在表中设置唯一标识做的概念上的便利。在目前的实现中，下面一句话：
CREATE TABLE tablename (colname SERIAL);
等价于：
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename(
colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);
类型名 SERIAL 和 SERIAL4 是等效的： 两个都创建 INTEGRE 字段。类型名 BIGSERIAL 和 SERIAL8 也一样，只不过它创建一个 BIGINT 字段。 如果你预计在表的生存期中使用的标识数目超过 2^31 ，那么你应该使用 BIGSERIAL。
2、日期时间类型
PostgreSQL保存日期时间格式的数据类型有多种，主要有：TIME、DATE、TIMESTAMP、INTERVAL。日期时间类型输入像字符串类型输入一样，需要加单引号。每种日期时间类型都有合法的取值范围，超出范围时系统会将"零"插入数据记录中。
1.时间类型
时间类型是TIME和TIMEwith time zone，默认情况下为不带时区(即:TIME)。不带时区时间格式可接受输入方式有：HH:MM:SS、HH:MM、HHMMSS。带时间格式输入可用系统的NOW()函数。时间、时区输入参考如下。
时间输入：
时区输入：
2.DATE类型
DATE类型用于仅需要日期值时，日期输入格式为：YYYY-MM-DD、YYYYMMDD。输入DATE类型数据时，可以使用字符串或数字类型的数据输入，符合DATE的日期格式即可。可以使用CURRENT_DATE或NOW()插入当前系统时间。
3. TIMESTAMP类型
时间戳类型的有效输入由一个日期和时间的联接组成，后面跟着一个可选的时区，一个可选的 AD 或者 BC。时间戳输入格式为：YYYY-MM-DD HH:MM:SS。带时区输入格式为：2019-03-22 4:05:06 -8:00
4.INTERVAL类型
INTERVAL类型输入格式如下：
quantity unit [quantity unit...] [direction]
quantity 是一个数字或符号； unit 是 second, minute, hour, day, week, month, year, decade, century, millennium, 或这些单位的缩写或复数； direction 可以是 ago 或者为空。符号 @ 是一个可选的东西。不同的单位以及相应正确的符号都是隐含地增加。
3、字符串类型、二进制类型
PostgreSQL支持两种字符型数据：文本字符串和二进制字符串。PostgreSQL提供了三种存储字符串的类型：char、varchar、text。bytea类型用于允许存储二进制字串。
1.char和varchar类型
两种类型最多存储用户自定义长度N个字符。超出长度会产生错误，存储字符小于定义长度时，CHAR类型会用空格补满存储空间，VARCHAR类型只存储实际长度。
2.text类型
TEXT不是标准的SQL类型，许多数据库系统都实现了这一类型，在PostgreSQL中，TEXT可存储任意长度的字符串。
3.bytea类型
PostgreSQL提供了BYTEA类型，用于存储二进制字符串。BYTEA类型数据存储空间为用户实际二进制字符串加4字节。例如向file表file_byte字段插入E'\\001'数据，SQL语句如下：
INSERT INTO file file_byte VALUES (E'\\001);
4、布尔类型、位串类型
PostgreSQL支持标准的 SQL boolean 数据类型，boolean值只能有两种： '真'('True') 或 '假'('False')。boolean有两种值外的第三种状态，'未知'('Unknow')，用 SQL空状态表示。位串就是一串 1 和 0 的字符串。它们可以用于存储和视觉化位掩码。
1.布尔类型
boolean类型存储空间为1字节，可接受的"真"值的有效文本输入值有：TRUE、't'、'true'、'y'、'yes'、'1'。"假"值可接受的有效输入值有：FALSE、'f'、'false'、'n'、'no'、'0'。建议使用与SQL标准兼容的TRUE和FALSE做为boolean类型的输入值。查询时使用字母 t 和 f做为boolean型数据显示。例如，users表中有列used字段为boolea类型，字段查询后显示如下：
used
-------
t
f
2.位串类型
有两种类型的SQL位类型：bit(n) 和 bit varying(n)；bit类型的数据必须准确匹配长度n；bit varying类型数据是最大长度为n的变长类型；把一个位串值转换成bit(n)，那么它的右边将被截断或者在右边补齐零，以符合定义的n位长度，不会抛出任何错误。把一个位串数值转换成 bit varying(n)，如果它超过了n位，那么它的右边将被截断。示例：创建包括a(bit)，b(bit varying)两列表test，插入数据后执行查询，SQL语句如下：
CREATE TABLE test (a bit(3), b bit varying(5));
INSERT INTO test VALUES (B'101', B'00');
INSERT INTO test VALUES (B'10', B'101');
ERROR: Bit string length 2 does not match type bit(3)
INSERT INTO test VALUES (B'10'::bit(3), B'101');
SELECT * FROM test;
显示结果如下：
a | b
-----+-----
101 | 00
100 | 101
5、数组类型
PostgreSQL 允许将字段定义成定长或不定长的多维数组，数组类型可以是任何基本类型或用户定义类型(复合类型和域的数组还不支持)。
1.数据类型定义
数组类型定义是通过在数组元素类型名后面国方括号：[]来命名的。PostgreSQL目前的实现并不强制限制数组长度，即：声明长度和未声明长度的数组相同。也不强制数组维数，特定元素类型的数组都被认为是相同的类型，不管他们的大小或者维数。创建数组类型字段示例如下：
CREATE TABLE array_tmp(
name text,
pay_by_quarter integer[],
schedule text[][],
squares integer[3][3] --指定长度后仍然不会限制长度
);
2.数组类型数据插入
数组类型数据插入用大括号把值括起来并且用逗号将它们分开。可以在任意数组值周围添加双引号，如果值包含逗号或者花括弧，必须加上双引号输入。数据类型数据插入，还可以使用ARRAY构造器。示例如下：
INSERT INTO array_tmp VALUES(
'freeoa',
'{1,2,3,4}',
'{{"include {"},{"2value"}}',
'{{111,122},{211, 222}}');
INSERT INTO array_tmp VALUES (
'liuht',
ARRAY[1,2,3,4],
ARRAY[['value 11', 'value 12'], ['value 21', 'value 22']],
ARRAY[[111,122],[211, 222]]); --使用ARRAY构造器
查询结果如下：
name | pay_by_quarter | schedule | squares
--------+----------------+---------------------------------------------------+-----------------------
freeoa | {1,2,3,4} | {{"include {"},{2value}} | {{111,122},{211,222}}
liuht | {1,2,3,4} | {{"value 11","value 12"},{"value 21","value 22"}} | {{111,122},{211,222}}
3.数组类型数据的查询
和其他语言一样，PostgreSQL中数组也是通过下标数字的方式进行访问，只是PostgreSQL中数组元素的下标是从1开始n结束，格式如：[n]。对于一维或更多维数组，查询一个数组的部分数据查询格式为：[脚标界上:脚标下界]。
数组下标定位查询及返回结果：
# SELECT name, schedule FROM array_tmp WHERE schedule[1][1]='value 11';
name | schedule
--------+---------------------------------------------------
freeoa | {{"value 11","value 12"},{"value 21","value 22"}}
查询多维数据的部分数据，如查询单维字段pay_by_quarter的第2,3列数据，及多维列schedule的第1维的1列数据和第2维第一列数据。SQL语句及查询结果如下：
# SELECT pay_by_quarter[2:3],schedule[1:2][1:1] FROM array_tmp;
pay_by_quarter | schedule
----------------+-----------------------------
{2,3} | {{"include {"},{2value}}
{2,3} | {{"value 11"},{"value 21"}}
6、复合类型
PostgreSQL复合类型描述一行或者一条记录的结构，实际上它只是一个字段名和它们的数据类型的列表。在PostgreSQL中你可以像使用简单数据类型那样使用复合类型。
1.复合类型声明
复合类型只能声明字段名字和类型，不能声明约束(比如 NOT NULL 这样的)。使用复合类型需要先定义，之后在创建表的语句中引用定义的复合类型。示例如下：
CREATE TYPE student_lesson AS (
name text,
times numeric
);
CREATE TABLE students (
name text,
lesson student_lesson
);
2.复合类型数据插入
复合类型数据插入要以文本常量书写复合类型值，在圆括弧里包括字段值并且用逗号分隔，如果值本身包含逗号或者圆括弧，刚需要用双引号括起来。 复合类型常量的一般格式如下：
'( val1 , val2 , ... )'
例如，在上一步创建的表中插入数据：
# INSERT INTO students VALUES('freeoa','("math", 3)');INSERT INTO students VALUES('liuht',ROW('math', 5));
INSERT 0 1
# SELECT * FROM students;
name | lesson
--------+----------
freeoa | (math,3)
liuht | (math,5)
3.复合类型查询
要访问复合类型字段的一个子字段，需要按字段名.子字段的形式查询，非常类似从一个表名字里查出一个字段。为避免分析器混淆，需要在查询的复合类型字段外增加圆括弧。示例如下：
#SELECT lesson.name FROM students;
ERROR: missing FROM-clause entry for table "lesson" --不加圆括号会按表查询，查询失败
LINE 1: SELECT lesson.name FROM students;
^
# SELECT (lesson).name FROM students; --正确的查询方法
name
------
math
math
如查在查询中需要使用表名，并按复合类型的子字段进行筛选。可参考下面的示例进行查询：
# SELECT students.name, (students.lesson).name FROM students WHERE (students.lesson).times>3;
name | name
-------+------
liuht | math
(1 row)
4.复合类型数据修改
修改复合类型数据时，可以整体更新复合类型字段，也可更新复合类型字段的子字段。示例如下：
# UPDATE students SET lesson = ROW('Hto', 6) WHERE name = 'hto'; --整字段更新
UPDATE 1
# UPDATE students SET lesson.times = 7 WHERE name = 'freeoa'; --子字段更新
UPDATE 1
#SELECT * FROM students;
name | lesson
--------+-----------
liuht | (Hto,6)
freeoa | (math,7)
---------------------------------------------------------------
各数据类型的操作函数
数学函数(绝对值函数、圆周率函数、求余函数、平方根函数)
PostgreSQL中提供了很多简单易用且功能强大的函数，通过这些函数可以极大的提高用户管理数据库的效率。PostgreSQL中包括：数学函数、字符串函数、日期时间函数、系统及加密函数和条件判断函数等。本篇将介绍PostgreSQL数学函数中的：绝对值函数、圆周率函数、求余函数、平方根函数。
1. 绝对值函数：ABS(x)
ABS(x)会返回x的绝对值。
例如，计算-2、3､-2.2的绝对值：
SELECT ABS(-2), ABS(3), ABS(-2.2);
返回值如下:
abs | abs | abs
-----+-----+-----
2 | 3 | 2.2
对于绝对值的计算：正数会返回其本身，所以3的绝对值还是3；负数的绝对值会是其相反的数，所以-2和-2.2分别返回2和2.2。
2. 圆周率函数：PI()
PI()会返回圆周率π的什，默认以15位小数的格式显示。例如：
SELECT PI();
其返回值如下：
pi
------------------
3.14159265358979
3. 求余函数：MOD(x,y)
MOD(x,y)会返回x除y后的余数，MOD()对于带有小数值的数据也同样适用。
例如，对MOD(21,8)、MOD(167,10)、MOD(89.8,4)做求余运算：
SELECT MOD(21,8),MOD(167,10),MOD(89.8,4);
返回值如下：
mod | mod | mod
-----+-----+-----
5 | 7 | 1.8
4. 平方根函数：SQRT(x)
SQRT(x)会返回x的非负平方根。
例如，求16和50的平方根：
SELECT SQRT(16), SQRT(50);
返回值如下：
sqrt | sqrt
------+------------------
4 | 7.07106781186548
数学函数(符号函数、四舍五入函数、整数函数)
1. 符号函数：SIGN(x)
SIGN(x)会返回x参数的符号。
例如，使用SIGN函数返回－11､0和11的符号：
SELECT SIGN(-11), SIGN(0), SIGN(11);
返回值如下:
sign | sign | sign
------+------+------
-1 | 0 | 1
用SIGN()函数计算符号时，当其为负数、0或正数时，其返回结果分别是-1、0和1
2. 四舍五入函数：ROUND(x)、ROUND(x,y)
ROUND(x)会返回对x进行四舍五入后的整数。
例如，分别对3.14、-3.14、2.56进行四舍五入运算：
SELECT ROUND(3.14), ROUND(-3.14), ROUND(2.56);
其返回值如下：
round | round | round
-------+-------+-------
3 | -3 | 3
ROUND(x,y)会返回对x进行四舍五入计算，其保留小数位为y。如果y是负数，则会保留x到小数点左边的y位。
例如，对3.14进行1位、0位和-1位四舍五入运算：
SELECT ROUND(3.14,1), ROUND(3.14,0), ROUND(3.14,-1);
其返回值如下：
round | round | round
-------+-------+-------
3.1 | 3 | 0
3. 整数函数：CEIL(x)、CEILING(x)、FLOOR(x)
CEIL(x)和CEILING(x)作用相同，都会返回一个不小于x位的最小整数，返回值会被转换成一个BIGIN类型。
例如，使用CEIL函数和CEILING计算3.14和-3.14的最小整数：
SELECT CEIL(3.14), CEIL(-3.14), CEILING(3.14), CEILING(-3.14);
返回值如下：
ceil | ceil | ceiling | ceiling
------+------+---------+---------
4 | -3 | 4 | -3
FLOOR(x)会返回一个不大于x位的最小整数，返回值会被转换成一个BIGIN类型。
例如，使用FLOOR函数计算3.14和-3.14的最大整数值：
SELECT FLOOR(3.14), FLOOR(-3.14);
返回值如下：
floor | floor
-------+-------
3 | -4
4.数字截取函数：trunc
trunc()函数返回一个数截断到一定的小数位数，用于截断数字，而不是日期。date_trunc()函数用于截断时间戳。
语法：trunc(number,[ decimal_places ])
参数：要截断的数字，要截断的小数位数；decimal_places可选的，该值必须是正整数或负整数，默认为0。
注意：如果decimal_places是负数，则trunc函数将使小数位0值左侧的数字成为数字。
适用于：trunc函数可用于以下PostgreSQL 8.4以上的版本。要将时间截断为小时，则可以使用：
date_trunc('hour', date)
如果你想截断当天则可以使用它：
date_trunc('day', date)
函数 trunc() 需要一个数字或双精度条目：
trunc ( numeric ) → numeric
trunc ( double precision ) → double precision
或者如果需要设置小数的情况，它必须是一个数字：
trunc ( v numeric, s integer ) → numeric
select trunc(123.45, 1);
123.4 正数1表示小数点的个数
select trunc(123.45, -2);
100
select trunc(123.45, -3);
0，-3表示最小值从1000算起，不足1000的则视为零
数学函数(角度转弧度函数、弧度转角度函数、幂运算函数、对数函数)
1. 角度转弧度函数：RADIANS(x)
RADIANS(x)会将x的角度转换为弧度。
例如，计算90度角转换为弧度的值：
SELECT RADIANS(90);
返回值如下:
radians
-----------------
1.5707963267949
2. 弧度转角度函数：DEGREES(x)
DEGREES(x)会将x的弧度转换为角度。例如：
SELECT DEGREES(PI());
其返回值如下：
degrees
---------
180
3. 幂运算函数：POW(x,y)、POWER(x,y)、EXP(x)
POW(x,y)和POWER(x,y)函数会返回x的y次方的计算值。例如，分别使用POW和POWER函数计算4的3次方：
SELECT POW(4,3), POWER(4,3);
返回值如下：
pow | power
-----+-------
64 | 64
EXP(x)会返回e的x次方。例如：
SELECT EXP(3);
返回值如下：
exp
------------------
20.0855369231877
4. 对数函数：LOG(x)
LOG(x)会返回x的对数。在计算对数时x不能是负数，非常将会出错。
例如，求3的对数：
SELECT LOG(3);
返回值如下：
log
-------------------
0.477121254719662
数学函数(正弦函数、反正弦函数、余弦函数、反余弦函数)
1. 正弦函数：SIN(x)
SIN(x)会返回x的正弦值，x为弧度。
例如，使用SIN函数计算正弦值：
SELECT SIN(1), ROUND(SIN(PI()));
返回值如下:
sin | round
-------------------+-------
0.841470984807897 | 0
2. 反正弦函数：ASIN(x)
ASIN(x)会将x的反正弦值，即：正弦为x的值。x的取值范围为-1~1，超出范围会报错。例如，使用ASIN函数计算反正弦值：
SELECT ASIN(1), ASIN(2);
其返回值如下：
asin | asin
-----------------+------
1.5707963267949 | NaN
3. 余弦函数：COS(x)
COS(x)会返回x的余弦值，x为弧度。例如，计算COS函数计算余弦值：
SELECT COS(0), COS(1), COS(PI());
返回值如下：
cos | cos | cos
-----+------------------+-----
1 | 0.54030230586814 | -1
4. 反余弦函数：ACOS(x)
ACOS(x)会返回x的反余弦值，即：余弦值是x的值。x的取值范围为-1~1，超出范围会报错。例如，使用ACOS函数计算反余弦值：
SELECT ACOS(1), ACOS(0.54030230586814), ACOS(0);
返回值如下：
acos | acos | acos
------+------+-----------------
0 | 1 | 1.5707963267949
字符串函数(字符串截取函数、字符串倒序函数、字符串位置查找函数)
Postgre字符串处理函数中的：字符串截取函数、字符串倒序函数、字符串位置查找函数，可分别对字符串数据进行截取、倒序及查找子字符串在字符串中的位置。
1. 字符串截取函数：SUBSTRING(s, n, len)
SUBSTRING(s, n, len)
SUBSTRING()函数用于字符串的截取操作，它会从字符串s的位置n开始截取长度len的子字符串。
例如，使用SUBSTRING函数截取一个子字符串：
SELECT SUBSTRING('freeoa.net', 1, 6);
返回值如下:
substring
-----------
freeoa
2. 字符串倒序函数：REVERSE(s)
REVERSE(s)
REVERSE()是字体串倒序函数，它后将字符串s的字符顺序进行反转。
例如，使用REVERSE函数将字符串倒序：
SELECT REVERSE('freeoa.net');
返回值如下：
reverse
------------
ten.aoeerf
3. 字符串位置查找函数：POSITION(s1 IN s)
POSITION(s1 IN s)
POSITION()函数用于查找子字符串s1在字符串s中的位置。这个函数非常有用，如果需要对字符进行相关操作时需要操作字符或字符串的位置，就要用这个函数查找字符位置。
例如，使用POSITION查找字符串位置：
SELECT SUBSTRING('xxxxxxxfreeoa.netxxxxx', POSITION('freeoa.net' IN 'xxxxxxxfreeoa.netxxxxx'), 10);
返回值如下：
substring
------------
freeoa.net
字符串函数(字符串替换函数、字符移除函数、生成重复字符串函数)
Postgre中提供了字符串替换函数、字符串中指定字符移除函数、生成重复字符串函数，使用这些函数可以方便的对数据库中的字符串数据进行字符替换、字符移除及生成重复字符串的操作。
1. 字符串替换函数：REPLACE(s, s1,s2)
REPLACE(s, s1,s2)
REPLACE()函数用于字符串中字符的替换操作，它会将字符串s中的所有s1替换为s2
例如，使用REPLACE函数替换字符串：
SELECT REPLACE('freeoa.netm', 'netm', 'net');
返回值如下:
replace
------------
freeoa.net
2. 字符串移除函数：TRIM(s1 FROM s)
TRIM(s1 FROM s)
TRIM()函数用于删除字符串两端的空格，添加FROM表达式后可用于删除字符串两端的指定字符s1。
例如，使用TRIM函数进行字符串删除操作：
SELECT TRIM('lu' FROM 'lufreeoa.netlu');
其返回值如下：
btrim
------------
freeoa.net
3. 生成重复字符串函数：REPEAT(s, n)
REPEAT(s, n)
REPEAT(s, n)<()函数将字符串s重复生成n次。
例如，使用REPEAT()生成重复字符串：
SELECT REPEAT('freeoa.net ', 3);
返回值如下：
repeat
-----------------------------------
freeoa.net freeoa.net freeoa.net
(1 row)
字符串函数(字符串合并函数、字符串添充函数、删除字符串空格函数)
在数据库中处理字符串数据时时，我们有时会对字符串进行合并、添充或删除空格式等操作，PostgreSQL中提供了相应的字符串处理函数。
1. 字符串合并函数：CONCAT(s1,s2,……)、CONCAT_WS(x,s1,s2,……)
CONCAT(s1,s2,……)
CONCAT()函数会将多个字符串参数连接成一个字符串。如果其中一个参数为NULL时，则返回结果也是NULL，其值会被忽略。如果其一个参数为二进制字符串，则返回结果也是二进制字符串。
例如，使用CONCAT函数连接字符串：
SELECT CONCAT('freeoa','.net'), CONCAT('freeoa','.net', NULL), CONCAT('freeoa', '.net', '\\000'::bytea);
返回值如下:
concat | concat | concat
------------+------------+----------------------
freeoa.net | freeoa.net | freeoa.net\x5c303030
CONCAT_WS(x,s1,s2,……);
CONCAT_WS()(concat with separator)是CONCAT()函数的特殊形式，CONCAT_WS()函数连接字符串时，可以指定一个分隔符x。
例如，使用CONCAT_WS函数连接字符串，并指定分隔符：
# SELECT CONCAT_WS('.', 'freeoa', 'net'), CONCAT_WS('.', 'freeoa','net', NULL), CONCAT_WS('.', 'freeoa', 'net', '\\000'::bytea);
返回值如下:
concat | concat | concat
------------+------------+----------------------
freeoa.net | freeoa.net | freeoa.net\x5c303030
2. 字符串添充函数：LPAD(s1, len, s2)、RPAD(s1, len, s2)
LPAD(s1, len, s2);
RPAD(s1, len, s2);
LPAD()函数和RPAD()函数都会使用字符串s2对s1进行添充，添充指定长度是len，两个函数的添充方向分别是左侧和右侧。如果s1长度大于len，则会被切隔至指定长度。
例如，使用LPAD函数和RPAD函数进行字符串添充操作：
SELECT LPAD('freeoa', 10, '.net'), RPAD('freeoa', 10, '.net');
其返回值如下：
lpad | rpad
------------+------------
.netfreeoa | freeoa.net
3. 删除字符串空格函数：LTRIM(s)、RTRIM(s) 、TRIM(s)
LTRIM(s);
RTRIM(s);
TRIM(s);
LTRIM()、RTRIM() 、TRIM()三个函数分别会去除字符串左侧、右侧和两侧的空格。
例如，使用LTRIM()、RTRIM() 、TRIM()函数去除字符串空格：
SELECT CONCAT(LTRIM(' freeoa '), '.net'), CONCAT(RTRIM(' freeoa '), '.net'), CONCAT(TRIM(' freeoa'), '.net');
返回值如下：
concat | concat | concat
-------------+-------------+------------
freeoa .net | freeoa.net | freeoa.net
字符串函数(计算字符串字符数函数、计算字符串长度函数、获取指定长度字符串函数)
在对数据库中的数据进行处理时，很多情况下会涉及对字符串数据的处理。在PostgreSQL中，处理字符串的函数有很多，本篇介绍：计算字符串字符数函数、计算字符串长度函数、获取指定长度字符串函数。
1. 计算字符串字符数函数：CHAR_LENGTH(x)
CHAR_LENGTH(x)
CHAR_LENGTH(x)会返回字符串x的字符中数。
例如，使用CHAR_LENGTH函数计算字符串的字符数：
SELECT CHAR_LENGTH('aaa'), CHAR_LENGTH('freeoa.net');
返回值如下:
char_length | char_length
-------------+-------------
3 | 10
2. 计算字符串长度函数：LENGTH(x)
LENGTH(X)
LENGTH(x)会返回字符串x的字节长度。
例如，使用LENGTH函数计算字符串的字节长度：
SELECT LENGTH('aaa'), LENGTH('freeoa.net');
其返回值如下：
length | length
--------+--------
3 | 10
LENGTH()函数计算字符串字节长度时，utf-8编码的字符串，一个汉字会占用3个字节长度，一个字母或数字点用一个字节长。上面两个示例CHAR_LENGTH()函数和LENGTH()函数计算值相同，就是因为英文字符所点的字节数相同，都是一个字节。
3. 获取指定长度字符串函数：LEFT(x)、RIGHT(x)
LEFT(x);
RIGHT(x);
获取指定长度字符串可以使用LEFT(x)函数从字符串左侧截取，也可以使用RIGHT(x)函数从字符串右侧截取。例如，LEFT(x)和RIGHT(x)函数对freeoa.com左侧和右侧截取：
SELECT LEFT('freeoa.net', 6), RIGHT('freeoa.net', 4);
返回值如下：
left | right
--------+-------
freeoa | .net
日期时间函数(使用EXTRACT函数获取日期中的指定值)
在PostgreSQL的日期时间函数中，有一个EXTRACT函数，使用该函数可以方便的获取日期中的某一部分值，如：日期、月份、年、年中的第几天等。
1. 语法格式
EXTRACT(type FROM d)
EXTRACT函数用于提取日期d中的指定类型type的值，type可以是以下值之一：
值：描述
DAY：返回日期值
MONTH：返回月份值
YEAR：返回年份值
DOY(day of year)：返回年中的第几天
DOW(day of week)：返回星期几
QUARTER：返回季度
2. EXTRACT使用示例
使用EXTRACT函数查询日期值、月份、年份等：
SELECT EXTRACT(DAY FROM NOW()), EXTRACT(MONTH FROM NOW()), EXTRACT(YEAR FROM NOW()), EXTRACT(DOY FROM NOW()), EXTRACT(DOW FROM NOW()), EXTRACT(QUARTER FROM NOW());
其返回值如下：
date_part | date_part | date_part | date_part | date_part | date_part
-----------+-----------+-----------+-----------+-----------+-----------
15 | 11 | 2015 | 319 | 0 | 4
日期时间函数(获取当前日期时间函数、获取当前日期的函数、获取当前时间的函数)
日期时间函数用于处理数据库中的日期和时间值，在PostgreSQL中用于表示日期和时间的数据类型有：DATE类型、TIME类型、DATETIME类型和TIMESTAMP类型，详见：PostgreSQL日期时间类型。
1. 获取当前日期时间函数：CURRENT_TIMESTAMP、LOCALTIMESTAMP、NOW()
CURRENT_TIMESTAMP;
LOCALTIMESTAMP;
NOW();
PostgreSQL提供了三个用于获取当前日期时间的函数：CURRENT_TIMESTAMP、LOCALTIMESTAMP、NOW()，返回格式为：YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS。使用时可以根据不用的应用场景使用不同的函数。
例如，使用日期时间函数获取当前日期时间：
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, NOW();
返回值如下:
now | timestamp | now
-------------------------------+----------------------------+-------------------------------
2019-08-11 14:55:46.298786+08 | 2019-08-11 14:55:46.298786 | 2019-08-11 14:55:46.298786+08
LOCALTIMESTAMP与另外两个函数略有区别，这个函数返回的是不带时区的时间。
2. 获取当前日期的函数：CURRENT_DATE
CURRENT_DATE
计算日期时间时，如果仅需要当前的日期，可以使用CURRENT_DATE()。日期返回格式是YYYY-MM-DD，但具体返回格式和使用环境有关。例如，使用CURRENT_DATE函数返回当前日期：
SELECT CURRENT_DATE;
返回值如下：
date
------------
2019-08-11
3. 获取当前时间的函数：CURRENT_TIME
CURRENT_TIME
CURRENT_TIME函数会返回当前系统时间，在操作日期时间时，如果仅需要当前时间使用这个函数会很方便。其默认返回格式是HH:MM:SS，但具体返回格式和使用环境有关。
例如，使用CURRENT_TIME函数返回当前时间：
SELECT LOCALTIME;
返回值如下：
time
-----------------
21:17:01.436793
函数之用户、用户连接信息与系统版本
在PostgreSQL中，你可使用系统函数查看数据库的版本号、用户名、连接数等，或使用用户名函数查看当前登录用户的用户名等信息。
1. 查看系统版本信息
VERSION()
通过VERSION()可以查看当前PostgreSQL数据库服务器的版本信息等
SELECT VERSION();
返回值如下:
PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (CRUX-x86_64-multilib) 5.2.0, 64-bit
2. 用户信息查看
USER
CURRENT_USER
USER和CURRENT_USER函数可以查看当前PostgreSQL的登录用户信息。
SELECT USER, CURRENT_USER;
返回值如下:
current_user | current_user
--------------+--------------
postgres | postgres
3. 查看用户连接信息
pg_stat_activity
pg_stat_activity是一个PostgreSQL内部视图，可以用于查看当前连接的数据库名，用户，IP地址，连接开始时间，查询的语句等。
SELECT * FROM pg_stat_activity;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change
| wait_event_type | wait_event | state | backend_xid | backend_xmin | query
-------+---------+------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-----------------------------
--+-----------------+------------+--------+-------------+--------------+---------------------------------
16385 | pdb | 1773 | 16384 | dba | psql | 172.18.0.8 | | 38899 | 2019-08-11 14:37:43.050681+08 | 2019-08-11 14:41:48.728643+08 | 2019-08-11 14:41:48.728643+08 | 2019-08-11 14:41:48.728647+0
8 | | | active | | 118712 | SELECT * FROM pg_stat_activity;
函数之加密、编/解码函数
对于存入数据库的数据，我们可能会需要进行加密，而从数据库读出数据时，又需要数据进行解密。PostgreSQL提供了几个加密、解密函数，可以帮我们完成相关操作。
1. MD5加密函数
MD5(str)
MD5()用于计算字符串的MD5加密值，传入NULL时其返回值也是NULL。注意：MD5加密是不可解密的，这在用户密码等使用方面非常有用，但如果需要对加密值进行解密这种加密方式就不太适用。
SELECT MD5('freeoa.net');
返回结果：
40c0323aa7b9b6d135768f1e40b67020
2. ENCODE编码
ENCODE(str, paswd_str)
ENCODE()是一个编码函数，该函数可以指定一个编码paswd_str来对字符串编码。可接受的编码方式有：base64、hex、escape。
SELECT ENCODE('freeoa.net', 'base64');
其返回值如下：
encode
------------------
ZnJlZW9hLm5ldA==
3. DECODE编码
DECODE(str, paswd_str)
对于已使用ENCODE()编码的函数，可以使用DECODE()函数对其进行解码，可接受的解码方式与编码方式对应有：base64、hex、escape。
SELECT DECODE('ZnJlZW9hLm5ldA==','base64');
返回值如下：
decode
------------------------
freeoa.net
函数之流程控制
流程控制语句会根据不同的条件执行不同的流程。在PostgreSQL中，流程控制使用CASE和CASE WHEN语句实现。
1. CASE条件判断与流程控制
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[ELSE else_result_expression]
END
CASE函数会判断表达式input_expression的值，并根据判断结果执行对应的WHEN分支流程。
CASE使用示例：
SELECT CASE 1+1 WHEN 1 THEN 'net.freeoa' WHEN 3 THEN 'freeoa' ELSE 'freeoa.net' END;
返回值如下:
case
------------
net.freeoa
2. CASE THEN条件判断与流程控制
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[ELSE else_result_expression]
END
CASE THEN语句与CASE语句类似，其条件判断语句Boolean_expression是一个计算结果是布尔值的表达式。
CASE THEN使用示例：
SELECT CASE WHEN 1>2 THEN 'net.freeoa' ELSE 'freeoa.net' END;
返回值如下：
case
------------
freeoa.net
函数之集合查询：AVG、COUNT、MAX、MIN、SUM
在数据统计的时候，大多不需要返回详细数据，而只是对数据进行一些分析和汇总。在PostgreSQL中，提供了一些查询函数，可以方便的对数据进行分析、汇总等，如：统计数据总数，对某个字段进行总和查询，最大值、最小值、平均值的判断等。下面是PostgreSQL中一些常用的查询函数。
函数名 函数功能
AVG() 计算平均值
COUNT() 计算总行数
MAX() 计算最大值
MIN() 计算最小值
SUM() 计算和
1. 使用AVG()计算平均值
AVG()函数用于计算指定列数据的平均值。例如，从产品表products中查询平均价格：
# SELECT AVG(price) avg_price FROM products;
AVG()函数查询时，可以指定WHERE条件及GROUP BY子句，进行分组查询。例如，查询某几个分类sort_id的平均价格：
# SELECT sort_id, AVG(price) avg_price FROM products WHERE sort_id in(1,2,3) GROUP BY sort_id;
# 查询结果如下
sort_id | avg_price
---------+-----------------------
1 | 10.0000000000000000
2 | 5088.0000000000000000
3 | 2288.0000000000000000
2. 使用COUNT()计算总行数
COUNT()函数用于计算表的总行数或某列包含数据的总行数。该函数有以下两种用途：
COUNT(*)，计算表的总行数
COUNT(字段名)，计算表中某列的总行数，计算时会忽略字段值为空的行
例如，计算产品表products的总行数：
# SELECT COUNT(*) FROM products;
count
-------
4
例如，计算产品表products，价格price不为空总行数：
# SELECT COUNT(price) FROM products;
count
-------
3
COUNT()函数查询时，也可以指定WHERE条件及GROUP BY子句，进行分组查询。例如，查询某几个分类sort_id下各自的行数：
# SELECT sort_id, COUNT(*) FROM products WHERE sort_id IN(2, 3, 4) GROUP BY sort_id;
sort_id | count
---------+-------
2 | 1
3 | 1
4 | 1
3. 使用MAX()计算最大值
MAX()函数用于返回指定列中的最大值。例如，查询产品表products中价格最高的产品：
# SELECT MAX(price) FROM products;
max
------
5088
MAX()函数查询时，也可以指定WHERE条件及GROUP BY子句以查询分组中的。例如，查询某几个分类sort_id下的最大价格：
# SELECT sort_id, MAX(price) FROM products WHERE sort_id in(1, 2, 3) GROUP BY sort_id;
sort_id | max
---------+------
1 | 10
2 | 5088
3 | 2288
4. 使用MIN()计算最小值
MIN()函数用于返回指定列中的最小值。例如，查询产品表products中价格最低的产品：
# SELECT MIN(price) FROM products;
min
-----
10
MIN()函数查询时，也可以指定WHERE条件及GROUP BY子句以查询分组中的。例如，查询某个分类sort_id下的最低价格：
# SELECT sort_id sort_id, MIN(price) FROM products WHERE sort_id = 2 GROUP BY sort_id;
sort_id | min
---------+------
2 | 5088
5. 使用SUM()计算总和
前面介绍COUNT()函数可以返回总行数，如果要返回指定列的总和就需要使用SUM()函数。例如，计算产品表products的总价格：
# SELECT SUM(price) FROM products;
sum
------
7386
与前面介绍的其它分组查询函数一样，SUM()函数也可以在查询时指定WHERE条件及GROUP BY子句以查询分组中的。例如，查询某个分类sort_id下的最低价格：
# SELECT sort_id, SUM(price) FROM products WHERE sort_id = 3 GROUP BY sort_id;
sort_id | sum
---------+------
3 | 2288
网友pmars整理的PostgreSQL常见字符串操作函数
函数：string || string
说明：String concatenation(字符串连接操作)
例子：select 'Post' || 'greSQL';#PostgreSQL
函数：string || non-string or non-string || string
说明：String concatenation with one non-string input(字符串与非字符串类型进行连接操作)
例子：select 'Value: ' || 42;#Value: 42
函数：bit_length(string)
说明：Number of bits in string(计算字符串的位数)
例子：select bit_length('pmars')#40
函数：char_length(string) or character_length(string)
说明：Number of characters in string(计算字符串中字符个数)
例子：select char_length('pmars');#5
函数：lower(string)
说明：Convert string to lower case(转换字符串为小写)
例子：select lower('PmArS');#"pmars"
函数：octet_length(string)
说明：Number of bytes in string(计算字符串的字节数)
例子：select octet_length('我是pmars');#11
select octet_length('我'); #3
函数：overlay(string placing string from int [for int])
说明：Replace substring(替换字符串中任意长度的子字串为新字符串)
例子：select overlay('I am pmars' placing 'ming' from 6 for 5);#"I am ming"
函数：position(substring in string)
说明：Location of specified substring(子串在一字符串中的位置)
例子：select position('ma' in 'pmars');#2
函数：substring(string [from int] [for int])
说明：Extract substring(截取任意长度的子字符串)
例子：select substring('topmars' from 3 for 3);#"pma"
函数：substring(string from pattern)
说明：Extract substring matching POSIX regular expression. See Section 9.7 for more information on pattern matching(利用正则表达式对一字符串进行任意长度的字串的截取)
例子：select substring('topmars' from 'p.*$');#"pmars"
函数：substring(string from pattern for escape)
说明：Extract substring matching SQL regular expression. See Section 9.7 for more information on pattern matching(利用正则表达式对某类字符进行删除，以得到子字符串)
例子：select substring('Thomas' from '%#"o_a#"_' for '#');#"oma"
函数：trim([leading | trailing | both] [characters] from string)
说明：Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string(去除尽可能长开始，结束或者两边的某类字符，默认为去除空白字符，当然可以自己指定，可同时指定多个要删除的字符串)
例子：select trim(leading 'p' from 'pmars');#"mars"
函数：upper(string)
说明：Convert string to uppercase(将字符串转换为大写)
例子：select upper('pmars');#"PMARS"
函数：ascii(string)
说明：ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings. the argument must be a strictly ASCII character(得到某一个字符的Ascii值)
例子：select ascii('pmars');select ascii('p');#112
函数：btrim(string text [, characters text])
说明：Remove the longest string consisting only of characters in characters (a space by default) from the start and end of string(去除字符串两边的所有指定的字符，可同时指定多个字符)
例子：select btrim('pmars','prs');#"ma"
函数：chr(int)
说明：Character with the given code. For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate a strictly ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes(得到某ACSII值对应的字符)
例子：select chr(65);#A
函数：convert(string bytea, src_encoding name, dest_encoding name)
说明：Convert string to dest_encoding. The original encoding is specified by src_encoding.
The string must be valid in this encoding.Conversions can be defined by CREATE CONVERSION. Also there are some predefined conversions. See Table 9-7 for available conversions(转换字符串编码，指定源编码与目标编码)
例子：select convert('我是pmars_in_utf8', 'UTF8', 'GBK');#"\316\322\312\307pmars_in_utf8"
函数：convert_from(string bytea, src_encoding name)
说明：Convert string to the database encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding(转换字符串编码，自己要指定源编码，目标编码默认为数据库指定编码)
例子：select convert_from('\316\322\312\307pmars','GBK');#"我是pmars"
函数：convert_to(string text, dest_encoding name)
说明：Convert string to dest_encoding(转换字符串编码，源编码默认为数据库指定编码，自己要指定目标编码)
例子：select convert_to('我是pmars_in_utf8','GBK');#"\316\322\312\307pmars_in_utf8"
函数：decode(string text, type text)
说明：Decode binary data from string previously encoded with encode. Parameter type is same as in encode(对字符串按指定的类型进行解码)
例子：select decode('MTIzAAE=', 'base64');#"123\000\001"
函数：encode(data bytea, type text)
说明：Encode binary data to different representation. Supported types are: base64, hex, escape. Escape merely outputs null bytes as \000 and doubles backslashes(与decode相反，对字符串按指定类型进行编码)
例子：select encode('123\000\001','base64');#"MTIzAAE="
函数：initcap(string)
说明：Convert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters(将字符串所有的单词进行格式化，首字母大写，其它为小写)
例子：select initcap('I AM PMARs');#"I Am Pmars"
函数：length(string)
说明：Number of characters in string(讲算字符串长度)
例子：select length('我是pmars');#7
函数：length(stringbytea, encoding name )
说明：Number of characters in string in the given encoding.
The string must be valid in this encoding(计算字符串长度，指定字符串使用的编码)
例子：select length('我是pmars','GBK');#8
函数：lpad(string text, length int [, fill text])
说明：Fill up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right)(对字符串左边进行某类字符自动填充，即不足某一长度，则在左边自动补上指定的字符串，直至达到指定长度，可同时指定多个自动填充的字符)
例子：select lpad('pmars', 10, 'to');#"tototpmars"
函数：ltrim(string text [, characters text])
说明：Remove the longest string containing only characters from characters (a space by default) from the start of string(删除字符串左边某一些的字符，可以时指定多个要删除的字符)
例子：select ltrim('pmars','amp');#"rs"
函数：md5(string)
说明：Calculates the MD5 hash of string, returning the result in hexadecimal(将字符串进行md5编码)
例子：select md5('freeoa');#"53df37d49955e1bf849dd295e5e8393f"
函数：pg_client_encoding()
说明：Current client encoding name(得到pg客户端编码)
例子：select pg_client_encoding();#"UTF8"
函数：quote_ident(string text)
说明：Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled(对某一字符串加上两引号)
例子：quote_ident('Foo bar')#"Foo bar"
函数：quote_literal(string text)
说明：Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled(对字符串里两边加上单引号，如果字符串里面出现sql编码的单个单引号，则会被表达成两个单引号)
例子：quote_literal('O\'Reilly')#'O''Reilly'
函数：quote_literal(value anyelement)
说明：Coerce the given value to text and then quote it as a literal. Embedded single-quotes and backslashes are properly doubled(将一数值转换为字符串，并为其两边加上单引号，如果数值中间出现了单引号，也会被表示成两个单引号)
例子：quote_literal(42.5)#'42.5'
函数：regexp_matches(string text, pattern text [, flags text])
说明：Return all captured substrings resulting from matching a POSIX regular expression against the string. See Section 9.7.3 for more information(对字符串按正则表达式进行匹配，如果存在则会在结果数组中表示出来)
例子：regexp_matches('foobarbequebaz', '(bar)(beque)')#{bar,beque}
函数：regexp_replace(string text, pattern text, replacement text [, flags text])
说明：Replace substring(s) matching a POSIX regular expression. See Section 9.7.3 for more information(利用正则表达式对字符串进行替换)
例子：regexp_replace('Thomas', '.[mN]a.', 'M')#ThM
函数：regexp_split_to_array(string text, pattern text [, flags text ])
说明：Split string using a POSIX regular expression as the delimiter. See Section 9.7.3 for more information(利用正则表达式将字符串分割成数组)
例子：regexp_split_to_array('hello world', E'\\s+')#{hello,world}
函数：regexp_split_to_table(string text, pattern text [, flags text])
说明：Split string using a POSIX regular expression as the delimiter. See Section 9.7.3 for more information(利用正则表达式将字符串分割成表格)
例子：regexp_split_to_table('hello world', E'\\s+')
hello
world
函数：repeat(string text, number int)
说明：Repeat string the specified number of times(重复字符串一指定次数)
例子：repeat('Pg', 4)#PgPgPgPg
函数：replace(string text, from text, to text)
说明：Replace all occurrences in string of substring from with substring to(将字符的某一子串替换成另一子串)
例子：replace('abcdefabcdef', 'cd', 'XX')#abXXefabXXef
函数：rpad(string text, length int [, fill text])
说明：Fill up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated(对字符串进行填充，填充内容为指定的字符串)
例子：rpad('hi', 5, 'xy')#hixyx
函数：rtrim(string text [, characters text])
说明：Remove the longest string containing only characters from characters (a space by default) from the end of string(去除字符串右边指定的字符)
例子：rtrim('trimxxxx', 'x')#trim
函数：split_part(string text, delimiter text, field int)
说明：Split string on delimiter and return the given field (counting from one)(对字符串按指定子串进行分割，并返回指定的数值位置的值)
例子：split_part('abc-def-ghi', '-', 2)#def
函数：strpos(string, substring)
说明：Location of specified substring (same as position(substring in string),but note the reversed argument order)(指定字符串在目标字符串的位置)
例子：strpos('high', 'ig')#2
函数：substr(string, from [, count])
说明：Extract substring (same as substring(string from from for count))(截取子串)
例子：substr('alphabet', 3, 2)#ph
函数：to_ascii(string text [, encoding text])
说明：Convert string to ASCII from another encoding(only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings)(将字符串转换成ascii编码字符串)
例子：to_ascii('Karel')#Karel
函数：to_hex(number int or bigint)
说明：Convert number to its equivalent hexadecimal representation(对数值进行十六进制编码)
例子：to_hex(2147483647)#7fffffff
函数：translate(string text, from text, to text)
说明：Any character in string that matches a character in the from set is replaced by the corresponding character in the to set(将字符串中某些匹配的字符替换成指定字符串，目标字符与源字符都可以同时指定多个)
例子：translate('12345', '14', 'ax')#a23x5
网友钱魏Way的SQL日期时间处理函数节选
数据库中常见的日期/时间存储格式：
Unix时间戳(10位，单位为秒，1970年1月1日到现在的秒数)：1622606037
Unix时间戳(13位，单位为微秒)：1622606037123
DATE类型的日期：2021-06-02
字符串类型日期：2021-06-02、2021/06/02、20210602
数值型日期：20210602
DATETIME类型时间：2021-06-02T11:53:57
PostgreSQL支持SQL中所有的日期和时间类型，如下表所示：
time、timestamp和interval接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界；p允许的范围是从 0 到 6。
interval类型有一个附加选项，它可以通过写下面之一的短语来限制存储的fields的集合：
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
注意：如果fields和p被指定，fields必须包括SECOND，因为精度只应用于秒。
PostgreSQL中with和without time zone的区别：从名字上可以看出一个是是带时区的，另一个是不带时区的，查出来的时间是一样的，只是一个带时区标志，一个不带而已，时区的基准是格林威治时间UTC。比如中国(PRC),时区是东八区,带时区标志的话就是+08。
SET timezone = "US/Pacific";
SHOW timezone; -- US/Pacific
SELECT
now()::TIMESTAMP WITH TIME ZONE, -- 2020-11-22 18:13:32.034831-08
now()::TIMESTAMP WITHOUT TIME ZONE; -- 2020-11-22 18:13:32.034831
SET timezone = PRC;
SHOW timezone; -- PRC
SELECT
now()::TIMESTAMP WITH TIME ZONE, --2020-11-23 10:13:32.037091+08
now()::TIMESTAMP WITHOUT TIME ZONE; --2020-11-23 10:13:32.037091
SET timezone = UTC;
SHOW timezone; --UTC
SELECT
now()::TIMESTAMP WITH TIME ZONE, --2020-11-23 02:13:32.040013+00
now()::TIMESTAMP WITHOUT TIME ZONE; -- 2020-11-23 02:13:32.040013
SET timezone = "Asia/Shanghai";
SHOW timezone; --Asia/Shanghai
SELECT
now()::TIMESTAMP WITH TIME ZONE, -- 2020-11-23 10:13:32.042719+08
now()::TIMESTAMP WITHOUT TIME ZONE; --2020-11-23 10:13:32.042719
备注：在PostgreSQL中，双冒号::是用于类型转换。
想要查看PostgreSQL支持的所有时区，可以通过以下SQL查看。
SELECT * FROM pg_timezone_names;
注意，在SQL中设置时区，只是临时性的修改，当关闭连接时会恢复到原来的时区，想要永久修改时区，需要修改配置文件。
PostgreSQL获取当前日期和时间
SELECT CURRENT_DATE; -- 2020-11-15
SELECT CURRENT_TIME; -- 19:53:10.138282+08
SELECT CURRENT_TIMESTAMP; --19:53:25.618691+08
SELECT CURRENT_TIME(0); -- 19:58:05+08,传入参数precision,可选值0-6,即秒后面的位数。
SELECT CURRENT_TIMESTAMP(1); -- 2020-11-15 19:58:40.7+08
SELECT LOCALTIME; -- 19:54:40.377817
SELECT LOCALTIMESTAMP; -- 2020-11-15 19:54:56.594073
SELECT LOCALTIME(0); -- 19:59:09
SELECT LOCALTIMESTAMP(1); -- 2020-11-15 19:59:19.9
SELECT now(); -- 2020-11-23 10:25:50.801659+08
PostgreSQL如何获取当前Unix时间戳？
在PG数据库中，extract函数用于从一个日期中获取某个子集，比如获取年，月，日，时，分，秒等。
SELECT now();-- 2020-11-23 10:40:28.85565+08
SELECT EXTRACT('HOUR' FROM now()); -- 10
SELECT EXTRACT('HOUR' FROM '2020-11-23 10:40:28.85565+08'::timestamp); -- 10
SELECT EXTRACT('epoch' FROM now()); -- 1606099228.856519
SELECT TO_TIMESTAMP(1606099228.856519) -- 2020-11-23 10:40:28.856519+08
新纪元时间 Epoch 是以 1970-01-01 00:00:00 UTC 为标准的时间，将目标时间与 1970-01-01 00:00:00 时间的差值以秒来计算，单位是秒，可以是负值。
EXTRACT，date_part函数支持的field：
PostgreSQL的几个日期和时间
SELECT TIMESTAMP 'yesterday'; -- 2020-11-20 00:00:00
SELECT DATE 'yesterday'; -- 2020-11-20
SELECT TIMESTAMP 'today'; -- 2020-11-21 00:00:00
SELECT DATE 'today'; -- 2020-11-21
SELECT TIMESTAMP 'tomorrow'; -- 2020-11-22 00:00:00
SELECT DATE 'tomorrow'; -- 2020-11-22
SELECT TIME 'allballs'; -- 00:00:00
SELECT now(); -- 2020-11-21 16:53:41.676354+08
SELECT TIMESTAMP 'now'; --2020-11-21 16:53:41.676898
SELECT DATE 'now'; -- 2020-11-21
PostgreSQL日期/时间格式化
PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数，它们都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是定义输出或输出格式的模板。
示例：
SELECT to_char( CURRENT_TIMESTAMP, 'HH12:MI:SS' ) -- 07:46:34
SELECT to_char( INTERVAL '15h 2m 12s', 'HH24:MI:SS' ) -- 15:02:12
SELECT to_date('05 Dec 2000', 'DD Mon YYYY') -- 2000-12-05
SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY') -- 2000-12-05 00:00:00+08
用于日期/时间格式化的模板模式
用于数值格式化的模板模式：
PostgreSQL时间/日期函数和操作符
下面是PostgreSQL中支持的时间/日期操作符的列表：
PostgreSQL支付多种数值类型，数值类型主要有用来存储数字。不同的数值类型有不同的取值范围，取值范围越大，所需的存储空间也越大。PostgreSQL提供的数值类型主要包括：整数类型、浮点数类型、任意精度类型和序号类型。
| 类型名称 | 存储空间 | 数据范围 | 说明 |
| SMALLINT | 2字节 | -32 768~32 767 | 小范围整型 |
| INT(INTEGER) | 4字节 | －2 147 483 648〜2 147 483 647 | 普通整型 |
| BIGINT | 8字节 | -9 223 372 036 854 775 808 〜 9 223 372 036 854 775 807 | 大整型 |
| REAL | 4字节 | 6 位十进制数字精度 | 浮点数类型 |
| DOUBLE PRECISION | 8字节 | 15 位十进制数字精度 | 双精度浮点数类型 |
| NUMERIC | 变长 | 任意 | 任意精度类型 |
| SERIAL | 4字节 | 1 ~ +2 147 483 647 | 自增整数 |
| BIGSERIAL | 8字节 | 1 ~ +9 223 372 036 854 775 807 | 大范围的自增整数 |
1.整数类型
整数类型包括：SMALLINT、INT(INTEGER)、BIGINT三种，三者在取值空间和存储范围上有所不同，不同的存储空间也决定了不同的查询效率。应根据实际需要选择最适合的类型，以在查询效率和存储空间上有所平衡。
2.浮点数类型
实际工作中很多地方需要用到带小数的数值，PostgreSQL使用浮点数来表示小数。浮点数类型有两种：REAL和DOUBLE PRECISION。PostgreSQL也支持使用标准的SQL语法，即：float和float(p)来声明非精确的数值类型，p表示可接受的精度。REAL类型对应float(1)~float(24)，DOUBLE PRECISION对应float(25)~float(53)，未声明精度时将被当作DOUBLE PRECISION处理。
3.任意精度类型
NUMERIC表示任意精度类型，PostgreSQL中任意精度类型可存储最多1000位精度的数字并且准确地进行计算，非常适合用于货币金额和其它要求计算准确的数据，但是，NUMERIC类型的运算速度要比整数类型或者浮点数类型要慢很多。使用格式为：NUMERIC(M,N)。其中，M称为精度，表示总位数；N称为标度，表示小数位。M和N决定了NUMERIC的取值范围，当用户数据的精度超出指定精度时，会进行四舍五入处理。
4.序号类型
序号类型SERIAL和BIGSERIAL并不是真正的类型， 只是为在表中设置唯一标识做的概念上的便利。在目前的实现中，下面一句话：
CREATE TABLE tablename (colname SERIAL);
等价于：
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename(
colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);
类型名 SERIAL 和 SERIAL4 是等效的： 两个都创建 INTEGRE 字段。类型名 BIGSERIAL 和 SERIAL8 也一样，只不过它创建一个 BIGINT 字段。 如果你预计在表的生存期中使用的标识数目超过 2^31 ，那么你应该使用 BIGSERIAL。
2、日期时间类型
PostgreSQL保存日期时间格式的数据类型有多种，主要有：TIME、DATE、TIMESTAMP、INTERVAL。日期时间类型输入像字符串类型输入一样，需要加单引号。每种日期时间类型都有合法的取值范围，超出范围时系统会将"零"插入数据记录中。
| 类型名称 | 存储空间 | 数据范围 | 说明 | 分辨率 |
| TIME | 8字节 | 00:00:00 ~ 24:00:00 | 时间类型(只用于一天内的时间)［无时区］ | 1毫秒/14位 |
| TIME[with time zone] | 12字节 | 00:00:00+1359~24:00:00-1359 | 时间类型(只用于一天内的时间)［含时区］ | 1毫秒/14位 |
| DATE | 4字节 | 4713 BC~32767 AD | 只用于一天的日期 | 1天 |
| TIMESTAMP | 8字节 | 4713 BC~5874897 AD | 日期和时间［无时区］ | 1毫秒/14位 |
| TIMESTAMP[with time zone] | 8字节 | 4713 BC~5874897 AD | 日期和时间［含时区］ | 1毫秒/14位 |
| INTERVAL | 12字节 | -178000000年~178000000年 | 时间间隔 | 1毫秒/14位 |
1.时间类型
时间类型是TIME和TIMEwith time zone，默认情况下为不带时区(即:TIME)。不带时区时间格式可接受输入方式有：HH:MM:SS、HH:MM、HHMMSS。带时间格式输入可用系统的NOW()函数。时间、时区输入参考如下。
时间输入：
| 示例 | 描述 |
| 04:05:06.789 | ISO 8601 |
| 04:05:06 | ISO 8601 |
| 04:05 | ISO 8601 |
| 040506 | ISO 8601 |
| 04:05 AM | 与 04:05 一样；AM 不影响数值 |
| 04:05 PM | 与 16:05一样；输入小时数必须 <= 12 |
| 04:05:06.789-8 | ISO 8601 |
| 04:05:06-08:00 | ISO 8601 |
| 04:05-08:00 | ISO 8601 |
| 040506-08 | ISO 8601 |
| 04:05:06 PST | 用名字声明的时区 |
时区输入：
| 示例 | 描述 |
| PST | 太平洋标准时间(Pacific Standard Time) |
| -8:00 | ISO-8601 与 PST 的偏移 |
| -800 | ISO-8601 与 PST 的偏移 |
| -8 | ISO-8601 与 PST 的偏移 |
| zulu | 某军方对 UTC 的缩写 |
| z | zulu的缩写 |
2.DATE类型
DATE类型用于仅需要日期值时，日期输入格式为：YYYY-MM-DD、YYYYMMDD。输入DATE类型数据时，可以使用字符串或数字类型的数据输入，符合DATE的日期格式即可。可以使用CURRENT_DATE或NOW()插入当前系统时间。
3. TIMESTAMP类型
时间戳类型的有效输入由一个日期和时间的联接组成，后面跟着一个可选的时区，一个可选的 AD 或者 BC。时间戳输入格式为：YYYY-MM-DD HH:MM:SS。带时区输入格式为：2019-03-22 4:05:06 -8:00
4.INTERVAL类型
INTERVAL类型输入格式如下：
quantity unit [quantity unit...] [direction]
quantity 是一个数字或符号； unit 是 second, minute, hour, day, week, month, year, decade, century, millennium, 或这些单位的缩写或复数； direction 可以是 ago 或者为空。符号 @ 是一个可选的东西。不同的单位以及相应正确的符号都是隐含地增加。
3、字符串类型、二进制类型
PostgreSQL支持两种字符型数据：文本字符串和二进制字符串。PostgreSQL提供了三种存储字符串的类型：char、varchar、text。bytea类型用于允许存储二进制字串。
| 类型名称 | 存储空间 | 长度 |
|---|---|---|
| char(n)/character | 用户定义 | 定长，有长度限制 |
| varchar(n)/character varying(n) | 用户定义 | 变长，有长度限制 |
| text | 实际长度 | 变长，无长度限制 |
| tytea | 4字节加上实际的二进制字串 | 变长，无长度限制 |
1.char和varchar类型
两种类型最多存储用户自定义长度N个字符。超出长度会产生错误，存储字符小于定义长度时，CHAR类型会用空格补满存储空间，VARCHAR类型只存储实际长度。
2.text类型
TEXT不是标准的SQL类型，许多数据库系统都实现了这一类型，在PostgreSQL中，TEXT可存储任意长度的字符串。
3.bytea类型
PostgreSQL提供了BYTEA类型，用于存储二进制字符串。BYTEA类型数据存储空间为用户实际二进制字符串加4字节。例如向file表file_byte字段插入E'\\001'数据，SQL语句如下：
INSERT INTO file file_byte VALUES (E'\\001);
4、布尔类型、位串类型
PostgreSQL支持标准的 SQL boolean 数据类型，boolean值只能有两种： '真'('True') 或 '假'('False')。boolean有两种值外的第三种状态，'未知'('Unknow')，用 SQL空状态表示。位串就是一串 1 和 0 的字符串。它们可以用于存储和视觉化位掩码。
1.布尔类型
boolean类型存储空间为1字节，可接受的"真"值的有效文本输入值有：TRUE、't'、'true'、'y'、'yes'、'1'。"假"值可接受的有效输入值有：FALSE、'f'、'false'、'n'、'no'、'0'。建议使用与SQL标准兼容的TRUE和FALSE做为boolean类型的输入值。查询时使用字母 t 和 f做为boolean型数据显示。例如，users表中有列used字段为boolea类型，字段查询后显示如下：
used
-------
t
f
2.位串类型
有两种类型的SQL位类型：bit(n) 和 bit varying(n)；bit类型的数据必须准确匹配长度n；bit varying类型数据是最大长度为n的变长类型；把一个位串值转换成bit(n)，那么它的右边将被截断或者在右边补齐零，以符合定义的n位长度，不会抛出任何错误。把一个位串数值转换成 bit varying(n)，如果它超过了n位，那么它的右边将被截断。示例：创建包括a(bit)，b(bit varying)两列表test，插入数据后执行查询，SQL语句如下：
CREATE TABLE test (a bit(3), b bit varying(5));
INSERT INTO test VALUES (B'101', B'00');
INSERT INTO test VALUES (B'10', B'101');
ERROR: Bit string length 2 does not match type bit(3)
INSERT INTO test VALUES (B'10'::bit(3), B'101');
SELECT * FROM test;
显示结果如下：
a | b
-----+-----
101 | 00
100 | 101
5、数组类型
PostgreSQL 允许将字段定义成定长或不定长的多维数组，数组类型可以是任何基本类型或用户定义类型(复合类型和域的数组还不支持)。
1.数据类型定义
数组类型定义是通过在数组元素类型名后面国方括号：[]来命名的。PostgreSQL目前的实现并不强制限制数组长度，即：声明长度和未声明长度的数组相同。也不强制数组维数，特定元素类型的数组都被认为是相同的类型，不管他们的大小或者维数。创建数组类型字段示例如下：
CREATE TABLE array_tmp(
name text,
pay_by_quarter integer[],
schedule text[][],
squares integer[3][3] --指定长度后仍然不会限制长度
);
2.数组类型数据插入
数组类型数据插入用大括号把值括起来并且用逗号将它们分开。可以在任意数组值周围添加双引号，如果值包含逗号或者花括弧，必须加上双引号输入。数据类型数据插入，还可以使用ARRAY构造器。示例如下：
INSERT INTO array_tmp VALUES(
'freeoa',
'{1,2,3,4}',
'{{"include {"},{"2value"}}',
'{{111,122},{211, 222}}');
INSERT INTO array_tmp VALUES (
'liuht',
ARRAY[1,2,3,4],
ARRAY[['value 11', 'value 12'], ['value 21', 'value 22']],
ARRAY[[111,122],[211, 222]]); --使用ARRAY构造器
查询结果如下：
name | pay_by_quarter | schedule | squares
--------+----------------+---------------------------------------------------+-----------------------
freeoa | {1,2,3,4} | {{"include {"},{2value}} | {{111,122},{211,222}}
liuht | {1,2,3,4} | {{"value 11","value 12"},{"value 21","value 22"}} | {{111,122},{211,222}}
3.数组类型数据的查询
和其他语言一样，PostgreSQL中数组也是通过下标数字的方式进行访问，只是PostgreSQL中数组元素的下标是从1开始n结束，格式如：[n]。对于一维或更多维数组，查询一个数组的部分数据查询格式为：[脚标界上:脚标下界]。
数组下标定位查询及返回结果：
# SELECT name, schedule FROM array_tmp WHERE schedule[1][1]='value 11';
name | schedule
--------+---------------------------------------------------
freeoa | {{"value 11","value 12"},{"value 21","value 22"}}
查询多维数据的部分数据，如查询单维字段pay_by_quarter的第2,3列数据，及多维列schedule的第1维的1列数据和第2维第一列数据。SQL语句及查询结果如下：
# SELECT pay_by_quarter[2:3],schedule[1:2][1:1] FROM array_tmp;
pay_by_quarter | schedule
----------------+-----------------------------
{2,3} | {{"include {"},{2value}}
{2,3} | {{"value 11"},{"value 21"}}
6、复合类型
PostgreSQL复合类型描述一行或者一条记录的结构，实际上它只是一个字段名和它们的数据类型的列表。在PostgreSQL中你可以像使用简单数据类型那样使用复合类型。
1.复合类型声明
复合类型只能声明字段名字和类型，不能声明约束(比如 NOT NULL 这样的)。使用复合类型需要先定义，之后在创建表的语句中引用定义的复合类型。示例如下：
CREATE TYPE student_lesson AS (
name text,
times numeric
);
CREATE TABLE students (
name text,
lesson student_lesson
);
2.复合类型数据插入
复合类型数据插入要以文本常量书写复合类型值，在圆括弧里包括字段值并且用逗号分隔，如果值本身包含逗号或者圆括弧，刚需要用双引号括起来。 复合类型常量的一般格式如下：
'( val1 , val2 , ... )'
例如，在上一步创建的表中插入数据：
# INSERT INTO students VALUES('freeoa','("math", 3)');INSERT INTO students VALUES('liuht',ROW('math', 5));
INSERT 0 1
# SELECT * FROM students;
name | lesson
--------+----------
freeoa | (math,3)
liuht | (math,5)
3.复合类型查询
要访问复合类型字段的一个子字段，需要按字段名.子字段的形式查询，非常类似从一个表名字里查出一个字段。为避免分析器混淆，需要在查询的复合类型字段外增加圆括弧。示例如下：
#SELECT lesson.name FROM students;
ERROR: missing FROM-clause entry for table "lesson" --不加圆括号会按表查询，查询失败
LINE 1: SELECT lesson.name FROM students;
^
# SELECT (lesson).name FROM students; --正确的查询方法
name
------
math
math
如查在查询中需要使用表名，并按复合类型的子字段进行筛选。可参考下面的示例进行查询：
# SELECT students.name, (students.lesson).name FROM students WHERE (students.lesson).times>3;
name | name
-------+------
liuht | math
(1 row)
4.复合类型数据修改
修改复合类型数据时，可以整体更新复合类型字段，也可更新复合类型字段的子字段。示例如下：
# UPDATE students SET lesson = ROW('Hto', 6) WHERE name = 'hto'; --整字段更新
UPDATE 1
# UPDATE students SET lesson.times = 7 WHERE name = 'freeoa'; --子字段更新
UPDATE 1
#SELECT * FROM students;
name | lesson
--------+-----------
liuht | (Hto,6)
freeoa | (math,7)
---------------------------------------------------------------
各数据类型的操作函数
数学函数(绝对值函数、圆周率函数、求余函数、平方根函数)
PostgreSQL中提供了很多简单易用且功能强大的函数，通过这些函数可以极大的提高用户管理数据库的效率。PostgreSQL中包括：数学函数、字符串函数、日期时间函数、系统及加密函数和条件判断函数等。本篇将介绍PostgreSQL数学函数中的：绝对值函数、圆周率函数、求余函数、平方根函数。
1. 绝对值函数：ABS(x)
ABS(x)会返回x的绝对值。
例如，计算-2、3､-2.2的绝对值：
SELECT ABS(-2), ABS(3), ABS(-2.2);
返回值如下:
abs | abs | abs
-----+-----+-----
2 | 3 | 2.2
对于绝对值的计算：正数会返回其本身，所以3的绝对值还是3；负数的绝对值会是其相反的数，所以-2和-2.2分别返回2和2.2。
2. 圆周率函数：PI()
PI()会返回圆周率π的什，默认以15位小数的格式显示。例如：
SELECT PI();
其返回值如下：
pi
------------------
3.14159265358979
3. 求余函数：MOD(x,y)
MOD(x,y)会返回x除y后的余数，MOD()对于带有小数值的数据也同样适用。
例如，对MOD(21,8)、MOD(167,10)、MOD(89.8,4)做求余运算：
SELECT MOD(21,8),MOD(167,10),MOD(89.8,4);
返回值如下：
mod | mod | mod
-----+-----+-----
5 | 7 | 1.8
4. 平方根函数：SQRT(x)
SQRT(x)会返回x的非负平方根。
例如，求16和50的平方根：
SELECT SQRT(16), SQRT(50);
返回值如下：
sqrt | sqrt
------+------------------
4 | 7.07106781186548
数学函数(符号函数、四舍五入函数、整数函数)
1. 符号函数：SIGN(x)
SIGN(x)会返回x参数的符号。
例如，使用SIGN函数返回－11､0和11的符号：
SELECT SIGN(-11), SIGN(0), SIGN(11);
返回值如下:
sign | sign | sign
------+------+------
-1 | 0 | 1
用SIGN()函数计算符号时，当其为负数、0或正数时，其返回结果分别是-1、0和1
2. 四舍五入函数：ROUND(x)、ROUND(x,y)
ROUND(x)会返回对x进行四舍五入后的整数。
例如，分别对3.14、-3.14、2.56进行四舍五入运算：
SELECT ROUND(3.14), ROUND(-3.14), ROUND(2.56);
其返回值如下：
round | round | round
-------+-------+-------
3 | -3 | 3
ROUND(x,y)会返回对x进行四舍五入计算，其保留小数位为y。如果y是负数，则会保留x到小数点左边的y位。
例如，对3.14进行1位、0位和-1位四舍五入运算：
SELECT ROUND(3.14,1), ROUND(3.14,0), ROUND(3.14,-1);
其返回值如下：
round | round | round
-------+-------+-------
3.1 | 3 | 0
3. 整数函数：CEIL(x)、CEILING(x)、FLOOR(x)
CEIL(x)和CEILING(x)作用相同，都会返回一个不小于x位的最小整数，返回值会被转换成一个BIGIN类型。
例如，使用CEIL函数和CEILING计算3.14和-3.14的最小整数：
SELECT CEIL(3.14), CEIL(-3.14), CEILING(3.14), CEILING(-3.14);
返回值如下：
ceil | ceil | ceiling | ceiling
------+------+---------+---------
4 | -3 | 4 | -3
FLOOR(x)会返回一个不大于x位的最小整数，返回值会被转换成一个BIGIN类型。
例如，使用FLOOR函数计算3.14和-3.14的最大整数值：
SELECT FLOOR(3.14), FLOOR(-3.14);
返回值如下：
floor | floor
-------+-------
3 | -4
4.数字截取函数：trunc
trunc()函数返回一个数截断到一定的小数位数，用于截断数字，而不是日期。date_trunc()函数用于截断时间戳。
语法：trunc(number,[ decimal_places ])
参数：要截断的数字，要截断的小数位数；decimal_places可选的，该值必须是正整数或负整数，默认为0。
注意：如果decimal_places是负数，则trunc函数将使小数位0值左侧的数字成为数字。
适用于：trunc函数可用于以下PostgreSQL 8.4以上的版本。要将时间截断为小时，则可以使用：
date_trunc('hour', date)
如果你想截断当天则可以使用它：
date_trunc('day', date)
函数 trunc() 需要一个数字或双精度条目：
trunc ( numeric ) → numeric
trunc ( double precision ) → double precision
或者如果需要设置小数的情况，它必须是一个数字：
trunc ( v numeric, s integer ) → numeric
select trunc(123.45, 1);
123.4 正数1表示小数点的个数
select trunc(123.45, -2);
100
select trunc(123.45, -3);
0，-3表示最小值从1000算起，不足1000的则视为零
数学函数(角度转弧度函数、弧度转角度函数、幂运算函数、对数函数)
1. 角度转弧度函数：RADIANS(x)
RADIANS(x)会将x的角度转换为弧度。
例如，计算90度角转换为弧度的值：
SELECT RADIANS(90);
返回值如下:
radians
-----------------
1.5707963267949
2. 弧度转角度函数：DEGREES(x)
DEGREES(x)会将x的弧度转换为角度。例如：
SELECT DEGREES(PI());
其返回值如下：
degrees
---------
180
3. 幂运算函数：POW(x,y)、POWER(x,y)、EXP(x)
POW(x,y)和POWER(x,y)函数会返回x的y次方的计算值。例如，分别使用POW和POWER函数计算4的3次方：
SELECT POW(4,3), POWER(4,3);
返回值如下：
pow | power
-----+-------
64 | 64
EXP(x)会返回e的x次方。例如：
SELECT EXP(3);
返回值如下：
exp
------------------
20.0855369231877
4. 对数函数：LOG(x)
LOG(x)会返回x的对数。在计算对数时x不能是负数，非常将会出错。
例如，求3的对数：
SELECT LOG(3);
返回值如下：
log
-------------------
0.477121254719662
数学函数(正弦函数、反正弦函数、余弦函数、反余弦函数)
1. 正弦函数：SIN(x)
SIN(x)会返回x的正弦值，x为弧度。
例如，使用SIN函数计算正弦值：
SELECT SIN(1), ROUND(SIN(PI()));
返回值如下:
sin | round
-------------------+-------
0.841470984807897 | 0
2. 反正弦函数：ASIN(x)
ASIN(x)会将x的反正弦值，即：正弦为x的值。x的取值范围为-1~1，超出范围会报错。例如，使用ASIN函数计算反正弦值：
SELECT ASIN(1), ASIN(2);
其返回值如下：
asin | asin
-----------------+------
1.5707963267949 | NaN
3. 余弦函数：COS(x)
COS(x)会返回x的余弦值，x为弧度。例如，计算COS函数计算余弦值：
SELECT COS(0), COS(1), COS(PI());
返回值如下：
cos | cos | cos
-----+------------------+-----
1 | 0.54030230586814 | -1
4. 反余弦函数：ACOS(x)
ACOS(x)会返回x的反余弦值，即：余弦值是x的值。x的取值范围为-1~1，超出范围会报错。例如，使用ACOS函数计算反余弦值：
SELECT ACOS(1), ACOS(0.54030230586814), ACOS(0);
返回值如下：
acos | acos | acos
------+------+-----------------
0 | 1 | 1.5707963267949
字符串函数(字符串截取函数、字符串倒序函数、字符串位置查找函数)
Postgre字符串处理函数中的：字符串截取函数、字符串倒序函数、字符串位置查找函数，可分别对字符串数据进行截取、倒序及查找子字符串在字符串中的位置。
1. 字符串截取函数：SUBSTRING(s, n, len)
SUBSTRING(s, n, len)
SUBSTRING()函数用于字符串的截取操作，它会从字符串s的位置n开始截取长度len的子字符串。
例如，使用SUBSTRING函数截取一个子字符串：
SELECT SUBSTRING('freeoa.net', 1, 6);
返回值如下:
substring
-----------
freeoa
2. 字符串倒序函数：REVERSE(s)
REVERSE(s)
REVERSE()是字体串倒序函数，它后将字符串s的字符顺序进行反转。
例如，使用REVERSE函数将字符串倒序：
SELECT REVERSE('freeoa.net');
返回值如下：
reverse
------------
ten.aoeerf
3. 字符串位置查找函数：POSITION(s1 IN s)
POSITION(s1 IN s)
POSITION()函数用于查找子字符串s1在字符串s中的位置。这个函数非常有用，如果需要对字符进行相关操作时需要操作字符或字符串的位置，就要用这个函数查找字符位置。
例如，使用POSITION查找字符串位置：
SELECT SUBSTRING('xxxxxxxfreeoa.netxxxxx', POSITION('freeoa.net' IN 'xxxxxxxfreeoa.netxxxxx'), 10);
返回值如下：
substring
------------
freeoa.net
字符串函数(字符串替换函数、字符移除函数、生成重复字符串函数)
Postgre中提供了字符串替换函数、字符串中指定字符移除函数、生成重复字符串函数，使用这些函数可以方便的对数据库中的字符串数据进行字符替换、字符移除及生成重复字符串的操作。
1. 字符串替换函数：REPLACE(s, s1,s2)
REPLACE(s, s1,s2)
REPLACE()函数用于字符串中字符的替换操作，它会将字符串s中的所有s1替换为s2
例如，使用REPLACE函数替换字符串：
SELECT REPLACE('freeoa.netm', 'netm', 'net');
返回值如下:
replace
------------
freeoa.net
2. 字符串移除函数：TRIM(s1 FROM s)
TRIM(s1 FROM s)
TRIM()函数用于删除字符串两端的空格，添加FROM表达式后可用于删除字符串两端的指定字符s1。
例如，使用TRIM函数进行字符串删除操作：
SELECT TRIM('lu' FROM 'lufreeoa.netlu');
其返回值如下：
btrim
------------
freeoa.net
3. 生成重复字符串函数：REPEAT(s, n)
REPEAT(s, n)
REPEAT(s, n)<()函数将字符串s重复生成n次。
例如，使用REPEAT()生成重复字符串：
SELECT REPEAT('freeoa.net ', 3);
返回值如下：
repeat
-----------------------------------
freeoa.net freeoa.net freeoa.net
(1 row)
字符串函数(字符串合并函数、字符串添充函数、删除字符串空格函数)
在数据库中处理字符串数据时时，我们有时会对字符串进行合并、添充或删除空格式等操作，PostgreSQL中提供了相应的字符串处理函数。
1. 字符串合并函数：CONCAT(s1,s2,……)、CONCAT_WS(x,s1,s2,……)
CONCAT(s1,s2,……)
CONCAT()函数会将多个字符串参数连接成一个字符串。如果其中一个参数为NULL时，则返回结果也是NULL，其值会被忽略。如果其一个参数为二进制字符串，则返回结果也是二进制字符串。
例如，使用CONCAT函数连接字符串：
SELECT CONCAT('freeoa','.net'), CONCAT('freeoa','.net', NULL), CONCAT('freeoa', '.net', '\\000'::bytea);
返回值如下:
concat | concat | concat
------------+------------+----------------------
freeoa.net | freeoa.net | freeoa.net\x5c303030
CONCAT_WS(x,s1,s2,……);
CONCAT_WS()(concat with separator)是CONCAT()函数的特殊形式，CONCAT_WS()函数连接字符串时，可以指定一个分隔符x。
例如，使用CONCAT_WS函数连接字符串，并指定分隔符：
# SELECT CONCAT_WS('.', 'freeoa', 'net'), CONCAT_WS('.', 'freeoa','net', NULL), CONCAT_WS('.', 'freeoa', 'net', '\\000'::bytea);
返回值如下:
concat | concat | concat
------------+------------+----------------------
freeoa.net | freeoa.net | freeoa.net\x5c303030
2. 字符串添充函数：LPAD(s1, len, s2)、RPAD(s1, len, s2)
LPAD(s1, len, s2);
RPAD(s1, len, s2);
LPAD()函数和RPAD()函数都会使用字符串s2对s1进行添充，添充指定长度是len，两个函数的添充方向分别是左侧和右侧。如果s1长度大于len，则会被切隔至指定长度。
例如，使用LPAD函数和RPAD函数进行字符串添充操作：
SELECT LPAD('freeoa', 10, '.net'), RPAD('freeoa', 10, '.net');
其返回值如下：
lpad | rpad
------------+------------
.netfreeoa | freeoa.net
3. 删除字符串空格函数：LTRIM(s)、RTRIM(s) 、TRIM(s)
LTRIM(s);
RTRIM(s);
TRIM(s);
LTRIM()、RTRIM() 、TRIM()三个函数分别会去除字符串左侧、右侧和两侧的空格。
例如，使用LTRIM()、RTRIM() 、TRIM()函数去除字符串空格：
SELECT CONCAT(LTRIM(' freeoa '), '.net'), CONCAT(RTRIM(' freeoa '), '.net'), CONCAT(TRIM(' freeoa'), '.net');
返回值如下：
concat | concat | concat
-------------+-------------+------------
freeoa .net | freeoa.net | freeoa.net
字符串函数(计算字符串字符数函数、计算字符串长度函数、获取指定长度字符串函数)
在对数据库中的数据进行处理时，很多情况下会涉及对字符串数据的处理。在PostgreSQL中，处理字符串的函数有很多，本篇介绍：计算字符串字符数函数、计算字符串长度函数、获取指定长度字符串函数。
1. 计算字符串字符数函数：CHAR_LENGTH(x)
CHAR_LENGTH(x)
CHAR_LENGTH(x)会返回字符串x的字符中数。
例如，使用CHAR_LENGTH函数计算字符串的字符数：
SELECT CHAR_LENGTH('aaa'), CHAR_LENGTH('freeoa.net');
返回值如下:
char_length | char_length
-------------+-------------
3 | 10
2. 计算字符串长度函数：LENGTH(x)
LENGTH(X)
LENGTH(x)会返回字符串x的字节长度。
例如，使用LENGTH函数计算字符串的字节长度：
SELECT LENGTH('aaa'), LENGTH('freeoa.net');
其返回值如下：
length | length
--------+--------
3 | 10
LENGTH()函数计算字符串字节长度时，utf-8编码的字符串，一个汉字会占用3个字节长度，一个字母或数字点用一个字节长。上面两个示例CHAR_LENGTH()函数和LENGTH()函数计算值相同，就是因为英文字符所点的字节数相同，都是一个字节。
3. 获取指定长度字符串函数：LEFT(x)、RIGHT(x)
LEFT(x);
RIGHT(x);
获取指定长度字符串可以使用LEFT(x)函数从字符串左侧截取，也可以使用RIGHT(x)函数从字符串右侧截取。例如，LEFT(x)和RIGHT(x)函数对freeoa.com左侧和右侧截取：
SELECT LEFT('freeoa.net', 6), RIGHT('freeoa.net', 4);
返回值如下：
left | right
--------+-------
freeoa | .net
日期时间函数(使用EXTRACT函数获取日期中的指定值)
在PostgreSQL的日期时间函数中，有一个EXTRACT函数，使用该函数可以方便的获取日期中的某一部分值，如：日期、月份、年、年中的第几天等。
1. 语法格式
EXTRACT(type FROM d)
EXTRACT函数用于提取日期d中的指定类型type的值，type可以是以下值之一：
值：描述
DAY：返回日期值
MONTH：返回月份值
YEAR：返回年份值
DOY(day of year)：返回年中的第几天
DOW(day of week)：返回星期几
QUARTER：返回季度
2. EXTRACT使用示例
使用EXTRACT函数查询日期值、月份、年份等：
SELECT EXTRACT(DAY FROM NOW()), EXTRACT(MONTH FROM NOW()), EXTRACT(YEAR FROM NOW()), EXTRACT(DOY FROM NOW()), EXTRACT(DOW FROM NOW()), EXTRACT(QUARTER FROM NOW());
其返回值如下：
date_part | date_part | date_part | date_part | date_part | date_part
-----------+-----------+-----------+-----------+-----------+-----------
15 | 11 | 2015 | 319 | 0 | 4
日期时间函数(获取当前日期时间函数、获取当前日期的函数、获取当前时间的函数)
日期时间函数用于处理数据库中的日期和时间值，在PostgreSQL中用于表示日期和时间的数据类型有：DATE类型、TIME类型、DATETIME类型和TIMESTAMP类型，详见：PostgreSQL日期时间类型。
1. 获取当前日期时间函数：CURRENT_TIMESTAMP、LOCALTIMESTAMP、NOW()
CURRENT_TIMESTAMP;
LOCALTIMESTAMP;
NOW();
PostgreSQL提供了三个用于获取当前日期时间的函数：CURRENT_TIMESTAMP、LOCALTIMESTAMP、NOW()，返回格式为：YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS。使用时可以根据不用的应用场景使用不同的函数。
例如，使用日期时间函数获取当前日期时间：
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, NOW();
返回值如下:
now | timestamp | now
-------------------------------+----------------------------+-------------------------------
2019-08-11 14:55:46.298786+08 | 2019-08-11 14:55:46.298786 | 2019-08-11 14:55:46.298786+08
LOCALTIMESTAMP与另外两个函数略有区别，这个函数返回的是不带时区的时间。
2. 获取当前日期的函数：CURRENT_DATE
CURRENT_DATE
计算日期时间时，如果仅需要当前的日期，可以使用CURRENT_DATE()。日期返回格式是YYYY-MM-DD，但具体返回格式和使用环境有关。例如，使用CURRENT_DATE函数返回当前日期：
SELECT CURRENT_DATE;
返回值如下：
date
------------
2019-08-11
3. 获取当前时间的函数：CURRENT_TIME
CURRENT_TIME
CURRENT_TIME函数会返回当前系统时间，在操作日期时间时，如果仅需要当前时间使用这个函数会很方便。其默认返回格式是HH:MM:SS，但具体返回格式和使用环境有关。
例如，使用CURRENT_TIME函数返回当前时间：
SELECT LOCALTIME;
返回值如下：
time
-----------------
21:17:01.436793
函数之用户、用户连接信息与系统版本
在PostgreSQL中，你可使用系统函数查看数据库的版本号、用户名、连接数等，或使用用户名函数查看当前登录用户的用户名等信息。
1. 查看系统版本信息
VERSION()
通过VERSION()可以查看当前PostgreSQL数据库服务器的版本信息等
SELECT VERSION();
返回值如下:
PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (CRUX-x86_64-multilib) 5.2.0, 64-bit
2. 用户信息查看
USER
CURRENT_USER
USER和CURRENT_USER函数可以查看当前PostgreSQL的登录用户信息。
SELECT USER, CURRENT_USER;
返回值如下:
current_user | current_user
--------------+--------------
postgres | postgres
3. 查看用户连接信息
pg_stat_activity
pg_stat_activity是一个PostgreSQL内部视图，可以用于查看当前连接的数据库名，用户，IP地址，连接开始时间，查询的语句等。
SELECT * FROM pg_stat_activity;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change
| wait_event_type | wait_event | state | backend_xid | backend_xmin | query
-------+---------+------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-----------------------------
--+-----------------+------------+--------+-------------+--------------+---------------------------------
16385 | pdb | 1773 | 16384 | dba | psql | 172.18.0.8 | | 38899 | 2019-08-11 14:37:43.050681+08 | 2019-08-11 14:41:48.728643+08 | 2019-08-11 14:41:48.728643+08 | 2019-08-11 14:41:48.728647+0
8 | | | active | | 118712 | SELECT * FROM pg_stat_activity;
函数之加密、编/解码函数
对于存入数据库的数据，我们可能会需要进行加密，而从数据库读出数据时，又需要数据进行解密。PostgreSQL提供了几个加密、解密函数，可以帮我们完成相关操作。
1. MD5加密函数
MD5(str)
MD5()用于计算字符串的MD5加密值，传入NULL时其返回值也是NULL。注意：MD5加密是不可解密的，这在用户密码等使用方面非常有用，但如果需要对加密值进行解密这种加密方式就不太适用。
SELECT MD5('freeoa.net');
返回结果：
40c0323aa7b9b6d135768f1e40b67020
2. ENCODE编码
ENCODE(str, paswd_str)
ENCODE()是一个编码函数，该函数可以指定一个编码paswd_str来对字符串编码。可接受的编码方式有：base64、hex、escape。
SELECT ENCODE('freeoa.net', 'base64');
其返回值如下：
encode
------------------
ZnJlZW9hLm5ldA==
3. DECODE编码
DECODE(str, paswd_str)
对于已使用ENCODE()编码的函数，可以使用DECODE()函数对其进行解码，可接受的解码方式与编码方式对应有：base64、hex、escape。
SELECT DECODE('ZnJlZW9hLm5ldA==','base64');
返回值如下：
decode
------------------------
freeoa.net
函数之流程控制
流程控制语句会根据不同的条件执行不同的流程。在PostgreSQL中，流程控制使用CASE和CASE WHEN语句实现。
1. CASE条件判断与流程控制
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[ELSE else_result_expression]
END
CASE函数会判断表达式input_expression的值，并根据判断结果执行对应的WHEN分支流程。
CASE使用示例：
SELECT CASE 1+1 WHEN 1 THEN 'net.freeoa' WHEN 3 THEN 'freeoa' ELSE 'freeoa.net' END;
返回值如下:
case
------------
net.freeoa
2. CASE THEN条件判断与流程控制
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[ELSE else_result_expression]
END
CASE THEN语句与CASE语句类似，其条件判断语句Boolean_expression是一个计算结果是布尔值的表达式。
CASE THEN使用示例：
SELECT CASE WHEN 1>2 THEN 'net.freeoa' ELSE 'freeoa.net' END;
返回值如下：
case
------------
freeoa.net
函数之集合查询：AVG、COUNT、MAX、MIN、SUM
在数据统计的时候，大多不需要返回详细数据，而只是对数据进行一些分析和汇总。在PostgreSQL中，提供了一些查询函数，可以方便的对数据进行分析、汇总等，如：统计数据总数，对某个字段进行总和查询，最大值、最小值、平均值的判断等。下面是PostgreSQL中一些常用的查询函数。
函数名 函数功能
AVG() 计算平均值
COUNT() 计算总行数
MAX() 计算最大值
MIN() 计算最小值
SUM() 计算和
1. 使用AVG()计算平均值
AVG()函数用于计算指定列数据的平均值。例如，从产品表products中查询平均价格：
# SELECT AVG(price) avg_price FROM products;
AVG()函数查询时，可以指定WHERE条件及GROUP BY子句，进行分组查询。例如，查询某几个分类sort_id的平均价格：
# SELECT sort_id, AVG(price) avg_price FROM products WHERE sort_id in(1,2,3) GROUP BY sort_id;
# 查询结果如下
sort_id | avg_price
---------+-----------------------
1 | 10.0000000000000000
2 | 5088.0000000000000000
3 | 2288.0000000000000000
2. 使用COUNT()计算总行数
COUNT()函数用于计算表的总行数或某列包含数据的总行数。该函数有以下两种用途：
COUNT(*)，计算表的总行数
COUNT(字段名)，计算表中某列的总行数，计算时会忽略字段值为空的行
例如，计算产品表products的总行数：
# SELECT COUNT(*) FROM products;
count
-------
4
例如，计算产品表products，价格price不为空总行数：
# SELECT COUNT(price) FROM products;
count
-------
3
COUNT()函数查询时，也可以指定WHERE条件及GROUP BY子句，进行分组查询。例如，查询某几个分类sort_id下各自的行数：
# SELECT sort_id, COUNT(*) FROM products WHERE sort_id IN(2, 3, 4) GROUP BY sort_id;
sort_id | count
---------+-------
2 | 1
3 | 1
4 | 1
3. 使用MAX()计算最大值
MAX()函数用于返回指定列中的最大值。例如，查询产品表products中价格最高的产品：
# SELECT MAX(price) FROM products;
max
------
5088
MAX()函数查询时，也可以指定WHERE条件及GROUP BY子句以查询分组中的。例如，查询某几个分类sort_id下的最大价格：
# SELECT sort_id, MAX(price) FROM products WHERE sort_id in(1, 2, 3) GROUP BY sort_id;
sort_id | max
---------+------
1 | 10
2 | 5088
3 | 2288
4. 使用MIN()计算最小值
MIN()函数用于返回指定列中的最小值。例如，查询产品表products中价格最低的产品：
# SELECT MIN(price) FROM products;
min
-----
10
MIN()函数查询时，也可以指定WHERE条件及GROUP BY子句以查询分组中的。例如，查询某个分类sort_id下的最低价格：
# SELECT sort_id sort_id, MIN(price) FROM products WHERE sort_id = 2 GROUP BY sort_id;
sort_id | min
---------+------
2 | 5088
5. 使用SUM()计算总和
前面介绍COUNT()函数可以返回总行数，如果要返回指定列的总和就需要使用SUM()函数。例如，计算产品表products的总价格：
# SELECT SUM(price) FROM products;
sum
------
7386
与前面介绍的其它分组查询函数一样，SUM()函数也可以在查询时指定WHERE条件及GROUP BY子句以查询分组中的。例如，查询某个分类sort_id下的最低价格：
# SELECT sort_id, SUM(price) FROM products WHERE sort_id = 3 GROUP BY sort_id;
sort_id | sum
---------+------
3 | 2288
网友pmars整理的PostgreSQL常见字符串操作函数
函数：string || string
说明：String concatenation(字符串连接操作)
例子：select 'Post' || 'greSQL';#PostgreSQL
函数：string || non-string or non-string || string
说明：String concatenation with one non-string input(字符串与非字符串类型进行连接操作)
例子：select 'Value: ' || 42;#Value: 42
函数：bit_length(string)
说明：Number of bits in string(计算字符串的位数)
例子：select bit_length('pmars')#40
函数：char_length(string) or character_length(string)
说明：Number of characters in string(计算字符串中字符个数)
例子：select char_length('pmars');#5
函数：lower(string)
说明：Convert string to lower case(转换字符串为小写)
例子：select lower('PmArS');#"pmars"
函数：octet_length(string)
说明：Number of bytes in string(计算字符串的字节数)
例子：select octet_length('我是pmars');#11
select octet_length('我'); #3
函数：overlay(string placing string from int [for int])
说明：Replace substring(替换字符串中任意长度的子字串为新字符串)
例子：select overlay('I am pmars' placing 'ming' from 6 for 5);#"I am ming"
函数：position(substring in string)
说明：Location of specified substring(子串在一字符串中的位置)
例子：select position('ma' in 'pmars');#2
函数：substring(string [from int] [for int])
说明：Extract substring(截取任意长度的子字符串)
例子：select substring('topmars' from 3 for 3);#"pma"
函数：substring(string from pattern)
说明：Extract substring matching POSIX regular expression. See Section 9.7 for more information on pattern matching(利用正则表达式对一字符串进行任意长度的字串的截取)
例子：select substring('topmars' from 'p.*$');#"pmars"
函数：substring(string from pattern for escape)
说明：Extract substring matching SQL regular expression. See Section 9.7 for more information on pattern matching(利用正则表达式对某类字符进行删除，以得到子字符串)
例子：select substring('Thomas' from '%#"o_a#"_' for '#');#"oma"
函数：trim([leading | trailing | both] [characters] from string)
说明：Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string(去除尽可能长开始，结束或者两边的某类字符，默认为去除空白字符，当然可以自己指定，可同时指定多个要删除的字符串)
例子：select trim(leading 'p' from 'pmars');#"mars"
函数：upper(string)
说明：Convert string to uppercase(将字符串转换为大写)
例子：select upper('pmars');#"PMARS"
函数：ascii(string)
说明：ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings. the argument must be a strictly ASCII character(得到某一个字符的Ascii值)
例子：select ascii('pmars');select ascii('p');#112
函数：btrim(string text [, characters text])
说明：Remove the longest string consisting only of characters in characters (a space by default) from the start and end of string(去除字符串两边的所有指定的字符，可同时指定多个字符)
例子：select btrim('pmars','prs');#"ma"
函数：chr(int)
说明：Character with the given code. For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate a strictly ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes(得到某ACSII值对应的字符)
例子：select chr(65);#A
函数：convert(string bytea, src_encoding name, dest_encoding name)
说明：Convert string to dest_encoding. The original encoding is specified by src_encoding.
The string must be valid in this encoding.Conversions can be defined by CREATE CONVERSION. Also there are some predefined conversions. See Table 9-7 for available conversions(转换字符串编码，指定源编码与目标编码)
例子：select convert('我是pmars_in_utf8', 'UTF8', 'GBK');#"\316\322\312\307pmars_in_utf8"
函数：convert_from(string bytea, src_encoding name)
说明：Convert string to the database encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding(转换字符串编码，自己要指定源编码，目标编码默认为数据库指定编码)
例子：select convert_from('\316\322\312\307pmars','GBK');#"我是pmars"
函数：convert_to(string text, dest_encoding name)
说明：Convert string to dest_encoding(转换字符串编码，源编码默认为数据库指定编码，自己要指定目标编码)
例子：select convert_to('我是pmars_in_utf8','GBK');#"\316\322\312\307pmars_in_utf8"
函数：decode(string text, type text)
说明：Decode binary data from string previously encoded with encode. Parameter type is same as in encode(对字符串按指定的类型进行解码)
例子：select decode('MTIzAAE=', 'base64');#"123\000\001"
函数：encode(data bytea, type text)
说明：Encode binary data to different representation. Supported types are: base64, hex, escape. Escape merely outputs null bytes as \000 and doubles backslashes(与decode相反，对字符串按指定类型进行编码)
例子：select encode('123\000\001','base64');#"MTIzAAE="
函数：initcap(string)
说明：Convert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters(将字符串所有的单词进行格式化，首字母大写，其它为小写)
例子：select initcap('I AM PMARs');#"I Am Pmars"
函数：length(string)
说明：Number of characters in string(讲算字符串长度)
例子：select length('我是pmars');#7
函数：length(stringbytea, encoding name )
说明：Number of characters in string in the given encoding.
The string must be valid in this encoding(计算字符串长度，指定字符串使用的编码)
例子：select length('我是pmars','GBK');#8
函数：lpad(string text, length int [, fill text])
说明：Fill up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right)(对字符串左边进行某类字符自动填充，即不足某一长度，则在左边自动补上指定的字符串，直至达到指定长度，可同时指定多个自动填充的字符)
例子：select lpad('pmars', 10, 'to');#"tototpmars"
函数：ltrim(string text [, characters text])
说明：Remove the longest string containing only characters from characters (a space by default) from the start of string(删除字符串左边某一些的字符，可以时指定多个要删除的字符)
例子：select ltrim('pmars','amp');#"rs"
函数：md5(string)
说明：Calculates the MD5 hash of string, returning the result in hexadecimal(将字符串进行md5编码)
例子：select md5('freeoa');#"53df37d49955e1bf849dd295e5e8393f"
函数：pg_client_encoding()
说明：Current client encoding name(得到pg客户端编码)
例子：select pg_client_encoding();#"UTF8"
函数：quote_ident(string text)
说明：Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled(对某一字符串加上两引号)
例子：quote_ident('Foo bar')#"Foo bar"
函数：quote_literal(string text)
说明：Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled(对字符串里两边加上单引号，如果字符串里面出现sql编码的单个单引号，则会被表达成两个单引号)
例子：quote_literal('O\'Reilly')#'O''Reilly'
函数：quote_literal(value anyelement)
说明：Coerce the given value to text and then quote it as a literal. Embedded single-quotes and backslashes are properly doubled(将一数值转换为字符串，并为其两边加上单引号，如果数值中间出现了单引号，也会被表示成两个单引号)
例子：quote_literal(42.5)#'42.5'
函数：regexp_matches(string text, pattern text [, flags text])
说明：Return all captured substrings resulting from matching a POSIX regular expression against the string. See Section 9.7.3 for more information(对字符串按正则表达式进行匹配，如果存在则会在结果数组中表示出来)
例子：regexp_matches('foobarbequebaz', '(bar)(beque)')#{bar,beque}
函数：regexp_replace(string text, pattern text, replacement text [, flags text])
说明：Replace substring(s) matching a POSIX regular expression. See Section 9.7.3 for more information(利用正则表达式对字符串进行替换)
例子：regexp_replace('Thomas', '.[mN]a.', 'M')#ThM
函数：regexp_split_to_array(string text, pattern text [, flags text ])
说明：Split string using a POSIX regular expression as the delimiter. See Section 9.7.3 for more information(利用正则表达式将字符串分割成数组)
例子：regexp_split_to_array('hello world', E'\\s+')#{hello,world}
函数：regexp_split_to_table(string text, pattern text [, flags text])
说明：Split string using a POSIX regular expression as the delimiter. See Section 9.7.3 for more information(利用正则表达式将字符串分割成表格)
例子：regexp_split_to_table('hello world', E'\\s+')
hello
world
函数：repeat(string text, number int)
说明：Repeat string the specified number of times(重复字符串一指定次数)
例子：repeat('Pg', 4)#PgPgPgPg
函数：replace(string text, from text, to text)
说明：Replace all occurrences in string of substring from with substring to(将字符的某一子串替换成另一子串)
例子：replace('abcdefabcdef', 'cd', 'XX')#abXXefabXXef
函数：rpad(string text, length int [, fill text])
说明：Fill up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated(对字符串进行填充，填充内容为指定的字符串)
例子：rpad('hi', 5, 'xy')#hixyx
函数：rtrim(string text [, characters text])
说明：Remove the longest string containing only characters from characters (a space by default) from the end of string(去除字符串右边指定的字符)
例子：rtrim('trimxxxx', 'x')#trim
函数：split_part(string text, delimiter text, field int)
说明：Split string on delimiter and return the given field (counting from one)(对字符串按指定子串进行分割，并返回指定的数值位置的值)
例子：split_part('abc-def-ghi', '-', 2)#def
函数：strpos(string, substring)
说明：Location of specified substring (same as position(substring in string),but note the reversed argument order)(指定字符串在目标字符串的位置)
例子：strpos('high', 'ig')#2
函数：substr(string, from [, count])
说明：Extract substring (same as substring(string from from for count))(截取子串)
例子：substr('alphabet', 3, 2)#ph
函数：to_ascii(string text [, encoding text])
说明：Convert string to ASCII from another encoding(only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings)(将字符串转换成ascii编码字符串)
例子：to_ascii('Karel')#Karel
函数：to_hex(number int or bigint)
说明：Convert number to its equivalent hexadecimal representation(对数值进行十六进制编码)
例子：to_hex(2147483647)#7fffffff
函数：translate(string text, from text, to text)
说明：Any character in string that matches a character in the from set is replaced by the corresponding character in the to set(将字符串中某些匹配的字符替换成指定字符串，目标字符与源字符都可以同时指定多个)
例子：translate('12345', '14', 'ax')#a23x5
网友钱魏Way的SQL日期时间处理函数节选
数据库中常见的日期/时间存储格式：
Unix时间戳(10位，单位为秒，1970年1月1日到现在的秒数)：1622606037
Unix时间戳(13位，单位为微秒)：1622606037123
DATE类型的日期：2021-06-02
字符串类型日期：2021-06-02、2021/06/02、20210602
数值型日期：20210602
DATETIME类型时间：2021-06-02T11:53:57
PostgreSQL支持SQL中所有的日期和时间类型，如下表所示：
| 名字 | 存储尺寸 | 描述 | 最小值 | 最大值 | 解析度 |
| timestamp [ (p) ] [ without time zone ] | 8字节 | 包括日期和时间(无时区) | 4713 BC | 294276 AD | 1微秒 |
| timestamp [ (p) ] with time zone | 8字节 | 包括日期和时间，有时区 | 4713 BC | 294276 AD | 1微秒 |
| date | 4字节 | 日期(没有一天中的时间) | 4713 BC | 5874897 AD | 1日 |
| time [ (p) ] [ without time zone ] | 8字节 | 一天中的时间(无日期) | 00:00:00 | 24:00:00 | 1微秒 |
| time [ (p) ] with time zone | 12字节 | 仅仅是一天中的时间(没有日期)，带有时区 | 00:00:00+1459 | 24:00:00-1459 | 1微秒 |
| interval [ fields ] [ (p) ] | 16字节 | 时间间隔 | -178000000年 | 178000000年 | 1微秒 |
time、timestamp和interval接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界；p允许的范围是从 0 到 6。
interval类型有一个附加选项，它可以通过写下面之一的短语来限制存储的fields的集合：
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
注意：如果fields和p被指定，fields必须包括SECOND，因为精度只应用于秒。
PostgreSQL中with和without time zone的区别：从名字上可以看出一个是是带时区的，另一个是不带时区的，查出来的时间是一样的，只是一个带时区标志，一个不带而已，时区的基准是格林威治时间UTC。比如中国(PRC),时区是东八区,带时区标志的话就是+08。
SET timezone = "US/Pacific";
SHOW timezone; -- US/Pacific
SELECT
now()::TIMESTAMP WITH TIME ZONE, -- 2020-11-22 18:13:32.034831-08
now()::TIMESTAMP WITHOUT TIME ZONE; -- 2020-11-22 18:13:32.034831
SET timezone = PRC;
SHOW timezone; -- PRC
SELECT
now()::TIMESTAMP WITH TIME ZONE, --2020-11-23 10:13:32.037091+08
now()::TIMESTAMP WITHOUT TIME ZONE; --2020-11-23 10:13:32.037091
SET timezone = UTC;
SHOW timezone; --UTC
SELECT
now()::TIMESTAMP WITH TIME ZONE, --2020-11-23 02:13:32.040013+00
now()::TIMESTAMP WITHOUT TIME ZONE; -- 2020-11-23 02:13:32.040013
SET timezone = "Asia/Shanghai";
SHOW timezone; --Asia/Shanghai
SELECT
now()::TIMESTAMP WITH TIME ZONE, -- 2020-11-23 10:13:32.042719+08
now()::TIMESTAMP WITHOUT TIME ZONE; --2020-11-23 10:13:32.042719
备注：在PostgreSQL中，双冒号::是用于类型转换。
想要查看PostgreSQL支持的所有时区，可以通过以下SQL查看。
SELECT * FROM pg_timezone_names;
注意，在SQL中设置时区，只是临时性的修改，当关闭连接时会恢复到原来的时区，想要永久修改时区，需要修改配置文件。
PostgreSQL获取当前日期和时间
SELECT CURRENT_DATE; -- 2020-11-15
SELECT CURRENT_TIME; -- 19:53:10.138282+08
SELECT CURRENT_TIMESTAMP; --19:53:25.618691+08
SELECT CURRENT_TIME(0); -- 19:58:05+08,传入参数precision,可选值0-6,即秒后面的位数。
SELECT CURRENT_TIMESTAMP(1); -- 2020-11-15 19:58:40.7+08
SELECT LOCALTIME; -- 19:54:40.377817
SELECT LOCALTIMESTAMP; -- 2020-11-15 19:54:56.594073
SELECT LOCALTIME(0); -- 19:59:09
SELECT LOCALTIMESTAMP(1); -- 2020-11-15 19:59:19.9
SELECT now(); -- 2020-11-23 10:25:50.801659+08
PostgreSQL如何获取当前Unix时间戳？
在PG数据库中，extract函数用于从一个日期中获取某个子集，比如获取年，月，日，时，分，秒等。
SELECT now();-- 2020-11-23 10:40:28.85565+08
SELECT EXTRACT('HOUR' FROM now()); -- 10
SELECT EXTRACT('HOUR' FROM '2020-11-23 10:40:28.85565+08'::timestamp); -- 10
SELECT EXTRACT('epoch' FROM now()); -- 1606099228.856519
SELECT TO_TIMESTAMP(1606099228.856519) -- 2020-11-23 10:40:28.856519+08
新纪元时间 Epoch 是以 1970-01-01 00:00:00 UTC 为标准的时间，将目标时间与 1970-01-01 00:00:00 时间的差值以秒来计算，单位是秒，可以是负值。
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 |
PostgreSQL的几个日期和时间
| 输入串 | 合法类型 | 描述 |
| epoch | date, timestamp | 1970-01-01 00:00:00+00(Unix系统时间0) |
| infinity | date, timestamp | 比任何其他时间戳都晚 |
| -infinity | date, timestamp | 比任何其他时间戳都早 |
| now | date, time, timestamp | 当前事务的开始时间 |
| today | date, timestamp | 今日午夜 (00:00) |
| tomorrow | date, timestamp | 明日午夜 (00:00) |
| yesterday | date, timestamp | 昨日午夜 (00:00) |
| allballs | time | 00:00:00.00 UTC |
SELECT TIMESTAMP 'yesterday'; -- 2020-11-20 00:00:00
SELECT DATE 'yesterday'; -- 2020-11-20
SELECT TIMESTAMP 'today'; -- 2020-11-21 00:00:00
SELECT DATE 'today'; -- 2020-11-21
SELECT TIMESTAMP 'tomorrow'; -- 2020-11-22 00:00:00
SELECT DATE 'tomorrow'; -- 2020-11-22
SELECT TIME 'allballs'; -- 00:00:00
SELECT now(); -- 2020-11-21 16:53:41.676354+08
SELECT TIMESTAMP 'now'; --2020-11-21 16:53:41.676898
SELECT DATE 'now'; -- 2020-11-21
PostgreSQL日期/时间格式化
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 | 把数字转成字符串 | to_char(-125.8, ‘999D99S’) |
| to_date(text, text) | date | 把字符串转成日期 | to_date(’05 Dec 2000′, ‘DD Mon YYYY’) |
| to_number(text, text) | numeric | 把字符串转成数字 | to_number(‘12,454.8-‘, ’99G999D9S’) |
| to_timestamp(text, text) | timestamp with time zone | 把字符串转成时间戳 | to_timestamp(’05 Dec 2000′, ‘DD Mon YYYY’) |
示例：
SELECT to_char( CURRENT_TIMESTAMP, 'HH12:MI:SS' ) -- 07:46:34
SELECT to_char( INTERVAL '15h 2m 12s', 'HH24:MI:SS' ) -- 15:02:12
SELECT to_date('05 Dec 2000', 'DD Mon YYYY') -- 2000-12-05
SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY') -- 2000-12-05 00:00:00+08
用于日期/时间格式化的模板模式
| 模式 | 描述 |
| HH | 一天中的小时 (01-12) |
| HH12 | 一天中的小时 (01-12) |
| HH24 | 一天中的小时 (00-23) |
| MI | 分钟 (00-59)minute (00-59) |
| SS | 秒(00-59) |
| MS | 毫秒(000-999) |
| US | 微秒(000000-999999) |
| SSSS | 午夜后的秒(0-86399) |
| AM, am, PM or pm | 正午指示器(不带句号) |
| A.M., a.m., P.M. or p.m. | 正午指示器(带句号) |
| Y,YYY | 带逗号的年(4 位或者更多位) |
| YYYY | 年(4 位或者更多位) |
| YYY | 年的后三位 |
| YY | 年的后两位 |
| Y | 年的最后一位 |
| IYYY | ISO 8601 周编号方式的年(4 位或更多位) |
| IYY | ISO 8601 周编号方式的年的最后 3 位 |
| IY | ISO 8601 周编号方式的年的最后 2 位 |
| I | ISO 8601 周编号方式的年的最后一位 |
| BC, bc, AD或者ad | 纪元指示器(不带句号) |
| B.C., b.c., A.D.或者a.d. | 纪元指示器(带句号) |
| 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) |
| IDDD | ISO 8601 周编号方式的年中的日(001-371，年的第 1 日时第一个 ISO 周的周一) |
| DD | 月中的日(01-31) |
| D | 周中的日，周日(1)到周六(7) |
| ID | 周中的 ISO 8601 日，周一(1)到周日(7) |
| W | 月中的周(1-5)(第一周从该月的第一天开始) |
| WW | 年中的周数(1-53)(第一周从该年的第一天开始) |
| IW | ISO 8601 周编号方式的年中的周数(01 – 53；新的一年的第一个周四在第一周) |
| CC | 世纪(2 位数)(21 世纪开始于 2001-01-01) |
| J | 儒略日(从午夜 UTC 的公元前 4714 年 11 月 24 日开始的整数日数) |
| Q | 季度(to_date和to_timestamp会忽略) |
| RM | 大写形式的罗马计数法的月(I-XII；I 是 一月) |
| rm | 小写形式的罗马计数法的月(i-xii；i 是 一月) |
| TZ | 大写形式的时区缩写(仅在to_char中支持) |
| tz | 小写形式的时区缩写(仅在to_char中支持) |
| TZH | 时区的小时 |
| TZM | 时区的分钟 |
| OF | 从UTC开始的时区偏移(仅在to_char中支持) |
用于数值格式化的模板模式：
| 模式 | 描述 |
| 9 | 数位(如果无意义可以被删除) |
| 0 | 数位(即便没有意义也不会被删除) |
| . (period) | 小数点 |
| , (comma) | 分组(千)分隔符 |
| PR | 尖括号内的负值 |
| S | 带符号的数字(使用区域) |
| L | 货币符号(使用区域) |
| D | 小数点(使用区域) |
| G | 分组分隔符(使用区域) |
| MI | 在指定位置的负号(如果数字 < 0) |
| PL | 在指定位置的正号(如果数字 > 0) |
| SG | 在指定位置的正/负号 |
| RN | 罗马数字(输入在 1 和 3999 之间) |
| TH or th | 序数后缀 |
| V | 移动指定位数(参阅注解) |
| EEEE | 科学记数的指数 |
PostgreSQL时间/日期函数和操作符
下面是PostgreSQL中支持的时间/日期操作符的列表：
| 操作符 | 例子 | 结果 |
| + | date ‘2001-09-28’ + integer ‘7’ | date ‘2001-10-05’ |
| + | date ‘2001-09-28’ + interval ‘1 hour’ | timestamp ‘2001-09-28 01:00:00’ |
| + | date ‘2001-09-28′ + time ’03:00’ | timestamp ‘2001-09-28 03:00:00’ |
| + | interval ‘1 day’ + interval ‘1 hour’ | interval ‘1 day 01:00:00’ |
| + | timestamp ‘2001-09-28 01:00′ + interval ’23 hours’ | timestamp ‘2001-09-29 00:00:00’ |
| + | time ’01:00′ + interval ‘3 hours’ | time ’04:00:00′ |
| – | – interval ’23 hours’ | interval ‘-23:00:00’ |
| – | date ‘2001-10-01’ – date ‘2001-09-28’ | integer ‘3’ (days) |
| – | date ‘2001-10-01’ – integer ‘7’ | date ‘2001-09-24’ |
| – | date ‘2001-09-28’ – interval ‘1 hour’ | timestamp ‘2001-09-27 23:00:00’ |
| – | time ’05:00′ – time ’03:00′ | interval ’02:00:00′ |
| – | time ’05:00′ – interval ‘2 hours’ | time ’03:00:00′ |
| – | timestamp ‘2001-09-28 23:00′ – interval ’23 hours’ | timestamp ‘2001-09-28 00:00:00’ |
| – | interval ‘1 day’ – interval ‘1 hour’ | interval ‘1 day -01:00:00’ |
| – | timestamp ‘2001-09-29 03:00’ – timestamp ‘2001-09-27 12:00’ | interval ‘1 day 15:00:00’ |
| * | 900 * interval ‘1 second’ | interval ’00:15:00′ |
| * | 21 * interval ‘1 day’ | interval ’21 days’ |
| * | double precision ‘3.5’ * interval ‘1 hour’ | interval ’03:30:00′ |
| / | interval ‘1 hour’ / double precision ‘1.5’ | interval ’00:40:00′ |
日期/时间函数：
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
| age(timestamp, timestamp) | interval | 减去参数，生成一个使用年、月(而不是只用日)的“符号化”的结果 | age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) | 43 年 9 月 27 日 |
| age(timestamp) | interval | 从current_date(在午夜)减去 | age(timestamp ‘1957-06-13’) | 43 years 8 mons 3 days |
| clock_timestamp() | timestamp with time zone | 当前日期和时间(在语句执行期间变化) | ||
| current_date | date | 当前日期 | ||
| current_time | time with time zone | 当前时间(一天中的时间) | ||
| current_timestamp | timestamp with time zone | 当前日期和时间(当前事务开始时) | ||
| date_part(text, timestamp) | double precision | 获得子域(等价于extract) | date_part(‘hour’, timestamp ‘2001-02-16 20:38:40’) | 20 |
| date_part(text, interval) | double precision | 获得子域(等价于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’) | 36938.83333 |
| date_trunc(text, timestamp with time zone, text) | timestamp with time zone | 在指定的时区截断到指定的精度 | date_trunc(‘day’, timestamptz ‘2001-02-16 20:38:40+00’, ‘Australia/Sydney’) | 2001-02-16 13:00:00+00 |
| date_trunc(text, interval) | interval | 截断到指定精度 | date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’) | 2 days 03:00:00 |
| extract(field from timestamp) | double precision | 获得子域 | extract(hour from timestamp ‘2001-02-16 20:38:40’) | 20 |
| extract(field from interval) | double precision | 获得子域 | extract(month from interval ‘2 years 3 months’) | 3 |
| isfinite(date) | boolean | 测试有限日期(不是+/-无限) | isfinite(date ‘2001-02-16’) | TRUE |
| isfinite(timestamp) | boolean | 测试有限时间戳(不是+/-无限) | isfinite(timestamp ‘2001-02-16 21:28:30’) | TRUE |
| isfinite(interval) | boolean | 测试有限间隔 | isfinite(interval ‘4 hours’) | TRUE |
| justify_days(interval) | interval | 调整间隔这样30天时间周期可以表示为月 | justify_days(interval ’35 days’) | 1 mon 5 days |
| justify_hours(interval) | interval | 调整间隔这样24小时时间周期可以表示为日 | justify_hours(interval ’27 hours’) | 1 day 03:00:00 |
| justify_interval(interval) | interval | 使用justify_days和justify_hours调整间隔，使用额外的符号调整 | justify_interval(interval ‘1 mon -1 hour’) | 29 days 23:00:00 |
| localtime | time | 当前时间(一天中的时间) | ||
| localtimestamp | timestamp | 当前日期和时间(当前事务的开始) | ||
| make_date(year int, month int, day int) | date | 从年、月、日域创建日期 | make_date(2013, 7, 15) | 41470 |
| make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) | interval | 从年、月、周、日、时、分、秒域创建 interval | make_interval(days => 10) | 10 days |
| make_time(hour int, min int, sec double precision) | time | 从时、分、秒域创建时间 | make_time(8, 15, 23.5) | 0.344021991 |
| make_timestamp(year int, month int, day int, hour int, min int, sec double precision) | timestamp | 从年、月、日、时、分、秒域创建时间戳 | make_timestamp(2013, 7, 15, 8, 15, 23.5) | 41470.34402 |
| make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ]) | timestamp with time zone | 从年、月、日、时、分、秒域创建带时区的时间戳。如果没有指定timezone，则使用当前时区。 | make_timestamptz(2013, 7, 15, 8, 15, 23.5) | 2013-07-15 08:15:23.5+01 |
| now() | timestamp with time zone | 当前日期和时间(当前事务的开始) | ||
| statement_timestamp() | timestamp with time zone | 当前日期和时间(当前事务的开始) | ||
| timeofday() | text | 当前日期和时间(像clock_timestamp，但是作为一个text字符串) | ||
| transaction_timestamp() | timestamp with time zone | 当前日期和时间(当前事务的开始) | ||
| to_timestamp(double precision) | timestamp with time zone | 把 Unix 时间(从 1970-01-01 00:00:00+00 开始的秒)转换成 timestamp | to_timestamp(1284352323) | 2010-09-13 04:32:03+00 |