PostgreSQL——数据类型和运算符

1、数据类型

PostgreSQL支持多种数据类型，主要有整数类型、浮点数类型、任意精度数值、日期与时间类型、字符串类型、二进制类型、布尔类型和数组类型等。

1.1、整数类型

类型名称	说明	存储	取值范围
SMALLINT	小范围的整数	2个字节	-32768~32767
INT(INTEGRE)	普通大小的整数	4个字节	-2147483648~2147483647
BIGINT	大整数	8个字节	-9223372036854775808~9223372036854775807

CREATE TABLE tmp1
(x SMALLINT,y INT,z BIGINT
);

1.2、浮点数类型

类型名称	说明	存储
REAL	6位十进制数字精度	4个字节
DOUBLE PRECISION	15位十进制数字精度	8个字节

PostgreSQL也支持SQL标准表示法，float和float§用于声明非精确的数值类型。其中，p声明以二进制位表示的最低可接受精度。在选取REAL类型的时候，PostreSQL接受float(1)到float(24)，在选取DOUBLE PRECISION的时候，接受float(25)到float(53)。在允许范围之外的p值将导致一个错误，没有声明精度的float将被当作DOUBLEPRECISION。

CREATE TABLE tmp3
(x FLOAT(5),y REAL,z DOUBLE PRECISION
);

浮点类型有几个特殊值，Infinity表示正无穷大，-Infinity表示负无穷大，NaN表示不是一个数字。

1.3、任意精度类型

在PostgreSQL中，NUMERIC表示数值是任意精度的类型，使用NUMERIC(M,N)来表示。其中，M称为精度，表示总的位数：N称为标度，表示小数的位数。例如563.186中，精度为6，标度为3。

NUMERIC的有效的取值范围由M和N的值决定。如果改变M而固定N,则其取值范围将随M的变大而变大。另外如果用户数据的精度超出指定精度，则会四舍五入进行处理。

CREATE TABLE tmp4
(x NUMERIC(5, 1),y NUMERIC(5, 2)
);INSERT INTO tmp4
VALUES (9.12, 9.15);-- 查询数据，可以发现9.12被四舍五入
SELECT * FROM tmp4;

1.4、日期与时间类型

PostgreSQL中有多种表示日期的数据类型，TIME、DATE、TIMESTAMP和INTERVAL。每一个类型都有合法的取值范围，当指定确实不合法的值时，系统将零值插入到数据库中。

类型名称	含义	存储
TIME	只用于一日内时间	8字节
DATE	只用于日期	4字节
TIMESTAMP	日期和时间	8字节

另外，对于TIME和TIMESTAMP类型，默认情况下为without time zone(不带时区)，如果需要，可以设置为带时区(with time zone)。

1.4.1、TIME

TIME类型用于只需要时间信息的值，在存储时需要8个字节。格式为：HH:MM:SS。HH表示小时：MM表示分钟：SS表示秒。TIME类型的取值范围为00：00：00~24：00：00。

CREATE TABLE tmp5
(t TIME
);-- 省略秒数
INSERT INTO tmp5 
VALUES ('10:05:05'),('23:23');-- 查询数据
SELECT * FROM tmp5;

-- 插入数字值
INSERT INTO tmp5 
VALUES ('101112')-- 查询数据
SELECT * FROM tmp5;

插入系统时间，由于由时间函数获得的时间是带时区的，所以需要先将字段属性修改为带时区类型的时间：

ALTER TABLE tmp5 ALTER COLUMN t TYPE TIME without time zone;

DELETE FROM tmp5;-- 插入系统时间
INSERT INTO tmp5
VALUES (CURRENT_TIME), (NOW());-- 查询数据
SELECT * FROM tmp5;

1.4.2、DATE

DATE类型用在仅需要日期值时，没有时间部分，在存储时需要4个字节，日期格式为YYYY-MM-DD。其中，YYYY表示年：MM表示月：DD表示日。在给DATE类型的字段赋值时，
可以使用字符串类型或者数字类型的数据插入，只要符合DATE的日期格式即可，具体如下：

• 以'YYYY-MM-DD'或者'YYYYMMDD'字符串格式表示的日期。例如输入'2012-12-31'或者'20121231',插入数据库的日期都为2012-12-31.
• 以'YYMMDD'字符串格式表示的日期，在这里，YY表示两位的年值。包含两位年值的日期会令人模糊，因为不知道世纪。PostgreSQL使用以下规则解释两位年
  值：00~69范围的年值转换为'2000~2069；70~99范围的年值转换为1970~1999。例如揄入’12-12-31’，插入数据库的日期为2012-12-31；输入’981231’，插入数据的日期为1998-12-31.
• 使用CURRENT_DATE或者NOW()插入当前系统日期.

CREATE TABLE tmp6
(d DATE
);-- 插入YYYY-MM-DD和YYYYMMDD格式
INSERT INTO tmp6
VALUES ('1998-08-08'), ('19980808'), ('20101010');-- 查询数据
SELECT * FROM tmp6;

DELETE FROM tmp6;-- 插入YY-MM-DD和YYMMDD格式
INSERT INTO tmp6
VALUES ('990909'), ('000101'), ('121212');-- 查询数据
SELECT * FROM tmp6;

DELETE FROM tmp6;-- 插入系统时间
INSERT INTO tmp6
VALUES (NOW());-- 查询数据
SELECT * FROM tmp6;

1.4.3、TIMESTAMP

TIMESTAMP的日期格式为YYYY-MM-DDHH:MM:SS。在存储时需要8个字节，因此在插入数据时，要保证在合法的取值范围内。

CREATE TABLE tmp7
(ts TIMESTAMP
);INSERT INTO tmp7
VALUES ('1996-02-02 02:02:02'), (NOW());-- 查询数据
SELECT * FROM tmp7;

1.4.4、带时区的日期和时间类型

CREATE TABLE tmp7h
(th TIME with time zone
);INSERT INTO tmp7h
VALUES ('10:05:05 PST '), ('10:05:05');-- 查询数据
SELECT * FROM tmp7h;

1.5、字符串类型

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。字符串可以进行区分或者不区分大小写的串比较，另外，还可以进行模式匹配查找.PostgreSQL中字符串类型指CHAR、VARCHAR和TEXT.

类型名称	说明
CHAR(n) / CHARACTER(n)	固定长度非二进制字符串，不足补空白
VARCHAR(n) / CHARACTER VARYING(n)	变长非二进制字符串，有长度限制
TEXT	变长非二进制字符串，无长度限制

1.5.1、CHAR(n) / CHARACTER(n)

其中，n是一个正整数。CHARACTER(n)和CHARACTER VARYING(n)都可以存储最多n个字符的字符串。试图存储更长的字符串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字符串将被截断为最大长度。

如果要存储的字符串比声明的长度短，类型为CHARACTER的数值将会用空白填满：而类型为CHARACTER VARYING的数值将只是存储较短的字符串。

CREATE TABLE tmp8
(ch CHARACTER(4),vch CHARACTER VARYING(4)
);INSERT INTO tmp8
VALUES ('ab', 'ab'), ('abcd', 'abcd'), ('ab  ', 'ab  ');SELECT concat('(', ch, ')'), concat('(', vch, ')')
FROM tmp8;

从查询结果可以看到，ch在保存’b’时在右侧填充空格以达到指定的长度。而vch字段仅仅保留了’ab’。

CHARACTER类型中填充的空白是无意义的。例如在比较两个CHARACTER值时，填充的空白都会被忽略，在转换成其他字符串类型的时候，CHARACTER值里面的空白会被删除。请注意，在CHARACTER VARYING和TEXT数值里，结尾的空白是有意义的。

-- 如果插入的字符串长度超过规定的长度，则会报错
INSERT INTO tmp8
VALUES ('abcde', 'abcde');

1.5.2、TEXT

在PostgreSQL中，TEXT类型可以存储任何长度的字符串。尽管TEXT不是SQL标准类型，但是其他许多SQL数据库系统中也包括它。

CREATE TABLE tmp9
(te TEXT
);INSERT INTO tmp9
VALUES ('ab'), ('abcd'), ('ab  ');SELECT concat('(', te, ')')
FROM tmp9;

1.6、二进制类型

PostgreSQL提供了BYTEA类型，用于存储二进制字符串。BYTEA类型存储空间为4字节加上实际的二进制字符串。

CREATE TABLE tmp10
(b BYTEA
);INSERT INTO tmp10
VALUES (E'\\000');SELECT * FROM tmp10;

1.7、布尔类型

PostgreSQL提供了BOOLEAN布尔数据类型。BOOLEAN用一个字节来存储，提供了TRUE(真)和FALSE(假)两个值。

另外，用户可以使用其他有效文本值替代TRUE和FALSE。替代TRUE的文本值为：'t'、'tue'、'y'、'yes'和'1'。替代FALSE的文本值为：'f'、'false'、'n'、'no'和'0'。

CREATE TABLE tmp11
(b BOOLEAN
);INSERT INTO tmp11
VALUES(TRUE), (FALSE), ('y'), ('no'), ('0');SELECT * FROM tmp11;

1.8、数组类型

PostgreSQL允许将字段定义成定长或变长的一维或多维数组。数组类型可以是任何基本类型或用户定义类型。

1. 声明数组：

在PostgreSQL中，一个数组类型是通过在数组元素类型名后面附加中括号来命名的。例如如下命名SQL语句：

numb INT[],
xuehao TEXT[][],
zuoye TEXT[4][4];

其中，numb字段为一维INT数组：xuehao字段为二维TEXT数组：zuoye字段为二维TEXT数组，并且声明了数组的长度。不过，目前PostgreSQL并不强制数组的长度，所以声明长度和不声明长度是一样的。

另外对于一维数组，也可以使用SQL标准声明，SQL语句如下：

PAY_BY_QUARTER INT ARRAY[5];

2. 插入数组数值：
插入数组元素时，用大括号把数组元素括起来并且用逗号将它们分开。

CREATE TABLE tmp12
(bt int[]);INSERT INTO tmp12 
VALUES ('{{1,1,1}, {2,2,2}, {3,3,3}}');SELECT * FROM tmp12;

2、如何选择数据类型

2.1、整数和浮点数

如果不需要小数部分，则使用整数来保存数据：如果需要表示小数，则使用浮点数类型。对于浮点数据列，存入的数值会被该列定义的小数位进行四舍五入。例如，如果列的值的范围为1~99999，若使用整数，则NT是最好的类型：若需要存储小数，则使用浮点数类型。

2.2、日期与时间类型

PostgreSQL对于不同种类的日期和时间有很多的数据类型，比如TIME和DATE。如果只记录时间，则使用TME类型即可：如果只记录日期，只需使用DATE类型。

如果同时需要记录日期和时间，则可以使用TIMESTAMP类型。默认情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，PostgreSQL会把TIMESTAMP列设为当前的时间，因此当需要插入记录时、同时插入当前时间，使用TIMESTAMP是方便的。

2.3、CHAR与VARCHAR之间的特点与选择

CHAR和VARCHAR的区别：CHAR是固定长度字符，VARCHAR是可变长度字符。对于插入数据长度不够时，CHAR会自动填充插入数据的尾部空格，VARCHAR则不会。

由于CHAR是固定长度，所以它的处理速度比VARCHAR要快，但缺点就是浪费存储空间。

所以对于存储量不大，但在速度上有要求的数据可以使用CHAR类型，反之可以使用VARCHAR类型来实现。

3、运算符

3.1、概述

运算符是用来告诉PostgreSQL执行特定算术或逻辑操作的符号。PostgreSQL的内部运算符很丰富，主要有四大类，具体如下。

1. 算术运算符
   • 包括：+ - * / %
2. 比较运算符
   • 包括：> < = >= <= != IN、 BETWEEN AND、 GREATEST、 LEAST、 LIKE等
3. 逻辑运算符
   • 包括： TURE、 FALSE、 NOT、 AND、 OR
4. 位运算符
   • 包括： & | ~ ^ << >>

3.2、算数运算符

运算符	作用
+	加法运算
-	减法运算
*	乘法运算
/	触发运算，返回商
%	求余运算，返回余数

CREATE TABLE tmp14
(num INT);INSERT INTO tmp14 VALUES (64);-- 加减运算
SELECT num, num+10, num-10, num+5-3, num+36.5
FROM tmp14;

-- 乘除运算
SELECT num, num*2, num/2, num/3, num%3
FROM tmp14;

-- 除0
SELECT num, num/0, num%0
FROM tmp14;-- ERROR:  division by zero 

3.3、比较运算符

运算符	作用
=	等于
<> (!=)	不等于
<=	小于等于
>=	大于等于
>	大于
<	小于
LEAST	在有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是否是IN列表中的任意一个值
LIKE	通配符匹配

3.3.1、等于运算符=

“=”用来判断数字、字符串和表达式是否相等。如果相等，返回值为t,否则返回f。

SELECT 1-0, '2'=2, 2=2, 'b'='b', (1+3)=(2+1), NULL=NULL;

• 若有一个或两个参数为NULL,则比较运算的结果为空。
• 若同一个比较运算中的两个参数都是字符串，则按照字符串进行比较
• 若两个参数均为整数，则按照整数进行比较
• 若一个字符串和数字进行相等判断，则PostgreSQL可以自动将字符串转换为数字

3.3.2、不等于运算符<>或!=

“◇”或者“！=”用于判断数字、字符串、表达式是否不相等。如果不相等，返回值为t,否则返回f。这两个运算符不能用于判断空值NULL。

SELECT 'good' <> 'god', 1 <> 2, 4 != 4, 5.5 != 5, (1+3) != (2+1), NULL <> NULL;

3.3.3、小于等于运算符<=

“<=”用来判断左边的操作数是否小于或者等于右边的操作数。如果小于或者等于，返回值为t,否则返回f。“<=”不能用于判断空值NULL。

SELECT 'good' <= 'god', 1 <= 2, 4 <=4, 5.5<=5, (1+3) <= (2+1), NULL <= NULL;

3.3.4、小于运算符<

“<”运算符用来判断左边的操作数是否小于右边的操作数。如果小于，返回值为，否则返回f。“<”不能用于判断空值NULL。

SELECT 'good' < 'god', 1 < 2, 4 < 4, 5.5 < 5, (1+3) < (2+1), NULL < NULL;

3.3.5、大于等于运算符>=

“>=”运算符用来判断左边的操作数是否大于或者等于右边的操作数，如果大于或者等于，返回值为t,否则返回f。“>=”不能用于判断空值NULL。

SELECT 'good' >= 'god', 1 >= 2, 4 >= 4, 5.5 >= 5, (1+3) >= (2+1), NULL >= NULL;

3.3.6、大于运算符>

“>”运算符用来判断左边的操作数是否大于右边的操作数。如果大于，返回值为，否则返回f。“>”不能用于判断空值NULL。

SELECT 'good' > 'god', 1 > 2, 4 > 4, 5.5 > 5, (1+3) > (2+1), NULL > NULL;

3.3.7、BETWEEN AND运算符

BETWEEN AND运算符的语法格式为：expr BETWEEN min AND max。假如expr大于或等于min且expr小于或等于max,则BETWEEN的返回值为t,否则返回f。

SELECT 4 BETWEEN 2 AND 5, 4 BETWEEN 4 AND 6, 12 BETWEEN 9 AND 10;

由结果可以看到，4在端点值区间内或者等于其中一个端点值时，BETWEEN AND表达式返回值为t:12并不在指定区间内，因此返回f。

SELECT 'x' BETWEEN 'f' AND 'g', 'b' BETWEEN 'a' AND 'c';

对于字符串类型的比较，按字母表中字母顺序进行比较，'x不在指定的字母区间内，因此返回f:而b’位于指定字母区间内，返回t。

3.3.8、LEAST运算符

LEAST运算符的语法格式为：LEAST(值1，值2，值n),其中，值n表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值：假如任意一个值为NULL,在比较中忽略不计。

SELECT least(2, 0), least(20.0, 3.0, 100.5), least('a', 'c', 'b'), least(10, NULL);

3.3.9、GREATEST运算符

GREATEST运算符的语法格式为：GREATEST(值l,值2，值n),其中，值n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值：假如任意一个值为NULL,在比较中忽略
不计。

SELECT greatest(2,0), greatest(20.0, 3.0, 100.5), greatest('a', 'c', 'b'), greatest(10, NULL);

3.3.10、IN、NOT IN运算符

• IN运算符用来判断操作数是否为N列表中的一个值。如果是，返回值为，否则返回值为f。
• NOTN运算符用来判断操作数是否不是N列表中的一个值。如果不是，返回值为t,否则返回值为f。

SELECT 2 IN (1, 2, 5, 8), 3 IN (1, 2, 5, 9);

SELECT 2 NOT IN (1, 2, 5, 8), 3 NOT IN (1, 2, 5, 9);

SELECT NULL IN (1, 3, 5), 10 IN (1, 3, NULL);

3.3.11、LIKE

LIKE运算符用来匹配字符串，语法格式为：expr LIKE匹配条件，如果epxr满足匹配条件，则返回t(TRUE),如果不匹配，则返回f(FALSE)。若expr或匹配条件中任何一个为NULL,则结果为空值。

• %，匹配任何数目的字符，甚至包括零字符。
• _，只能匹配一个字符。

SELECT 'stud' LIKE 'stud', 'stud' LIKE 'stu_', 'stud' LIKE '%d', 'stud' LIKE 't___', 's' LIKE NULL;

3.4、逻辑运算符

运算符	作用
NOT	逻辑非
AND	逻辑与
OR	逻辑或

3.4.1、NOT

逻辑非运算符NOT表示当操作数为TRUE时，所得值为f:当操作数为FALSE时，所得值为t:当操作数为NULL时，所得的返回值为空值。

SELECT NOT '1', NOT 'y', NOT '0', NOT NULL, NOT 'n';

3.4.2、AND

逻辑与运算符AND表示当所有操作数均为TRUE、并且不为NULL时，计算所得结果为t:当一个或多个操作数为FALSE时，所得结果为f:其余情况返回值为空值。

SELECT '1' AND 'y', '1' AND '0', '1' AND NULL, '0' AND NULL;

3.4.3、OR

逻辑或运算符OR表示当两个操作数均为非NULL值时，任意一个操作数为TRUE,则结果为t,否则结果为f:当有一个操作数为NULL时，如果另一个操作数为TRUE,则结果为t,否则结果为空值：当两个操作数均为NULL时，则所得结果为空值。

SELECT '1' OR 't' OR '0', '1' OR 'y', '1' OR NULL, '0' OR NULL, NULL OR NULL;

3.5、位运算符

运算符	作用
||	连接
&	位与
|	位或
#	位异或
~	位非
<<	位左移
>>	位右移

SELECT '10001' || '011',10001 & 01101,10001 | 01101,10001 # 01101,~10001,10001 << 3,10001 >> 2;

3.6、运算符的优先级

优先级	运算符
最低	=（赋值运算符）, :=
	| OR
	AND
	NOT
	BETWEEN CASE WHEN ELSE
	=（比较运算） >= > <= < <> != IS LIKE IN
	- +
	* / %
	-（负号）
最高	!