5.1 PostgreSQL数据类型介绍
PostgreSQL支持多种数据类型，主要有整数类型、浮点数类型、任意精度数值、日期／时间类型、字符串类型、二进制类型、布尔类型和数组类型等。本节主要介绍这些常见类型的使用方法。
5.1.1 整数类型
数值型数据类型主要用来存储数字。PostgreSQL提供多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，其所需要的存储空间越大。PostgreSQL主要提供的整数类型有MALLINT，INT（INTEGER）和BIGINT。
表5.1列出了PostgreSQL中的整数类型。
表5.1 PostgreSQL中的整数型数据类型
从中可以看到，不同类型整数存储所需的字节数是不同的，占用字节数最小的是MALLINT类型，占用字节最大的是BITINT类型。相应的，占用字节越多的类型所能表示的数值范围越大。根据占用字节数可以求出每一种数据类型的取值范围，例如MALLINT需要2字节（16 bits）来存储，那么MALLINT的最大值为215-1，即32767。其他类型的整数的取值范围计算方法相同，如表5.2所示。
表5.2 不同整数类型的取值范围
【例5.1】创建表tmp1，其中字段x、y、z的数据类型依次为SMALLINT、INT、BIGINT，SQL语句如下：
CREATE TABLE tmp1 (x SMALLINT, y INT, z BIGINT );
语句执行成功之后，创建3种整数类型的字段。
不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此应该根据实际需要选择合适的类型，这样有利于提高查询的效率和节省存储空间。整数类型是不带小数部分的数值，现实生活中很多地方需要用到带小数的数值，下面将介绍PostgreSQL中支持的小数类型。
5.1.2 浮点数类型
PostgreSQL中使用浮点数来表示小数。浮点类型有两种：REAL和DOUBLE PRECISION。它们的含义如表5.3所示。
表5.3 PostgreSQL中的小数类型
在大多数系统平台上，REAL类型的范围是1E-37到1E+37，精度至少是6位小数。DOUBLE PRECISION的范围通常是1E-307到1E+308，精度至少是15位数字，太大或者太小的数值都会导致错误。
PostgreSQL也支持SQL标准表示法float和float(p)，用于声明非精确的数值类型。其中，p声明以二进制位表示的最低可接受精度。在选取real类型的时候，PostgreSQL接受float(1)到float(24)，在选取double precision的时候，接受float(25)到float(53)。在允许范围之外的p值将导致一个错误。没有声明精度的float将被当作double precision。
【例5.2】创建表tmp2，其中字段x、y、z的数据类型依次为FLOAT(5)、REAL和DOUBLE PRECISION，SQL语句如下：
CREATE TABLE tmp2 (x FLOAT(5), y REAL, z DOUBLE PRECISION );
语句执行成功之后，创建3种浮点类型的字段。
提 示
在PostgreSQL中，在浮点类型中有几个特殊值：Infinity表示正无穷大，-Infinity表示负无穷大，NaN表示不是一个数字。
5.1.3 任意精度类型
在PostgreSQL中，使用NUMERIC（M，N）表示任意精度类型的数值。其中，M称为精度，表示总共的位数；N称为标度，表示小数的位数。例如，563.186的精度为6、标度为3。
NUMERIC的有效取值范围由M和D的值决定。如果改变M而固定D，则其取值范围将随M的变大而变大。另外，如果用户指定的精度超出精度外，就会进行四舍五入处理。
【例5.3】创建表tmp3，其中字段x、y数据类型依次为NUMERIC(5,1)和NUMERIC (5,2)，向表中插入数据9.12、9.15。创建表tmp3，SQL语句如下：
CREATE TABLE tmp3 ( x NUMERIC (5,1), y NUMERIC (5,2));
向表中插入数据，SQL语句如下：
INSERT INTO tmp3 VALUES(9.12, 9.15);
查看表中的数据，SQL语句如下：
SELECT * FROM tmp3;
图5-1 查看表中的数据
语句执行后，结果如图5-1所示。可以看到PostgreSQL对插入的数据9.12进行了四舍五入处理。
5.1.4 日期与时间类型
PostgreSQL中有多种表示日期的数据类型，主要有TIME、DATE、TIMESTAMP和INTERVAL。每一个类型都有合法的取值范围，当指定确实不合法的值时系统将“零”值插入数据库中。本节将介绍PostgreSQL日期和时间数据类型的使用方法。表5.4列出了PostgreSQL中的日期与时间类型。
表5.4 日期／时间数据类型
提 示
在格里高利历法里没有零年，所以数字上的1BC是公元零年。
另外，对于TIME和TIMESTAMP类型，默认情况下为without time zone（不带时区），如果需要，可以设置为带时区（with time zone）。
1. TIME
图5-2 SQL语句执行结果
TIME类型用于只需要时间信息的值，在存储时需要8字节，格式为HH:MM:SS。其中，HH表示小时；MM表示分钟；SS表示秒。TIME类型的取值范围为00:00:00~24:00:00。
【例5.4】创建数据表tmp4，定义数据类型为TIME的字段t，向表中插入值‘10:05:05’、‘23:23’。
创建表tmp4，SQL语句如下：
CREATE TABLE tmp4( t TIME );
向表中插入数据，SQL语句如下：
INSERT INTO tmp4 values('10:05:05 '), ('23:23');
查看结果，SQL语句如下：
SELECT * FROM tmp4;
语句执行后，结果如图5-2所示。
由结果可以看到，‘10:05:05’被转换为10:05:05；‘23:23’被转换为23:23:00。
【例5.5】向表tmp4中插入值‘101112’，SQL语句如下：
向表中插入数据，SQL语句如下：
INSERT INTO tmp4 values('101112');
查看结果，SQL语句如下：
SELECT * FROM tmp4;
语句执行后，结果如图5-3所示。
图5-3 SQL语句执行结果
由结果可以看到，‘101112’被转换为10:11:12。
也可以是用系统日期函数向TIME字段列插入值。
【例5.6】向tmp4表中插入系统当前时间，SQL语句如下：
由于由时间函数获得的时间是带时区的，因此需要先将字段属性修改为带时区类型的时间：
删除表中的数据：
DELETE FROM tmp4;
向表中插入数据，SQL语句如下：
INSERT INTO tmp4 values (CURRENT_TIME) ,(NOW());
查看结果，SQL语句如下：
SELECT * FROM tmp4;
语句执行后，结果如图5-4所示。
由结果可以看到，获取系统当前的日期时间插入到TIME类型列，因为读者输入语句的时间不确定，所以获取的值与这里的可能不同，但都是系统当前的日期时间值，并显示所在的时区。
图5-4 SQL语句执行结果
2. DATE类型
DATE类型用在仅需要日期值时，没有时间部分，在存储时需要4字节，日期格式为‘YYYY-MM-DD’。其中，YYYY表示年；MM表示月；DD表示日。在给DATE类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATE的日期格式即可。
（1）以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期。例如，输入‘2012-12-31’或者‘20121231’，插入数据库的日期都为2012-12-31。
（2）以‘YY-MM-DD’或者‘YYMMDD’字符串格式表示的日期。在这里，YY表示两位的年值，包含两位年值的日期会令人模糊，因为不知道世纪。PostgreSQL使用以下规则解释两位年值：‘00~69’范围的年值转换为‘2000~2069’；‘70~99’范围的年值转换为‘1970~1999’。例如，输入‘12-12-31’，插入数据库的日期为2012-12-31；输入‘981231’，插入数据的日期为1998-12-31。
（3）利用CURRENT_DATE或者NOW()插入当前系统日期。
【例5.7】创建数据表tmp5，定义数据类型为DATE的字段d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期，SQL语句如下：
首先，创建表tmp5：
CREATE TABLE tmp5(d DATE);
向表中插入“YYYY-MM-DD“和“YYYYMMDD”格式日期：
INSERT INTO tmp5 values('1998-08-08'),('19980808'),('20101010');
查看插入结果：
SELECT * FROM tmp5;
语句执行后，结果如图5-5所示。
图5-5 SQL语句执行结果
可以看到各个不同类型的日期值都正确地插入到了数据表中。
【例5.8】向tmp5表中插入“YY-MM-DD“和“YYMMDD”字符串格式日期，SQL语句如下：
首先，删除表中的数据：
DELETE FROM tmp5;
向表中插入“YY-MM-DD“和“YYMMDD”格式日期：
INSERT INTO tmp5 values('99-09-09'),( '990909'), ( '000101') ,( '121212');
查看插入结果：
SELECT * FROM tmp5;
语句执行后，结果如图5-6所示。
图5-6 SQL语句执行结果
【例5.9】向tmp5表中插入系统当前日期，SQL语句如下：
删除表中的数据：
DELETE FROM tmp5;
向表中插入系统当前日期：
INSERT INTO tmp5 values(NOW() );
查看插入结果：
SELECT * FROM tmp5;
语句执行后，结果如图5-7所示。
图5-7 SQL语句执行结果
NOW()函数返回日期和时间值，在保存到数据库时，只保留了其日期部分。
3. TIMESTAMP
TIMESTAMP的日期格式为YYYY-MM-DD HH:MM:SS。在存储时需要8字节，因此在插入数据时要保证在合法的取值范围内。
【例5.10】创建数据表tmp7，定义数据类型为TIMESTAMP的字段ts，向表中插入值‘1996-02-02 02:02:02’、NOW()，SQL语句如下：
创建数据表和字段：
CREATE TABLE tmp7( ts TIMESTAMP);
向表中插入数据：
INSERT INTO tmp7 values ('1996-02-02 02:02:02'),( NOW() );
查看插入结果：
SELECT * FROM tmp7;
语句执行后，结果如图5-8所示。
图5-8 SQL语句执行结果
由结果可以看到，‘1996-02-02 02:02:02’被转换为1996-02-02 02:02:02；NOW()被转换为系统当前日期时间2019-03-13 13:38:55。
4. 创建带时区的日期和时间类型
【例5.11】创建数据表tmp7h，定义数据类型为TIME的字段th，向表中插入值‘10:05:05 PST’、‘10:05:05’。
创建表tmp7h，SQL语句如下：
CREATE TABLE tmp7h( t TIME with time zone);
向表中插入数据，SQL语句如下：
INSERT INTO tmp7h values('10:05:05 PST '), ('10:05:05');
查看结果，SQL语句如下：
SELECT * FROM tmp7h;
语句执行后，结果如图5-9所示。
图5-9 SQL语句执行结果
由结果可以看到，创建了带时区的时间类型，其中PST为西8区。如果不指定时区，默认为东8区。另外，带时区的日期类型的创建与之相似，这里不再重复讲述。
5.1.5 字符串类型
字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。字符串可以进行区分或者不区分大小写的串比较，另外还可以进行模式匹配查找。在PostgreSQL中，字符串类型是指CHAR、VARCHAR和TEXT。表5.5列出了PostgreSQL中的字符串数据类型。
表5.5 PostgreSQL中字符串数据类型
1. CHARACTER(n)和CHARACTER VARYING(n)
其中，n是一个正整数。CHARACTER(n)和CHARACTER VARYING(n)都可以存储最多n个字符的字符串。试图存储更长的字符串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字符串将被截断为最大长度。如果要存储的字符串比声明的长度短，类型为character的数值将会用空白填满；而类型为CHARACTER VARYING的数值将只存储短些的字符串。
【例5.12】创建tmp8表，定义字段ch和vch数据类型依次为CHARACTER（4）、CHARACTER VARYING（4），向表中插入不同长度的字符串，SQL语句如下：
创建表tmp8：
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;
语句执行后，结果如图5-10所示。
图5-10 SQL语句执行结果
从查询结果可以看到，ch在保存“ab”时在右侧填充空格以达到指定的长度，而vch字段仅仅保留了“ab”。
CHARACTER类型中填充的空白是无意义的。例如，在比较两个CHARACTER值的时候，填充的空白都会被忽略，在转换成其他字符串类型的时候，CHARACTER值里面的空白会被删除。注意，在CHARACTER VARYING和TEXT数值里，结尾的空白是有意思的。
提 示
上例中的concat()函数的语法为CONCAT(str1,str2,…)，返回结果为连接参数产生的字符串。
如果插入的字符长度超过规定的长度，例如插入字符为“abcde”，代码如下：
INSERT INTO tmp8 VALUES('abcde', 'abcde')
语句执行后，【消息】窗口中显示错误信息，如图5-11所示。可见系统会阻止这个数值的插入，并且提示语法错误，说明插入的字符串长度已经大于可以插入的最大值。
图5-11 SQL语句执行结果
2. TEXT类型
PostgreSQL中的TEXT类型可以存储任何长度的字符串。尽管类型TEXT不是SQL标准，但是许多其他SQL数据库系统中也有。
【例5.13】创建tmp9表，定义字段te，数据类型为TEXT，向表中插入不同长度的字符串，SQL语句如下：
创建表tmp9：
CREATE TABLE tmp9(te TEXT);
输入数据：
INSERT INTO tmp9 VALUES('ab'),('abcd'),('ab ');
查询结果：
SELECT concat('(', te, ')') FROM tmp9;
语句执行后，结果如图5-12所示。
图5-12 SQL语句执行结果
5.1.6 二进制类型
PostgreSQL支持两类字符型数据：文本字符串和二进制字符串。前面讲解了存储文本的字符串类型，这一节将讲解PostgreSQL中存储二进制数据的数据类型。
PostgreSQL提供了BYTEA类型，用于存储二进制字符串。BYTEA类型存储空间为4字节加上实际的二进制字符串。
【例5.14】创建表tmp10，定义BYTEA类型的字段b，向表中插入二进制数据“E'\\000'”。
创建表tmp10，SQL语句如下：
CREATE TABLE tmp10( b BYTEA );
插入数据：
INSERT INTO tmp10 VALUES(E'\\000');
查询插入结果：
SELECT * FROM tmp10;
语句执行后，结果如图5-13所示。
图5-13 SQL语句执行结果
5.1.7 布尔类型
PostgreSQL提供了BOOLEAN布尔数据类型。BOOLEAN用1字节来存储，提供了TRUE（真）和FALSE（假）两个值。
另外，用户可以使用其他有效文本值替代TRUE和FALSE。替代TRUE的文本值为't'、'true'、'y'、'yes'和'1'，替代FALSE的文本值为'f'、'false'、'n'、'no'和'0'。
【例5.15】创建表tmp11，定义BYTEA类型的字段b，向表中插入布尔型数据“TRUE”和“FALSE”。
创建表tmp11，SQL语句如下：
CREATE TABLE tmp11( b BOOLEAN );
插入数据：
INSERT INTO tmp11 VALUES(TRUE),(FALSE),('y'),('no'),('0');
查询插入结果：
SELECT * FROM tmp11;
图5-14 SQL语句执行结果
语句执行后，结果如图5-14所示。
5.1.8 数组类型
PostgreSQL允许将字段定义成定长或变长的一维或多维数组。数组类型可以是任何基本类型或用户定义类型。
1. 声明数组
在PostgreSQL中，一个数组类型是通过在数组元素类型名后面附加方括弧来命名的。例如：
其中，numb字段为一维INT数组，xuehao字段为二维TEXT数组，zuoye字段为二维TEXT数组，并且声明了数组的长度。不过，目前PostgreSQL并不强制数组的长度，所以声明长度和不声明长度是一样的。
另外，对于一维数组，也可以使用SQL标准声明，SQL语句如下：
PAY_BY_QUARTER INT ARRAY[5];
此种声明方式仍然不强制数组的长度。
2. 插入数组数值
插入数组元素时，用大括号把数组元素括起来并且用逗号将它们分开。
【例5.16】创建表tmp12，定义数组类型的字段bt，向表中插入一些数组数值。
创建表tmp12，SQL语句如下：
CREATE TABLE tmp12( bt int[]);
插入数据：
INSERT INTO tmp12 VALUES('{{1,1,1},{2,2,2},{3,3,3}}');
查询插入结果：
SELECT * FROM tmp12;
语句执行后，结果如图5-15所示。
图5-15 SQL语句执行结果