PostgreSQL常用数据类型及实践

Report Content

Issue: *
Copyright Infringment Spam Invalid Contents Broken Links

Your Name: *

Your Email: *

Details: *

Submit Report

前言

数据类型是编程语言中，在其数据结构上定义的相同值类型的集合以及对该相同值集合的一组操作。而数据类型的值存储离不开变量，因此变量的一个作用就是使用它来存储相同值集的数据类型。数据类型决定了如何将代表这些值的集合存储在计算机的内存中。变量一般遵循先声明后使用的原则。而在数据库中，变量就是字段，用字段来表示一组相同值类型的集合，其实也是先声明后使用的原则。

PostgreSQL支持丰富的数据类型，包括一般的数据类型和非常规的数据类型。一般数据类型包括数值型，货币类型，字符类型，日期类型，布尔类型，枚举类型等，非常规数据类型包括二进制数据类型，几何类型，网络地址类型，位串类型，文本搜索类型，UUID类型,XML类型，JSON类型，数组类型，复合类型，范围类型，Domain类型，OID类型，pg_lsn类型和pseudo-Types类型。

一、数值类型

1.1整型

PostgreSQL中的整型类型有小整型，整型，大整型，用 smallint,integer,和bigint表示，虽然三个都可以存储相同的数据类型，但是它们各自的存储大小和存储范围却不相同。见下表：

如下示例所示，在PostgreSQL中，smallint，integer，bigint 数据类型可以使用 int2,int4,int8的扩展写法来标识。

示例：

如上所示，查询的结果为插入不同整型范围的最值，也说明不同整型范围的边界都是被包括的。在实际生产场景中，SMALLINT、INTEGER和BIGINT类型存储各种范围的数字，也就是整数。当试图存储超出范围以外的数值将会导致错误。

常用的类型是INTEGER，因为它提供了在范围、存储空间、性能之间的最佳平衡。一般只有取值范围确定不超过SMALLINT的情况下，才会使用SMALLINT类型。而只有在INTEGER的范围不够的时候才使用BIGINT，因为前者相对要快。

除此之外，创建表也可以使用 int2,int4,int8来代表 smallint,integer,bigint。如下示例所示：

1.2任意精度类型和浮点类型

任意精度类型 numeric、decimal可以存储范围大的数字，存储大小为可变大小，小数点前最多131072位数字，小数点后最多16383位。它可以使用类似浮点类型，将小数精确到保留几位，也可以参与计算可以得到准确的值，但是相比于浮点类型，它的计算比较慢。通常 numeric被推荐使用于存储货币金额或其它要求计算准确的值。详细见下表：

示例：任意精度类型

1.3序列类型

SMALLSERIAL，SERIAL和BIGSERIAL类型不是真正的数据类型，只是为在表中设置唯一标识做的概念上的便利。因此，创建一个整数字段，并且把它的缺省数值安排为从一个序列发生器读取。应用了一个NOT NULL约束以确保NULL不会被插入。在大多数情况下用户可能还希望附加一个UNIQUE或PRIMARY KEY约束避免意外地插入重复的数值，但这个不是自动的。最后，将序列发生器从属于那个字段，这样当该字段或表被删除的时候也一并删除它。

示例：

通过上述示例，可以知道 smallserial,serial,bigserial相当于先创建一个序列，然后在创建表分别指定不同的整型数据类型smallint,integer,bigint。如下示例：

二、货币数据类型

货币类型存储带有固定小数精度的货币金额。

关于货币数据类型的详细信息如下表：

示例：

这里需要注意的是，如果插入的货币数据类型的金额没有明确指定货币表示符号，那么默认输出本区域货币符号，如上示例所示的20.00输出为$20.00。

如果是人民币，那么如何处理呢？

解决方法有两种，第一种，使用translate函数；第二种，修改本地区域货币符号显示参数。

货币符号作为特殊的数据类型，需要注意计算方式，以防止发生精度丢失的问题。

这种问题解决方式需要将货币类型转换为 numeric 类型以避免精度丢失。

温馨提示：

当一个money类型的值除以另一个money类型的值时，结果是double precision（也就是，一个纯数字，而不是money类型）；在运算过程中货币单位相互抵消。

三、布尔类型

PostgreSQL提供标准的boolean值，boolean的状态为 true或者false和unknown，如果是unknown状态表示boolean值为null。

示例：

boolean类型被广泛地使用在业务环境中，例如手机开关机，1表示开机，0表示关机或不在服务区。手机APP登录登出，1表示登录，0表示登出，微信登陆状态，1表示登录成功，0表示登录失败（可能由于网络或者密码错误导致）等等，此处不再一一举例。

四、字符类型

SQL定义了两种主要的字符类型:character varying(n) 和 character(n)。该处的n是一个正数。这两种字符类型都可以存储n（非字节）个长度的字符串。如果存储的字符长度超过了字符类型约束的长度会引起错误，除非多出的字符是空格。

注意，除了每列的大小限制以外，每个元组的总大小也不可超过1G-8023B（即1073733621B）。

在PostgreSQL中，除了以上的字符数据类型外，还有两种特殊的字符类型如下：

示例：

温馨提示：
在上面示例中，虽然统计的col1的定长为15的字符存储的字符长度为10个和11个，但是实际上，在存储中col1列占用的长度为15个。并且，在计算长度的时候，空格也被当作一个字符来对待。

五、二进制数据类型

在PostgreSQL中，二进制数据类型有两种，一种为 bytea hex格式，一种为 bytea escape格式。

注意：除了每列的大小限制以外，每个元组的总大小也不可超过1G-8203字节。

示例：

注意：

实际上bytea多个十六进制值使用E’\xFC’ 类似于Oracle中的rawtohex函数。只不过Oracle中的rawtohex函数转换后的值为大写十六进制字符串。实际上如果要在上表中的col2中插入E’\xFG’时，会提示G不是一个有效的十六进制字符。

同时需要注意的是，如果使用E’\xF’只包含单个十六进制字符时，使用一个反斜杠，如果有多个十六进制字符，需要两个反斜杠，如E’\xFE’。

如下:此处的hextoraw函数为我自定义实现的一个UDF函数。

六、日期时间数据类型

PostgreSQL支持丰富的日期时间数据类型如下表：

6.1日期输入

日期和时间的输入可以是任何合理的格式，包括ISO-8601格式、SQL-兼容格式、传统POSTGRES格式或者其它的形式。系统支持按照日、月、年的顺序自定义日期输入。如果把DateStyle参数设置为MDY就按照“月-日-年”解析，设置为DMY就按照“日-月-年”解析，设置为YMD就按照“年-月-日”解析。

日期的文本输入需要加单引号包围，语法如下：
type [ ( p ) ] ‘value’

可选的精度声明中的p是一个整数，表示在秒域中小数部分的位数。

示例：

6.2时间输入

时间类型包括

time [ § ] without time zone 和time [ § ] with time zone。

如果只写time等效于time without time zone。即不带时区的时间格式

如果在time without time zone类型的输入中声明了时区，则会忽略这个时区。

示例：

6.3特殊时间类型

特殊时间类型以reltime表示，表示真实的时间计算值，如100将会使用00:01:40来表示。

示例：

温馨提示：

对于 reltime 时间的输入，需要使用文本类型的输入，也就是说使用单引号引起来。

6.4其它时间类型

其它时间类型包含时间戳及间隔时间数据类型，示例如下：

示例：

七、网络地址类型

PostgreSQL也提供网络地址类型，以用于存储两大IP家族(IPv4 IPv6地址)地址和MAC地址的数据类型。

cidr（无类别域间路由，Classless Inter-Domain Routing）类型，保存一个IPv4或IPv6网络地址。声明网络格式为address/y，address表示IPv4或者IPv6地址，y表示子网掩码的二进制位数。如果省略y，则掩码部分使用已有类别的网络编号系统进行计算，但要求输入的数据已经包括了确定掩码所需的所有字节。

inet类型在一个数据区域内保存主机的IPv4或IPv6地址，以及一个可选子网。主机地址中网络地址的位数表示子网（“子网掩码”）。如果子网掩码是32并且地址是IPv4，则这个值不表示任何子网，只表示一台主机。在IPv6里，地址长度是128位，因此128位表示唯一的主机地址。

该类型的输入格式是address/y，address表示IPv4或者IPv6地址，y是子网掩码的二进制位数。如果省略/y，则子网掩码对IPv4是32，对IPv6是128，所以该值表示只有一台主机。如果该值表示只有一台主机，/y将不会显示。

inet和cidr类型之间的基本区别是inet接受子网掩码，而cidr不接受。

macaddr类型存储MAC地址，也就是以太网卡硬件地址（尽管MAC地址还用于其它用途）。

示例：

八、几何数据类型

PostgreSQL支持集合数据类型，用于存储GIS（地理信息系统）环境中的几何数据，用于地图测绘，城市交通轨迹，地图圈图等场景。

PostgreSQL支持以下几何数据类型：

点
线(射线)
线段
矩形
路径(包含开放路径【开放路径类似多边形】和闭合路径)
多边形
圆

对于以上几何类型而言，点是其它几何类型的基础。

对于所有的几何数据类型，都是使用二维坐标上面的横坐标和纵坐标来实现的。计算也是在二维坐标中进行的。

示例：

九、JSON数据类型

JSON数据类型可以用来存储JSON（JavaScript Object Notation）数据。数据可以存储为text，但是JSON数据类型更有利于检查每个存储的数值是可用的JSON值。

在 PostgreSQL中，JSON数据类型有两种，原生JSON和JSONB。最主要的区别就是效率不同。JSON 数据类型对于输入文本进行复制，因此在解析时需要进行转换，输入速度块。而JSONB是对输入文本进行分解并以二进制存储，因此在解析时不需要进行转换，处理速度块，但是输入速度相对会慢。除此之外，JSONB数据类型还支持索引。

示例：

温馨提示：

使用jsonb类型，可以使用PL/PYTHON映射为Python中表示的字典，列表等。

十、数组数据类型

PostgreSQL支持数组数据类型，同时支持多维数组。数组最大的优点就是按照数组下标访问，此时下标相当于一个索引，处理速度快。但是同时数组也有劣势，比如在删除或者添加数组元素需要对数组中的元素进行向前或者向后移动，这样导致删除或者添加数组元组时比较慢。

示例：

通过上述示例，可以发现，在PostgreSQL中，虽然声明了数组的长度，但是PostgreSQL对于数组的长度不会做任何限制。

同时访问数组元素从下标1开始，并且在PostgreSQL中并不会出现数组越界异常，如果数组的下标访问超过元素的长度，那么PostgreSQL便会返回一行空值。

以上就是常用数据类型介绍。但是在PostgreSQL中，除了上述数据类型外，还有其它的数据类型，比如XML数据类型，文本搜索数据类型，UUID数据类型，复合数据类型，范围类型，伪类型如any,anyelement,internal等等，在此不做一一介绍。

作者：宋少华，曾服务于国家电网冀北电力有限公司建设大数据平台，为人社局和北京市卫计委构建IT基础服务，为多家银行和证券公司构建web 服务器，系统及数据库维护；具有对税务局、国家电网、银行等政府行业和民营企业的IT培训经验；为相关安全行业设计DW数据仓库模型，使用PostgreSQL,Greenplum,HUAWEIGaussDB,Vertica和Clickhouse 做数据基础服务，开发TB级数据落地程序及百TB级别数据迁移程序。