进阶数据库系列（六）：PostgreSQL 数据类型与运算符

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

PostgreSQL 数据类型介绍

数值类型：整数类型、浮点数类型、任意精度类型。

整数类型

整数类型包括：SMALLINT、INT(INTEGER)、BIGINT三种，三者在取值空间和存储范围上有所不同，不同的存储空间也决定了不同的查询效率。应根据实际需要选择最适合的类型，以在查询效率和存储空间上有所平衡。

浮点数类型

实际工作中很多地方需要用到带小数的数值，PostgreSQL使用浮点数来表示小数。浮点数类型有两种：REAL和DOUBLE PRECISION。PostgreSQL也支持使用标准的SQL语法，即：float和float§来声明非精确的数值类型，p表示可接受的精度。REAL类型对应float(1)~float(24)，DOUBLE PRECISION对应float(25)~float(53)，未声明精度时将被当作DOUBLE PRECISION处理。

任意精度类型

NUMERIC表示任意精度类型，PostgreSQL中任意精度类型可存储最多1000位精度的数字并且准确地进行计算，非常适合用于货币金额和其它要求计算准确的数据，但是，NUMERIC类型的运算速度要比整数类型或者浮点数类型要慢很多。使用格式为：NUMERIC(M,N)。其中，M称为精度，表示总位数；N称为标度，表示小数位。M和N决定了NUMERIC的取值范围，当用户数据的精度超出指定精度时，会进行四舍五入处理。

序号类型

序号类型SERIAL和BIGSERIAL并不是真正的类型， 只是为在表中设置唯一标识做的概念上的便利。在目前的实现中，下面一句话：

CREATE TABLE tablename (colname SERIAL);

等价于：

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename(
    colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);

日期时间类型

PostgreSQL保存日期时间格式的数据类型有多种，主要有：TIME、DATE、TIMESTAMP、INTERVAL。日期时间类型输入像字符串类型输入一样，需要加单引号。每种日期时间类型都有合法的取值范围，超出范围时系统会将"零"插入数据记录中。

时间类型

时间类型是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的缩写

DATE类型

DATE类型用于仅需要日期值时，日期输入格式为：YYYY-MM-DD、YYYYMMDD。输入DATE类型数据时，可以使用字符串或数字类型的数据输入，符合DATE的日期格式即可。可以使用CURRENT_DATE或NOW()插入当前系统时间。

TIMESTAMP类型

时间戳类型的有效输入由一个日期和时间的联接组成，后面跟着一个可选的时区，一个可选的 AD 或者 BC。时间戳输入格式为：YYYY-MM-DD HH:MM:SS。带时区输入格式为：2019-03-22 4:05:06 -8:00

INTERVAL类型

INTERVAL类型输入格式如下：

quantity unit [quantity unit…] [direction]

字符串类型、二进制类型

PostgreSQL支持两种字符型数据：文本字符串和二进制字符串。PostgreSQL提供了三种存储字符串的类型：char、varchar、text。bytea类型用于允许存储二进制字串。

char和varchar类型

两种类型最多存储用户自定义长度N个字符。超出长度会产生错误，存储字符小于定义长度时，CHAR类型会用空格补满存储空间，VARCHAR类型只存储实际长度。

text类型

TEXT不是标准的SQL类型，许多数据库系统都实现了这一类型，在PostgreSQL中，TEXT可存储任意长度的字符串。

bytea类型

PostgreSQL提供了BYTEA类型，用于存储二进制字符串。BYTEA类型数据存储空间为用户实际二进制字符串加4字节。例如向file表file_byte字段插入E’\001’数据，SQL语句如下：

INSERT INTO file file_byte VALUES (E'\\001);

布尔类型、位串类型

PostgreSQL支持标准的 SQL boolean 数据类型，boolean值只能有两种： ‘真’(‘True’) 或 ‘假’(‘False’)。boolean有两种值外的第三种状态，‘未知’(‘Unknow’)，用 SQL空状态表示。位串就是一串 1 和 0 的字符串。它们可以用于存储和视觉化位掩码。

布尔类型

boolean类型存储空间为1字节，可接受的"真"值的有效文本输入值有：TRUE、‘t’、‘true’、‘y’、‘yes’、‘1’。"假"值可接受的有效输入值有：FALSE、‘f’、‘false’、‘n’、‘no’、‘0’。建议使用与SQL标准兼容的TRUE和FALSE做为boolean类型的输入值。查询时使用字母 t 和 f做为boolean型数据显示。

位串类型

有两种类型的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'::bit(3), B'101');
SELECT * FROM test;

数组类型

PostgreSQL 允许将字段定义成定长或不定长的多维数组，数组类型可以是任何基本类型或用户定义类型(复合类型和域的数组还不支持)。

数据类型定义

数组类型定义是通过在数组元素类型名后面国方括号：[]来命名的。PostgreSQL目前的实现并不强制限制数组长度，即：声明长度和未声明长度的数组相同。也不强制数组维数，特定元素类型的数组都被认为是相同的类型，不管他们的大小或者维数。创建数组类型字段示例如下：

CREATE TABLE array_tmp(
    name                 text,
    pay_by_quarter  integer[],
    schedule            text[][],
    squares             integer[3][3]  --指定长度后仍然不会限制长度
    );

数组类型数据插入

数组类型数据插入用大括号把值括起来并且用逗号将它们分开。可以在任意数组值周围添加双引号，如果值包含逗号或者花括弧，必须加上双引号输入。数据类型数据插入，还可以使用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构造器

数组类型数据的查询

和其他语言一样，PostgreSQL中数组也是通过下标数字的方式进行访问，只是PostgreSQL中数组元素的下标是从1开始n结束，格式如：[n]。对于一维或更多维数组，查询一个数组的部分数据查询格式为：[脚标界上:脚标下界]。

-- 数组下标定位查询及返回结果：
SELECT name, schedule  FROM array_tmp WHERE schedule[1][1]='value 11';
-- 查询多维数据的部分数据，
-- 如查询单维字段pay_by_quarter的第2,3列数据，及多维列schedule的第1维的1列数据和第2维第一列数据。SQL语句及查询结果如下：
SELECT pay_by_quarter[2:3],schedule[1:2][1:1] FROM array_tmp;

复合类型

PostgreSQL复合类型描述一行或者一条记录的结构，实际上它只是一个字段名和它们的数据类型的列表。在PostgreSQL中你可以像使用简单数据类型那样使用复合类型。

常见数据类型总结表：

案例：

创建表tmp1，其中字段x，y，z，数据类型依次为SMALLINT、INT、BIGINT,SQL语句如下：

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

创建表tmp2，其中字段x，y，z数据类型依次为FLOAT(5)、REAL和DOUBLE PRECISION，SQL语句如下：

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

创建表tmp3，其中字段x，y数据类型依次为NUMERIC(5,1) 和NUMERIC (5,2)和，向表中插入数据9.12、9.15，SQL语句如下：

CREATE TABLE tmp3 ( x NUMERIC (5,1), y NUMERIC (5,2));

向表中插入数据的SQL语句如下：

INSERT INTO tmp3 VALUES(9.12, 9.15);

查看表中的数据的SQL语句,可以看到PostgreSQL对插入的数据9.12进行了四舍五入的处理。

创建数据表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');

表tmp4中插入值‘101112’，SQL语句如下：

INSERT INTO tmp4 values('101112');

也可以使用系统日期函数向TIME字段列插入值。

向tmp4表中插入系统当前时间，SQL语句如下：

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

ALTER TABLE tmp4
   ALTER COLUMN t TYPE time without time zone;

删除表中的数据：

DELETE FROM tmp4;

向表中插入数据，SQL语句如下：

INSERT INTO tmp4 values (CURRENT_TIME) ,(NOW());

创建数据表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');

向tmp5表中插入“YY-MM-DD“和“YYMMDD”字符串格式日期，SQL语句如下： 首先删除表中的数据：

DELETE FROM tmp5;

向表中插入“YY-MM-DD“和“YYMMDD”格式日期：

INSERT INTO tmp5 values('99-09-09'),( '990909'), ( '000101') ,( '121212');

向tmp5表中插入系统当前日期，SQL语句如下：

首先删除表中的数据：

DELETE FROM tmp5;

向表中插入系统当前日期：

INSERT INTO tmp5 values(NOW() );

NOW()函数返回日期和时间值，在保存到数据库时，只保留了其日期部分。 TIMESTAMP的日期格式为YYYY-MM-DD HH:MM:SS。在存储时需要8个字节，因此在插入数据时，要保证在合法的取值范围内。

创建数据表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() );

创建数据表tmp7h，定义数据类型为TIME的字段th，向表中插入值‘10:05:05’，‘23:23’。

首先创建表tmp7h，SQL语句如下：

CREATE TABLE tmp7h( t TIME with time zone);

向表中插入数据，SQL语句如下：

INSERT INTO tmp7h values('10:05:05 PST '), ('10:05:05');

创建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;

创建tmp9表，定义字段te数据类型依次为TEXT，向表中插入不同长度的字符串，SQL语句如下：

创建表tmp9：

CREATE TABLE tmp9(te  TEXT);

输入数据：

INSERT INTO tmp9 VALUES('ab'),('abcd'),('ab  ');

查询结果：

SELECT concat('(', te, ')') FROM tmp9;

创建表tmp11，定义BYTEA类型的字段b，向表中插入布尔型数据“TRUE”和“FALSE”。

首先创建表tmp11，SQL语句如下：

CREATE TABLE tmp11( b BOOLEAN );

插入数据：

INSERT INTO tmp11 VALUES(TRUE),
(FALSE),
('y'),
('no'),
 ('0');

创建表tmp12，定义数组类型的字段bt，向表中插入一些数组数值。

首先创建表tmp12，SQL语句如下：

CREATE TABLE tmp12( bt int[]);

插入数据：

INSERT INTO tmp12 VALUES('{{1,1,1},{2,2,2},{3,3,3}}');

常见运算符介绍

算数运算符

包括加（+），减（-），乘（*），除（/），求余（或模运算，%，返回余数）

例：创建表tmp14，定义数据类型为INT的字段num，插入值64，对num值进行算术运算：

首先创建表tmp14，输入语句如下：

CREATE TABLE tmp14 ( num INT);

向字段num插入数据64：

INSERT INTO tmp14 VALUES (64);

接下来，对num值进行加法和减法运算：

SELECT num, num+10, num-10, num+5-3, num+36.5 FROM tmp14;

对tmp14表中的num进行乘法，除法运算。

SELECT num, num *2, num /2, num/3, num%3 FROM tmp14;

用0除num。

SELECT num, num / 0, num %0 FROM tmp14;

比较运算符：

使用“=”进行相等判断，SQL语句如下：

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

使用‘<>’和‘!=’进行不相等的判断，SQL语句如下：

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

使用‘<=’进行比较判断，SQL语句如下：

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

使用‘<’进行比较判断，SQL语句如下：

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

使用‘>=’进行比较判断，SQL语句如下：

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

使用‘>’进行比较判断，SQL语句如下：

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

使用BETWEEN AND 进行值区间判断，输入SQL语句如下：

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

使用BETWEEN AND 进行字符串的比较，输入SQL语句如下：

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

使用LEAST运算符进行大小判断，SQL语句如下：

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

使用GREATEST运算符进行大小判断，SQL语句如下：

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

使用NOT IN运算符进行判断，SQL语句如下：

SELECT 2 IN (1,3,5,9), 'thks' IN ('lius','thks');

使用NOT IN运算符进行判断，SQL语句如下：

SELECT 2 NOT IN (1,3,5), 'thks' NOT IN ('jsi','thks');

存在NULL值时的IN运算，SQL语句如下：

SELECT NULL IN (1,3,null),'abc' IN ('abc', null, 'ded');

使用运算符LIKE进行字符串匹配运算，SQL语句如下：

-- %匹配任何数目的字符；_只能匹配一个字符
SELECT 'stud' LIKE 'stud', 'stud' LIKE 'stu_','stud' LIKE '%d','stud' LIKE 't___', 's' LIKE NULL;

逻辑运算符

分别使用非运算符“NOT”进行逻辑判断，SQL语句如下：

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

分别使用与运算符“AND”进行逻辑判断，SQL语句如下：

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

使用或运算符“OR”进行逻辑判断，SQL语句如下：

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

常见问题及解答

疑问1：PostgreSQL可以存储文件吗？

PostgreSQL中的TEXT字段可以存储数据量较大的文件，可以使用这些数据类型存储图像，声音或者是大容量的文本内容，例如网页或者文档。但是对这些字段的处理会降低数据库的性能，如果不是非必要，可以选择只存储文件的路径。

参考文章：https://cuiyonghua.blog.csdn.net/article /details/105847406