PostgreSQL：五. 数据类型和运算符

ゝ一纸荒年。 2023-05-21 07:16 1阅读 0赞

### 五. 数据类型和运算符 ###

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

#### 5.1 PostgreSQL数据类型介绍 ####

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

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

2.浮点数类型  
实际工作中很多地方需要用到带小数的数值，PostgreSQL使用浮点数来表示小数。浮点数类型有两种：REAL和DOUBLE PRECISION。PostgreSQL也支持使用标准的SQL语法，即：float和float§来声明非精确的数值类型，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()函数。时间、时区输入参考如下。

时间输入：  
示例 描述  
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类型用于允许存储二进制字串。

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型数据显示。
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'::bit(3), B'101');
        SELECT * FROM test;

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构造器
3.  数组类型数据的查询  
    和其他语言一样，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;

6、复合类型

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

常见数据类型总结表：

<table> 
 <thead> 
  <tr> 
   <th>类型名称</th> 
   <th>存储空间</th> 
   <th>数据范围</th> 
   <th>说明</th> 
   <th>案例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SMALLINT</td> 
   <td>2字节</td> 
   <td>-32768~32767</td> 
   <td>小范围整型</td> 
   <td>如：例5.1</td> 
  </tr> 
  <tr> 
   <td>INT(INTEGER)</td> 
   <td>4字节</td> 
   <td>－2147483648〜2147483647</td> 
   <td>普通整型</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>8字节</td> 
   <td>-9223372036854775808〜9223372036854775807</td> 
   <td>大整数</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>REAL</td> 
   <td>4字节</td> 
   <td>6位十进制数字精度</td> 
   <td>浮点数类型</td> 
   <td>如：例5.2</td> 
  </tr> 
  <tr> 
   <td>DOUBLE PRECISION</td> 
   <td>8字节</td> 
   <td>15位十进制数字精度</td> 
   <td>双精度浮点数类型</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>NUMERIC(M,N)</td> 
   <td>变长</td> 
   <td>任意</td> 
   <td>任意精度类型</td> 
   <td>如：例5.3</td> 
  </tr> 
  <tr> 
   <td>SERIAL</td> 
   <td>4字节</td> 
   <td>1 ~ +2147483647</td> 
   <td>自增整数</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>BIGSERIAL</td> 
   <td>8字节</td> 
   <td>1 ~ +9223372036854775807</td> 
   <td>大范围的自增整数</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>8字节</td> 
   <td>00:00:00 ~ 24:00:00</td> 
   <td>时间类型(只用于一天内的时间)［无时区］</td> 
   <td>如：例5.4和例5.5，5.6</td> 
  </tr> 
  <tr> 
   <td>TIME[with time zone]</td> 
   <td>12字节</td> 
   <td>00:00:00+1359~24:00:00-1359</td> 
   <td>时间类型(只用于一天内的时间)［含时区］</td> 
   <td>例5.11</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>4字节</td> 
   <td>4713 BC~32767 AD</td> 
   <td>只用于一天的日期</td> 
   <td>如：例5.7，5.8，5.9</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>8字节</td> 
   <td>4713 BC~5874897 AD</td> 
   <td>日期和时间［无时区］ 1毫秒/14位</td> 
   <td>如：例5.10</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP[with time zone]</td> 
   <td>8字节</td> 
   <td>4713 BC~5874897 AD</td> 
   <td>日期和时间［含时区］</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>INTERVAL</td> 
   <td>12字节</td> 
   <td>-178000000年~178000000年</td> 
   <td>时间间隔(1毫秒/14位)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>4字节</td> 
   <td>YYYY-MM-DD或YYYYMMDD</td> 
   <td>日期类型</td> 
   <td>如：例5.7，5.8，5.9</td> 
  </tr> 
  <tr> 
   <td>char(n)/character(n)</td> 
   <td>用户定义</td> 
   <td>定长，有长度限制</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>varchar(n)/character varying(n)</td> 
   <td>用户定义</td> 
   <td>变长，有长度限制</td> 
   <td></td> 
   <td>如：例5.12</td> 
  </tr> 
  <tr> 
   <td>text</td> 
   <td>实际长度</td> 
   <td>变长，无长度限制</td> 
   <td></td> 
   <td>如：例5.13</td> 
  </tr> 
  <tr> 
   <td>tytea</td> 
   <td>4字节加上实际的二进制字串</td> 
   <td>变长，无长度限制</td> 
   <td></td> 
   <td>如：例5.14</td> 
  </tr> 
  <tr> 
   <td>boolean</td> 
   <td>1字节</td> 
   <td>‘真’(‘True’) 或 ‘假’(‘False’)</td> 
   <td></td> 
   <td>如：例5.15</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

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

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

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

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

【例5.3】创建表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进行了四舍五入的处理。  
【例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');

【例5.5】表tmp4中插入值‘101112’，SQL语句如下：  
向表中插入数据，SQL语句如下：

INSERT INTO tmp4 values('101112');

也可以使用系统日期函数向TIME字段列插入值。  
【例5.6】向tmp4表中插入系统当前时间，SQL语句如下：  
由于由时间函数获得的时间是带时区的，所以需要先将字段属性修改为带时区类型的时间：

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

删除表中的数据：

DELETE FROM tmp4;

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

INSERT INTO tmp4 values (CURRENT_TIME) ,(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');

【例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');

【例5.9】向tmp5表中插入系统当前日期，SQL语句如下：  
首先删除表中的数据：

DELETE FROM tmp5;

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

INSERT INTO tmp5 values(NOW() );

NOW()函数返回日期和时间值，在保存到数据库时，只保留了其日期部分。  
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() );

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

【例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.13】创建tmp9表，定义字段te数据类型依次为TEXT，向表中插入不同长度的字符串，SQL语句如下：  
创建表tmp9：

CREATE TABLE tmp9(te  TEXT);

输入数据：

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

查询结果：

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

【例5.15】创建表tmp11，定义BYTEA类型的字段b，向表中插入布尔型数据“TRUE”和“FALSE”。  
首先创建表tmp11，SQL语句如下：

CREATE TABLE tmp11( b BOOLEAN );

插入数据：

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

【例5.16】创建表tmp12，定义数组类型的字段bt，向表中插入一些数组数值。  
首先创建表tmp12，SQL语句如下：

CREATE TABLE tmp12( bt int[]);

插入数据：

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

#### 5.2 常见运算符介绍 ####

1、算数运算符：包括加（+），减（-），乘（\*），除（/），求余（或模运算，%，返回余数）  
例5.16】创建表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;

【例5.17】对tmp14表中的num进行乘法，除法运算。

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

【例5.18】用0除num。

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

2、比较运算符：

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>作用</th> 
   <th>案例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
   <td>如：例5.19</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt;(!=)</td> 
   <td>不等于</td> 
   <td>如：例5.20</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于</td> 
   <td>如：例5.21</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于</td> 
   <td>如：例5.23</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
   <td>如：例5.22</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
   <td>如：例5.24</td> 
  </tr> 
  <tr> 
   <td>LEAST</td> 
   <td>有两个或多个参数时，返回最小值</td> 
   <td>如：例5.27</td> 
  </tr> 
  <tr> 
   <td>GREATEST</td> 
   <td>有两个或多个参数时，返回最大值</td> 
   <td>如：例5.28</td> 
  </tr> 
  <tr> 
   <td>BETWEEN…AND…</td> 
   <td>判断一个值是否在两个值中间</td> 
   <td>如：例5.25，5.26</td> 
  </tr> 
  <tr> 
   <td>IN（NOT IN）</td> 
   <td>判断一个值是IN列表中的任意一个值</td> 
   <td>如：例5.29，5.30，5.31</td> 
  </tr> 
  <tr> 
   <td>LIKE</td> 
   <td>通配符匹配</td> 
   <td>如：例5.32</td> 
  </tr> 
 </tbody> 
</table>

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

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

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

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

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

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

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

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

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

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

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

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