PostgreSQL11快速入门(五)-PostgreSQL数据类型

PostgreSQL

PostgreSQL11快速入门(五)-PostgreSQL数据类型

在学习基本的 PG 用法前，需要先了解下 PG 支持的一些数据类型。

PG支持非常丰富的数据类型 , 几乎可以覆盖任何应用场景。需要对基本的数据类型有个了解，方便后续的使用。

注意： 下面中的别名大多数备选名称都是 PG 由于历史原因保留而使用的名称，通常更不建议使用。

1. 数值类型

1.1 整数类型

integer：有符号 4 字节整数 , 取值范围为 -2147483648 到 +2147483647。别名 int、 int4。
bigint：有符号 8 字节整数 , 取值范围为 -9223372036854775808 到 +9223372036854775807。别名 int8。
smallint：有符号的 2 字节整数。也称为小整数类型 , 取值范围为 -32768 到 +32767。别名 int2。

运用场景：

integer 最大支持的整数为正整数 20亿，如果预估数值不会超过 20亿，就可以直接选这个类型。但对于某些大数据量的场景,还是需要用 bigint 来防止溢出。
bigint 支持的整数特别大，这种类型通常适合日志表的 ID 等，一般的业务场景比较难到得了这个量级。
smallint 最大支持 32767 ，适合用来做一些业务类型枚举的定义，比较节省磁盘空间。

1.2 定点数类型

decimal：可选择精度的数值类型 , 可以指定精度和刻度。
numeric(p, s)：与 decimal 相同 , 别名 decimal 。

运用场景：

优势就是用于存储精度要求较高的定点数，可以指定任意精度。缺点是如果精度太高的话，占的磁盘空间高。
如果需要精确存储货币金额等定点数 , numeric 是唯一的选择。它可以指定任意精度,不会有浮点数的舍入误差。

1.3 浮点数类型

real：单精度 4字节浮点数类型。别名 float4。
double precision：双精度浮点数类型。8字节。别名 float8。

运用场景：

real 精度是6位十进制数字，适合小数点位数少的场景。
double 精度是15位十进制数字，适合小数点位数较多场景。
另一个方面，在选择浮点数类型时 , 要权衡精度和性能。一般情况下 , real 的精度已经够用 , 而且性能更好。但对于科学计算等精度要求极高的场合 , 就需要用 double 类型。

1.4 序列类型

bigserial：自动递增 8 字节整数类型。实际上创建的就是 bigint 类型。
smallserial：自动递增 2 字节整数。实际上创建的就是 smallint 类型。
serial：自动递增 4 字节整数。实际上创建的就是 integer 类型。

所谓的序列类型，其实就是创建一个自增的整数类型字段，只不过是自动帮你创建好一个序列 Sequence。这样每次插入数据的时候，这个字段不需要指定值，默认每次 +1 。

运用场景：

需要主键是个数值类型，并且要自增的就直接使用 serial 类型。
根据主键 ID 的范围，选择不同的 serial 类型。

2. 字符类型

char：定长字符串类型,长度固定,最大长度为 1GB。
varchar：变长字符串类型,长度可变,最大长度为 1GB。
text：变长字符串类型,无最大长度限制。

运用场景：

varchar：用于存储电表编号、用户名等可变长度的字符串。需要指定最大长度。
text：用于存储备注、日志等不定长的大字符串。不需要指定长度。

差异点：

相比于定长的 char 类型, varchar 和 text 在存储变长字符串时更加灵活和节省空间。
varchar 需要指定最大长度,适合存储有长度限制的字符串
text 不需要指定长度,适合存储任意长度的字符串。

3. 日期/时间类型

date：日期类型,格式为 YYYY-MM-DD。
time：时间类型,格式为 HH：MM：SS。
timestamp：时间戳类型,包含日期和时间,格式为 YYYY-MM-DD HH：MM：SS。
interval：时间间隔类型,表示一段时间。

运用场景：

date 用于存储采集日期等不含时间的日期。格式是’YYYY-MM-DD’。
timestamp 用于存储采集时间等含有日期和时间的时间戳。格式是’YYYY-MM-DD HH:MI:SS’。

差异点：

date 只包含日期信息 , 而 timestamp 包含完整的日期和时间信息。

4. 布尔类型

boolean：布尔类型,值为 true 或 false。它只有两个值: true 和 false。布尔类型只占一个字节 , 非常紧凑。

运用场景：

通常用于存储设备状态、告警标志等二值信息。

5. 数组类型

PG 支持任意类型的数组 , 数组元素可以是任意类型 , 包括数组类型。例如：

CREATE TABLE table_name (
  column1 integer[],
  column2 text[][]
);

运用场景：

举例如采集系统，一次采集的多个值 (如电压、电流等) 就可以作为一个数组存储在 values 字段中,避免了行的膨胀。
数组天然适合批量计算,在某些场景下还可以大幅提升性能。

6. JSON 类型

PG 支持原生的 JSON 数据类型,可以直接在数据库中存储和查询 JSON 数据。

json：文本格式的 JSON 数据类型。对格式不做校验 , 检索时仍然是文本格式。
jsonb：二进制格式的 JSON 数据类型 , 支持索引。插入时要进行格式校验,以二进制格式存储,检索时自动解析。

运用场景：

使用 JSON 类型 , 可以将非结构化和半结构化的数据 (如设备参数、告警信息等) 直接存储在 PG 中,非常灵活。
PG 提供了一系列函数和操作符 , 可以方便地对 JSON 数据进行增删改查，可以实现类似 MongoDB 的功能。

7. 其他类型

PG 还支持许多其他数据类型,如 UUID、XML、网络地址类型、几何类型等 , 可以根据具体的应用需求选择合适的数据类型。在实际的项目运用中，这些类型尽管很实用，但是实际上企业项目中一般不太会采用，会优先选择简单的类型解决。

在使用PG开发应用时 , 也需要认真分析业务数据的特点 , 为每个字段选择最恰当的数据类型。盲目的一张表全部使用 varchar 类型是不可取的，同时也要注意数值范围、精度、长度等的合理设置 , 避免空间浪费和溢出。

8. 参考链接

PG 官方文档 - 数据类型： https://www.postgresql.org/docs/11/datatype.html