第4章 了解数据类型

以恰当格式储存数据是建立可用数据库和实施准确分析的基础，因此了解数据类型至关重要。每当我钻研一个新数据库的时候，我都会检查每张表每个列所属的数据类型。在好运的情况下，我手上会有一份数据字典：这份文档会告诉我每个列到底是数字、字符还是其他类型，并对列的值进行解释。可惜的是，很多组织并没有创建和维护良好的文档，因此我听到“我们没有数据字典”的情况并不少见。在这种情况下，我会使用pgAdmin检视表结构并尽可能地获取信息。

数据类型是一个编程概念，它并不仅仅适用于SQL。我们在本章探索的概念同样可以应用到很多你想要学习的其他语言上面。

在SQL数据库中，表中的每个列仅能容纳一种数据类型，我们可以在CREATE TABLE语句中定义这一点，只需要在列名的后面声明数据类型即可。在下面的简单示例表中，你会看到三个列，它们分别拥有三种不同的数据类型：包括日期，数字还有文本。（你只需要观察这个表就可以，不必创建它。）

CREATE TABLE eagle_watch (
    observation_date date,
    eagles_seen integer,
    notes text
);

在这个名为eagle_watch的虚构的老鹰清单中，我们通过在observation_date列的名字后面添加数据类型来声明该列容纳日期值。同样地，我们通过integer类型声明设置eagles_seen容纳整数，并通过text类型声明notes容纳字符。

这些数据类型属于我们最经常会遇到的三种：

字符 任何字符或者符号

数字 包括整数和分数

日期和时间 时间相关的信息

在接下来的内容中，我们将深入探讨每种数据类型；并说明它们是ANSI SQL标准的一部分还是PostgreSQL特有的。如果你想要全面且深入地了解PostgreSQL和SQL标准的区别，那么请参考： https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard。

了解字符

字符串类型是一种通用类型，适用于由文本、数字和符号组成的任意组合。该类型包含以下几种：

char(n) 固定长度列，其中字符的长度由n指定。如果一个列被设置成char(20)，那么无论实际插入的字符有多少个，每个行的这一列都会储存20个字符。如果某个列插入的字符少于20个，那么PostgreSQL将用空格填充该列的剩余部分。这种类型是标准SQL的一部分，它还可以通过较长的名字character(n)来指定。时至今日，char(n)已经很少使用，它主要是作为老旧计算机系统的遗留物存在。

varchar(n) 可变长度列，其中最大长度由n指定。即使实际插入的字符数量比最大长度要小，PostgreSQL也不会储存额外的空格。比如说，储存字符串blue只会占用四个空格，而储存字符串123只会占用三个。在大型数据库中，这种做法可以节省大量空间。这种类型包含在标准SQL当中，并且它还能够通过较长的名字varying(n)来指定。

text 没有长度限制的可变长度列。（根据PostgreSQL文档描述，用户能够储存的最长字符串大约为 1 GB）。text类型并不是标准SQL的一部分，但包括Microsoft SQL和MySQL在内的其他数据库系统里面都有类似的实现。

根据PostgreSQL文档的记载，这三种类型在性能上并无实质区别：https://www.postgresql.org/docs/currentsubstantial/datatype-character.html。不过如果你使用的是其他数据库管理器，那么情况可能会有所不同，所以最好还是通过检查文档确认一下。varchar和text不仅更灵活，而且还不会浪费空间储存不必要的字符，这让它们看上去似乎占尽优势。但如果你看过网上的一些讨论，就会知道有些用户建议使用char定义那些总是具有相同字符数量的列，以此来更好地表明列所容纳的数据。比如说，你可能会看到char(2)被用于储存美国各州的邮政缩写。

注意

因为储存在char列中的数字是无法执行数学运算的，所以请仅在数字表示代号的情况下才将它们储存为字符类型（比如在表示美国邮政编码的时候）。

为了实际地观察这三种字符类型，我们可以运行代码清单4-1中展示的脚本。这个脚本会构建并载入一个简单的表，然后在你的计算机里面将数据导出至一个文本文件。

​x
CREATE TABLE char_data_types (
  ➊char_column char(10),
    varchar_column varchar(10),
    text_column text
);

➋ INSERT INTO char_data_types
   VALUES
       ('abc', 'abc', 'abc'),
       ('defghi', 'defghi', 'defghi');
       
➌ COPY char_data_types TO 'C:\YourDirectory\typetest.txt'
➍ WITH (FORMAT CSV, HEADER, DELIMITER '|');

代码清单 4-1 ：实际使用字符数据类型

这段代码首先定义了三个不同类型的字符列 ➊ ，然后插入两个行并在行的各个列中分别设置相同的字符串 ➋ 。跟第2章中展示的INSERT INTO语句不同，这段代码并没有指定各个列的名字。这是因为如果VALUES语句提供的值数量跟表的列数量保持一致，那么数据库就会认为你在按照定义表时指定列的顺序在插入值。

之后，代码使用PostgreSQL的COPY关键字 ➌ ，将数据导出至指定文件夹的typetest.txt文本文件中。在执行这段代码的时候，你需要将C:\YourDirectory\替换为你想要在计算机里面储存文件的完整目录路径。例子中使用的是Windows格式的目录路径，它是一个指向C:驱动器中的YourDirectory文件夹的路径，并且在文件夹和文件名之间使用了反斜线符号。 除此之外，Windows用户还必需根据第1章“从GitHub中下载代码和数据”一节介绍的方法，为目标文件夹设置权限。

Linux和macOS的文件路径格式跟Windows并不相同，它们在文件夹和文件名之间使用正斜线符号。比如说，在我的Mac中，指向桌面的文件路径为/Users/anthony/Desktop/。此外，被指定的目录必需已经存在，PostgreSQL不会自动为你创建指定的文件夹。

注意

因为PostgreSQL以postgres用户身份运行，它无法读写其他用户的文件夹，所以在Linux上执行COPY命令可能会由于权限不足而引发错误。解决这个问题的其中一个办法就是读写系统的/tmp文件夹，该文件夹允许所有用户访问。不过需要小心的是，由于某些配置的原因，这个文件夹可能会在计算机重启的时候被清空。关于解决这个问题的其他可选方法，请参考第5章中“通过pgAdmin导入和导出数据”一节，还有第18章关于psql的“导入、导出和使用文件”一节。

在PostgreSQL中，COPY table_name FROM是导入函数，而COPY table_name TO则是导出函数。本书将在第5章深入介绍这两个函数，但是现在来说，我们只需要知道WITH关键字选项➍会格式化文件中的数据并使用管道符号（|）分隔每一列即可。这样一来，我们就可以轻而易举地看到char列中的未使用部分是如何被空格填充的。

为了查看代码的输出结果，请使用你在第1章安装的文本编辑器打开typetest.txt文件（切记不要使用Word、Excel或者其他电子表格应用程序打开文件）。文件中包含的内容应该是这样的：

xxxxxxxxxx
char_column|varchar_column|text_column
abc       |abc|abc
defghi    |defghi|defghi

尽管我们将char列和varchar列的长度都设置成了10个字符，但只有char列会使用空格填充未使用空间，并且在两个行都输出了10个字符。与此相反，varchar列和text列只储存了被插入的字符。

再次声明，尽管这个例子说明char类型可能会消耗比实际所需更多的储存空间，但这三种类型在性能方面并无实质差别。每一列中出现少量未使用空间初看上去似乎微不足道，但如果这种情况出现在数十张表的数百万个行中，你很快就会理解积小成多的道理。

我个人倾向于对所有字符列使用text类型。这种做法使得我不必为每个可变长度的字符列逐一设置最大长度，并且当这些字符列的需求发生变化时，我也不必对它们的表进行修改。

了解数字

顾名思义，数字列能够容纳各种类型的数字，但这并不是全部：它还允许你对数字进行计算。

以字符串形式储存在字符列的数字不能执行加法、乘法、除法还有其他一系列数学运算，这是非常重要的一点。此外，以字符形式储存的数字跟以数字形式储存的数字在排序时会产生不同结果，因此如果你需要进行数学运算或者数值排序，那么就需要用到数字类型。

SQL的数字类型可以分为以下两类：

整数 包括正数和负数在内的所有整数 定点和浮点 由整数构成的两种不同形式的分数

接下来的内容将分别介绍这些类型。

使用整数

整数数据类型是你在探索SQL数据库时最经常会见到的数字类型，它们是包括0、正数和负数在内的所有整数。从街道或公寓的编号，到冰箱上的序列号，再到抽奖券上的兑奖数字，整数在我们的日常生活中随处可见。

SQL标准提供了三种整数类型，它们分别是：smallint、integer还有bigint。这三种类型之间的区别在于它们能够容纳的数字的最大体积。表4-1展示了这三种数字类型的最大值和最小值，还有储存这些数字所需的存储空间（字节）。

表4-1：整数数据类型

bigint类型几乎能够满足你对数字列的任何要求，但是它占用的存储空间也是最大的。当你需要处理大于21亿的数字时，使用bigint就是必须的，但你也可以简单地把这种类型看作是你的无忧默认选择，这样你就永远不必担心列能否容纳某个数字了。另一方面，如果你确信数字的范围不会超过integer的限制，那么使用这种类型也是一个不错的选择，因为它不会像bigint那样消耗那么多存储空间（这在处理上百万个数据行时会是一个需要关心的问题）。

当你知道数值将持续受到约束的时候，使用smallint将是有意义的：比如月份的天数和年份的天数就是一个很好的例子。因为smallint类型占用的存储空间只有integer的一半，所以如果列的值总是能够维持在smallint的取值范围之内，那么使用smallint就是一种精明的数据库设计抉择。

如果你尝试向上述类型的列中插入一个超出范围的数字，那么数据库将停止执行操作并返回一个越界错误。

自动递增整数

在一些情况下，创建一个容纳整数的列，并且在这个表每次添加新行的时候，让这个列的值自动递增，这样会很有帮助。举个例子，我们可以通过自动递增列，为表中的每个行创建一个独一无二的ID号码，并将其用作行的主键（primary key）。这样一来，数据库中的其他表就可以根据这个ID来引用行，本书稍后将在第7章介绍这个概念。

PostgreSQL提供了两种方法对整数列实行自动递增。一种是使用序列数据类型，这是PostgreSQL对ANSI SQL标准中的自动编号标识符列的一种专有实现；而另一种则是使用ANSI SQL标准的IDENTITY关键字。接下来的内容将从序列类型开始介绍。

通过序列实现自动递增

前面的第2章在创建teachers表的时候，创建了一个声明为bigserial的id列：这个bigserial和它的兄弟smallserial以及serial与其说是真正的数据类型，不如说更像是bigint、smallint和integer对应的特殊实现。每当你插入一个新行并且行中带有序列类型的列时，PostgreSQL就会对这些列的值执行自增操作，从1开始，直到对应整数类型的最大值为止。

表4-2展示了不同的序列类型以及它们的覆盖范围。

表4-2：序列数据类型

为了创建序列类型的列，我们需要在CREATE TABLE语句中声明它们，就像声明integer类型时一样。举个例子，通过以下代码，我们可以在创建people表的时候，让它拥有一个名为id的序列类型列，并且该列所需的存储空间等同于integer数据类型：

xxxxxxxxxx
CREATE TABLE people (
    id serial,
    person_name varchar(100)
);

现在，people表每增加一个包含person_name的新行，id列的值都会被加上1。

通过IDENTITY实现自动递增

PostgreSQL从第10版开始支持IDENTITY，这是自动递增整数的标准SQL实现。IDENTITY的语法比较冗长，一些数据库用户之所以选择它，更多是考虑到它和诸如Oracle等其他数据库系统的交叉兼容，另一方面则是考虑到它可以防止用户意外地往自动递增列中插入数值，而序列类型并不拒绝这种操作。

1. GENERATED ALWAYS AS IDENTITY命令数据库总是使用自动递增的值来填充该列。除非用户手动覆盖该设置，否则他们将无法向id列插入任何值。详情请见PostgreSQL INSERT文档中的OVERRIDING SYSTEM VALUE一节：https://www.postgresql.org/docs/current/sql-insert.html 。

2. GENERATED BY DEFAULT AS IDENTITY命令数据库在用户没有主动提供值的情况下，默认使用自动递增值来填充该列。这个选项可能会导致重复值出现，并给那些被用作键的列带来问题。稍后的第7章会更深入地探讨这个方面。

目前来说，我们将继续使用第一个选项，也即是ALWAYS。为了创建一个名为people的表，并让它带有一个由IDENTITY填充的id列，我们需要用到以下代码：

xxxxxxxxxx
CREATE TABLE people (
    id integer GENERATED ALWAYS AS IDENTITY,
    person_name varchar(100)
);

注意这里id列的数据类型为integer，并且后面跟着关键字GENERATED ALWAYS AS IDENTITY。现在，每当我们将一个person_name值插入到表，数据库就会使用一个自动递增的值来填充id列。

考虑到IDENTITY对ANSI SQL标准的兼容性，本书将在接下来的内容中继续使用它。

注意

尽管自动递增列的值在每次添加新行时都会增加，但有些情况可能会导致列中的数字序列产生空隙。比如说，当一个行被删除之后，该行对应的自增值就永远消失了。又比如说，即使一次行插入被中止了，但自增列的序列仍然会被递增。

使用小数

小数由整数以及整数的分数组成，其中分数由小数点后的数字表示。SQL数据库使用定点和浮点数据类型来处理小数。比如说，从我家到最近的杂货店的距离为6.7英里；我可以把6.7插入到定点或者浮点列中，而PostgreSQL对此不会有任何怨言。定点和浮点这两种数据类型的唯一区别在于计算机储存它们的方式，稍后我们就会知道这其中的重要意义。

了解定点数

定点类型，也称任意精度类型，在PostgreSQL中表示为numeric(precision, scale)。其中参数precision（精度）用于指定小数点左右两边的最大数字个数，而scale（刻度）则用于指定小数点右边允许的数字个数。此外，你还可以使用decimal(precision, scale)表示定点类型，它和numeric都是ANSI SQL标准的一部分。如果用户没有指定刻度，那么刻度将被设置为0，这实际上相当于创建一个整数。如果用户既没有指定精度，也没有指定刻度，那么数据库将在允许的最大范围内储存任意精度和刻度的值。（根据PostgreSQL的文档记录，这个最大值允许在小数点之前拥有最多131072个数字，并在小数点之后拥有最多16383个数字：https://www.postgresql.org/docs/current/datatype-numeric.html 。）

举个例子，假设你正在收集几个当地机场的降雨总量——这是一个极有可能出现的数据分析任务。美国国家气象局有提供相关的数据，其中降雨量的测量会精确至小数点后两位数。（正如你的小学数学老师所说，小数点后面的两位数就是百分位。）

为此，我们将使用numeric(5, 2) 在数据库中记录降雨量，其中精度总共为五个数字，而刻度则为小数点之后最多两个数字。此外，即使你并未像1.47、1.00和121.50那样，输入一个包含两位小数的数字，数据库也总是会返回小数点右边的两位数字。

了解浮点数类型

浮点类型分为real和double precision两种，它们都是SQL标准的一部分，两者的区别在于能够储存的数据量不同：real类型允许精度达到6位小数，而double precision的精度则能够达到15位小数，并且这两种类型在小数点两边能够容纳的位数是相同的。这些浮点类型也被称为可变精度类型。数据库会将浮点类型的数字分成多个部分储存，其中包括表示数字的部分以及表示指数的部分，后者决定了小数点所在的位置。因此，跟固定精度和刻度的numeric不一样，浮点数列的小数点可以根据数字的不同而“浮动”。

使用定点和浮点类型

如表4-3所示，每种类型能够容纳的总位数和精度都有所不同。

表4-3：定点和浮点数据类型

为了观察三种不同的数据类型如何处理相同数字，我们可以创建一个小型的表并向其插入各种测试用例，正如代码清单4-2所示。

xxxxxxxxxx
CREATE TABLE number_data_types (
  ➊ numeric_column numeric(20,5),
    real_column real,
    double_column double precision
);

➋ INSERT INTO number_data_types
   VALUES
       (.7, .7, .7),
       (2.13579, 2.13579, 2.13579),
       (2.1357987654, 2.1357987654, 2.1357987654);
      
SELECT * FROM number_data_types;

代码清单4-2：实际操作数字数据类型

这段代码创建了一个表，并在其中为每种分数数据类型都创建了一个列 ➊ ，然后再将三个行导入至表中 ➋ 。每个行都会重复地在三个列里面储存相同的数字。最后，代码的末尾一行会从表中选取所有内容，并得到以下结果：

xxxxxxxxxx
numeric_column real_column double_column
-------------- ----------- -------------
       0.70000         0.7           0.7
       2.13579     2.13579       2.13579
       2.13580   2.1357987  2.1357987654

注意这段结果中出现的细微区别。在numeric列，因为刻度被设置为5，所以无论我们输入的小数有多少位，它都总是会储存五位小数。如果小数部分小于五位，那么它就用0填充余下的位数。如果小数部分多于五位，那么它就对小数实行四舍五入——比如表第3行的数字原本就有10位小数。

跟numeric的做法相反，real和double precision列不会给数字添加任何填充。在第三行，我们会看到PostgreSQL在输出这两种类型时的默认行为：不展示整个值，而是以最简短的精确小数表示来输出浮点数。另外需要注意的是，较旧版本的PostgreSQL可能会显示稍微不一样的结果。

浮点数计算带来的问题

如果你认为储存浮点数跟储存定点数没什么两样，那么你要小心了。计算机储存浮点数的方式可能会导致意想不到的数学错误。为了展示这一点，我们需要执行一些浮点数计算。请执行代码清单4-3中的脚本。

xxxxxxxxxx
  SELECT
    ➊ numeric_column * 10000000 AS fixed,
       real_column * 10000000 AS floating
  FROM number_data_types
➋ WHERE numeric_column = .7;

代码清单4-3：浮点列的舍入问题

这段代码将numeric_column列和real_column列的所有值都乘以一千万 ➊ ，然后使用WHERE子句筛选出结果集的第一行 ➋ 。初看上去，两项计算应该会得到相同的结果，是吧？但查询实际返回的结果却是这样子的：

xxxxxxxxxx
fixed           floating
-------------   ----------------
7000000.00000   6999999.88079071

你看，这就是浮点数类型被认为“不精确”的原因，也是它不适合完成火星发射任务或者计算联邦预算赤字的原因。

浮点数之所以会产生这样的错误，跟计算机试图将大量信息塞进有限的比特位中有关。这个话题是很多著作的主题，但它超出了本书的范围，如果你有兴趣的话，可以在本书资源网站中找到一个很好的概要链接：https://www.nostarch.com/practical-sql-2nd-edition/。

numeric数据类型所需的存储空间是可变的，根据精度和刻度不同，它可能会比浮点类型消耗更多的空间。如果你需要处理的行非常多，那么可以考虑是否能够接受相对来说不太精确的浮点数计算。

选择合适的数字类型

目前来说，在处理数字数据类型的时候，有三条准则可供参考：

• 尽可能地使用整数。除非数据中包含小数，否则就应该坚持使用整数类型。

• 如果你正在处理小数数据，并且处于金钱交易等需要精确计算的场合，那么请选择numeric或者它的等价物decimal。浮点类型虽然能够节约空间，但它在计算时的不精确性对很多应用来说是无法接受的，请在精确性不那么重要的情况下使用它。

• 选择足够大的数字类型。除非你设计的数据库需要包含数百万个行，否则就应该尽可能地选择较大的数字类型。在使用numeric或者decimal的时候，将精度设置得足够大以便容纳小数点两边的所有数字。对于整数，除非你百分之百确定列的值可以容纳在较小的integer类型或者smallint类型中，否则就应该使用bigint类型。

了解日期和时间

每当我们在搜索框中键入日期的时候，数据库对（从服务器接收到的）当前时间的感知，还有对日历的日期、时间、闰年和时区等细节的格式处理能力，都让我们获益匪浅。这一点对于数据叙事来说至关重要，因为事件发生的时间往往和人物、内容还有参与人数一样重要。

表4-4展示了PostgreSQL对日期和时间提供支持的四种主要数据类型。

表4-4：日期和时间数据类型

以下是PostgreSQL中这些时间和日期数据类型的简要描述：

timestamp 记录日期和时间，非常适用于各种追踪任务：客运航班的出发和到达，美国职业棒球大联盟的时间表，又或者给一系列事件加上时间轴。我们通常需要添加关键字 with time zone 以确保事件记录的时间包含了相应的时区，否则全球不同地方记录的时间将无法进行对比。timestamp with time zone 格式是SQL标准的一部分，我们可以在PostgreSQL中通过timestamptz指定这一数据类型。

date 只记录日期。它是SQL标准的一部分。

time 只记录时间，也是SQL标准的一部分。尽管这种类型也允许我们添加with time zone关键字，但缺少日期的时区实际上并没有多大意义。

intelval 这种值以数量单位格式表示时间单位。它不记录时间段的开始或结束，而是记录其长度，例如：12天或者8个小时。（PostgreSQL的文档列出了范围从microsecond到millennium在内的单位值：https://www.postgresql.org/docs/current/datatype-datetime.html。）这种类型通常用于计算或是过滤其他的日期和时间列。intelval也是SQL标准的一部分，不过PostgreSQL通过特定语法为它提供了更多的选择。

让我们先把注意力放到timestamp with time zone和interval这两种类型上面。为了了解它们的实际效果，我们需要运行代码清单4-4。

xxxxxxxxxx
➊ CREATE TABLE date_time_types (
       timestamp_column timestamp with time zone,
       interval_column interval
   );
   
➋ INSERT INTO date_time_types
   VALUES
       ('2022-12-31 01:00 EST','2 days'),
       ('2022-12-31 01:00 -8','1 month'),
       ('2022-12-31 01:00 Australia/Melbourne','1 century'),
     ➌ (now(),'1 week');
   SELECT * FROM date_time_types;

代码清单4-4：实际使用timestamp和interval类型

这段代码首先在表中为两种不同的类型分别创建了列➊，然后向表中插入四个行➋。其中前三个行向timestamp_column列插入了相同的日期和时间（2022年12月31日的凌晨1点），使用的是国际标准化组织（ISO）的日期和时间格式：YYYY-MM-DD HH:MM:SS 。SQL也支持其他日期格式，比如MM/DD/YYYY，但ISO格式能够在世界各地提供更好的可移植性。

在插入的前三个行里面，我们除了时间之外，还分别用三种不同的格式指定了时区：第一行使用的是缩写EST，也即是美国的东部标准时间。

在第二个行，代码使用了数值-8来设置时区，它代表该时区与世界标准时间——协调世界时（UTC）的时差或偏移量。UTC的值是+/-00:00，所以-8指定的时区比UTC晚8小时。在美国，当夏令时生效时，-8是阿拉斯加时区的数值。从11月到3月初，当美国恢复到标准时间时，-8指代的是太平洋时区。(关于UTC时区的地图，请见：https://en.wikipedia.org/wiki/Coordinated_Universal_Time#/media/File:Standard_World_Time_Zones.tif)。

第三行代码使用了一个地区和位置的名称来指定时区：Australia/Melbourne（澳大利亚/墨尔本）。这种格式使用了计算机编程中经常采用的标准时区数据库中的数值。你可以在以下链接中获取到更多关于时区数据库的信息：https://en.wikipedia.org/wiki/Tz_database。

在代码的第4行，脚本没有指定日期、时间和时区，而是使用了PostgreSQL的now()函数➌，从硬件中获取当前的事务处理时间（transaction time）。

运行这个脚本之后，它将产生类似以下这样的输出（不过跟这里不会完全相同）：

xxxxxxxxxx
timestamp_column               interval_column
-----------------------------   ---------------
2022-12-31 01:00:00-05          2 days
2022-12-31 04:00:00-05          1 mon
2022-12-30 09:00:00-05          100 years
2020-05-31 21:31:15.716063-05   7 days

尽管代码在前三个行向timestamp_column提供了相同的日期和时间，但每个行输出的结果并不相同。原因在于pgAdmin会根据我所在的时区报告相对的日期和时间，所以在显示的结果中，每个时间戳后面都带有一个UTC偏移量-05。这个偏移量意味着当前时区的时间比UTC时间落后5个小时，相当于美国东部时区在秋季和冬季的标准时间。如果你生活在别的时区，那么你可能会看到不同的偏移量，并且结果显示的时间和日期也会和这里不一样。在之后的第12章，我们将会学习如何改变PostgreSQL报告这些时间戳的方式，并学习更多处理日期和时间的技巧。

最后，PostgreSQL在interval_column列展示了我们输入的间隔值，并且基于展示间隔值的默认设置，把原本的1世纪修改成了100 年、1周修改成了7天。你可以通过阅读PostgreSQL文档中关于“间隔输入”的部分来了解更多和间隔值有关的选项：https://www.postgresql.org/docs/current/datatype-datetime.html。

在计算中使用interval数据类型

interval数据类型非常有用，它能够让日期和时间数据的计算变得简单易懂。举个例子，如果你有一个列，它记录了客户签订合同的日期，那么通过interval数据，你可以给每个签订合同的日期加上90天的期限，从而确定应该在何时跟客户续约。

为了观察interval数据类型是如何工作的，我们需要用到前面创建的date_time_types表，正如代码清单4-5所示。

xxxxxxxxxx
SELECT
    timestamp_column,
    interval_column,
 ➊ timestamp_column - interval_column AS new_date
FROM date_time_types;

代码清单4-5：使用interval数据类型

这是一个典型的SELECT语句，我们唯一要做的就是计算一个名为new_date ➊的列，并在其中包含timestamp_column减去interval_column的结果。（这种使用列进行计算的技术被称为表达式，我们将会经常用到。）对于表中的每个行，代码都会使用日期减去interval数据类型所表示的时间单位，并最终产生以下结果：

xxxxxxxxxx
timestamp_column                interval_column     new_date
-----------------------------   ---------------     -----------------------------
2022-12-31 01:00:00-05          2 days              2022-12-29 01:00:00-05
2022-12-31 04:00:00-05          1 mon               2022-11-30 04:00:00-05
2022-12-30 09:00:00-05          100 years           1922-12-30 09:00:00-05
2020-05-31 21:31:15.716063-05    7 days              2020-05-24 21:31:15.716063-05

注意，new_date列默认会被格式化为timestamp with time zone类型，这样间隔值在有需要的时候就可以把它们展示为时间值或者日期了。（pgAdmin的结果网格会在列名的下面列出相应的数据类型。）再次提醒，根据你所处的时区，你看到的输出可能会跟这里展示的不一样。

了解JSON和JSONB

JavaScript Object Notation（简称JSON）是一种结构化的数据格式，用于储存数据或者在计算机系统之间进行数据交换。所有主要的编程语言都支持读写JSON格式的数据，这种数据通常会把信息组织成一系列键值对以及值列表。下面是一个简单的例子：

xxxxxxxxxx
{
  "business_name": "Old Ebbitt Grill",
  "business_type": "Restaurant",
  "employees": 300,
  "address": {
    "street": "675 15th St NW",
    "city": "Washington",
    "state": "DC",
    "zip_code": "20005"
  }
}

这个JSON片段展示了这种格式的基本结构。一个键和一个值相关联，就像这个例子中的business_name和Old Ebbitt Grill那样。此外，一个键的值还可以由其他多个键值对组成，就像address展示的那样。JSON标准制定了相应的格式化规则，比如使用冒号分隔键和值，还有使用双引号包围键名等等。你可以使用诸如https://jsonlint.com/这样的在线工具检查JSON对象的格式化是否合法。

PostgreSQL当前为JSON提供了两种数据类型，它们都能保证JSON的合法性，并且都提供了以JSON格式处理数据的函数：

• json 储存JSON文本的精确拷贝

• jsonb 以二进制格式储存JSON文本

这两种类型之间也存在一些显著的差异。比如说，jsonb支持索引，它可以有效地提升处理速度。

JSON是在2016年进入SQL标准的，但早在这之前的好几年，PostgreSQL还是9.2版本的时候就开始支持JSON了。PostgreSQL目前实现了几个SQL标准中的函数，除此之外还提供了一些自己特有的JSON函数和操作符。稍后的第16章将对这两种类型以及它们相应的函数和操作符作更深入的介绍。

使用各式各样的类型

字符、数字和日期/时间类型通常已经能够满足大部分工作的要求。但PostgreSQL还提供了很多其他类型，包括但不限于以下这些：

• 储存true或false的布尔类型

• 包含点、线、圆和其他二维对象的几何类型

• 为PostgreSQL全文搜索引擎而设的文本搜索类型

• 网络地址类型，比如 IP 或者 MAC 地址

• 通用唯一标识符（UUID）类型，有时会被用作表中的唯一键值

• 范围类型，它能够让你指定值的范围，比如整数或者时间戳

• 储存二进制数据的类型

• 以结构化格式储存信息的XML数据类型

本书后续将在适当的时候介绍这些类型。

使用CAST将值从一种类型转换为另一种类型

在一些情况下，你可能会想要把一个值从它的存储数据类型转换为另一种类型。举个例子，你可能会想要以字符形式获取数字，以便把它和文本结合起来。又或者，你可能会想要把以字符形式储存的日期转换为真正的日期类型，以便按日期顺序对其进行排序，又或者对其执行间隔值计算。这样的转换可以通过CAST()函数来完成。

CAST()函数只有在目标数据类型能够容纳原始值的时候才会成功。将数字转换为文本是可行的，因为字符类型本身就能够包含数字，但是想要将包含字母的文本转换成数字却是不可能的。

通过使用前面创建的包含三种数据类型的表，代码清单4-6列举了三个例子。前两个例子可以正常运行，但第三个例子会尝试执行无效的类型转换，以此来展示类型转换出现错误时的样子。

xxxxxxxxxx
➊ SELECT timestamp_column, CAST(timestamp_column AS varchar(10))
  FROM date_time_types;
  
➋ SELECT numeric_column,
         CAST(numeric_column AS integer),
         CAST(numeric_column AS text)
  FROM number_data_types;

➌ SELECT CAST(char_column AS integer) FROM char_data_types;

代码清单4-6：三个CAST()使用案例

第一个SELECT语句➊会以varchar也即是可变长度字符列的形式返回timestamp_column的值。在这个例子中，代码将字符的长度设置成了10，这意味着在转换字符串的时候，只有前10个字符会被保留。这种做法对我们这个例子来说是挺方便的，因为这样一来就只有列的日期部分会被保留，而时间部分则会被排除。当然，还有更好的做法可以从时间戳里面移除时间部分，本书将在第12章的“从时间戳值中提取组成部分”一节中对此进行介绍。

第二个SELECT语句➋会返回numeric_column三次：首先是它的原始形式，然后是它的整数形式，最后是它的文本形式。在进行整数转换的时候，PostgreSQL会将值四舍五入为整数。但是在进行文本转换的时候则不会发生四舍五入操作。

最后的SELECT语句➌无法正常工作：因为文字无法转换成整数，所以它将返回一个invalid input syntax for type integer错误。

使用CAST速记法

在编写SQL的时候，让后来者能够读懂你的代码是非常重要的，而CAST()的写法能够将你的使用意图表露无遗。但除此之外，PostgreSQL还提供了一种稍微没那么明显但是却能够节省更多空间的双冒号速记法。

这种速记法需要在列名和想要转换的数据类型之间插入两个冒号。比如说，以下两个语句都可以将timestamp_column转换为varchar：

xxxxxxxxxx
SELECT timestamp_column, CAST(timestamp_column AS varchar(10))
FROM date_time_types;

SELECT timestamp_column::varchar(10)
FROM date_time_types;

你可以选择任意一种你想要的转换方式，但需要注意的是，双冒号速记法是PostgreSQL特有的实现，它不存在于其他SQL变种，所以这种写法是无法移植的。

小结

在阅读完这一章之后，你在挖掘数据的时候就能够更好地理解数据格式之间的细微区别。你知道在遇到以浮点数形式储存的金融数值时，应该在执行任何计算之前把它们转换为小数，也学会了如何使用正确的文本列从而避免浪费数据库空间。

接下来的一章将继续介绍SQL的基础知识，并向你展示如何将外部数据导入至数据库。

实战演练

请通过完成以下练习继续探索数据类型。

1. 假设某家公司负责向当地的杂货铺运送水果和蔬菜，而你需要跟踪每个司机每天行驶的里程数，精确到十分之一英里。如果司机在一天内的行驶里程不会超过999英里，那么你应该在表中使用什么数据类型来表示里程列呢，这样选择的原因是什么？

2. 在记录公司司机的表中，记录司机的名字和姓氏应该使用什么数据类型？把名字和姓氏分成两列而不是合并成一个更大的名字列，这样做的好处是什么？

3. 假设你拥有一个文本列，里面包含了格式化为日期的字符串。其中一个字符串为'4//2021'。如果你试图把这个字符串转换为timestamp数据类型，会发生什么事情？