PostgreSQL数据类型总结

PostgreSQL数据库相比其他数据库，支持更多的数据类型，包括常用的数值类型、字符串类型、日期/时间类型外，还有几何类型、网络地址类型、xml类型和json类型，且还可以使用CREATE TYPE自行添加数据类型，本文主要介绍PostgreSQL数据库主要的数据类型

1. 数值类型

常用数值类型如下表所示：

名称	别名	存储大小	范围	描述	与其他数据库对比
smallint	int2	2字节	-2²⁵~2¹⁵-1	有符号的2字节整数	MySQL中smallint，Oracle中number(5)
integer	int,int4	4字节	-2³¹~2³¹-1	有符号的4字节整数	MySQL中int，Oracle中number(38,0)
bigint	int8	8字节	-2⁶³~2⁶³-1	有符号的8字节整数	MySQL中bigint，Oracle中number(38)
real	float4	4字节		单精度浮点数	MySQL中float，Oracle中binary_float
double precision	float8	8字节		双精度浮点数	MySQL中double，Oracle中binary_double或number
numeric [(p, s)]	decimal [(p, s)]			可选择精度的精确数字	MySQL中decimal[(p, s)]，Oracle中NUMBER[(p, s)]
money		8字节	-92233720368547758.08~+92233720368547758.07	货币金额

2. 自增类型

常用自增类型如下表所示：

名称	别名	存储大小	描述	与其他数据库对比
smallserial	serial2	2字节	2字节自增整数	MySQL中自增字段，Oracle中序列
serial	serial4	4字节	4字节自增整数	MySQL中自增字段，Oracle中序列
bigserial	serial8	8字节	8字节自增整数	MySQL中自增字段，Oracle中序列

3. 布尔类型

名称	别名	存储大小	范围	描述	与其他数据库对比
boolean	bool	1字节		true/false	MySQL中boolean，Oracle中number(1)

4. 二进制类型

名称	别名	存储大小	范围	描述	与其他数据库对比
bytea				二进制数据	MySQL中blob，Oracle中blob

5. 位串类型

名称	别名	存储大小	范围	描述	与其他数据库对比
bit [ (n) ]				固定长度位串	MySQL中bit [ (n) ]
bit varying [ (n) ]	varbit[ (n) ]			可变长度位串

6. 字符串类型

名称	别名	存储大小	描述	与其他数据库对比
character (n)	char (n)，bpchar(n)	n个字节	固定长度字符串，不足会被空格填充	MySQL中char(n)
character varying (n)	varchar (n)	最多n个字节	可变长度字符串	MySQL中varchar(n)，Oracle中varchar(n)
bpchar		n个字符	可变不限制长度字符串，不足字符被空格填充
text			可变长度字符串	MySQL中text，Oracle中clob

7.日期时间类型

名称	存储大小	范围	描述
timestamp [ (p) ] [without time zone]	8字节	4713BC~294276AD	无时区的日期和时间
timestamp [ (p) ] [with time zone]	8字节	4713BC~294276AD	有时区的日期和时间
date	4字节	4713BC~5874897AD	日期
time [ (p) ] [without time zone]	8字节	00:00:00~24:00:00	时间
time [ (p) ] [with time zone]	12字节	00:00:00+1559~24:00:00-1559	有时区的时间
interval [fields] [(p)]	16字节	-178000000years~178000000years	时间间隔

8. 枚举类型

枚举类型是包含一系列有序的静态值集合的一个数据类型，使用前需要先声明
枚举类型使用CREATE TYPE来创建，如下所示：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

枚举创建后，就可以将其作为PostgresSQL预定义的类型使用

CREATE TABLE person (
	name text,
	current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');

9. 几何类型

PostgreSQL提供了点、线、矩形、多边形等几何类型，这也是区别于其他数据库的地方，如下表所示：

名称	存储大小	描述	表现形式
point	16字节	平面中的点	(x,y)
line	32字节	无限长的直线	{A,B,C}
lseg	32字节	有限线段	((x1,y1),(x2,y2))
box	32字节	矩形框	((x1,y1),(x2,y2))
path	16+16n字节	封闭路径(类似多边形)	((x1,y1),…)
path	16+16n字节	开放路径	[(x1,y1),…]
polygon	40+16n字节	多边形(类似封闭路径)	((x1,y1),…)
circle	24字节	圆	<(x,y),r>

10. 网络地址类型

PostgreSQL为IPv4、IPv6以及以太网MAC地址都提供了特有的类型，如下表所示：

名称	存储大小	描述
cidr	7或19字节	IPv4和IPv6网络地址
inet	7或19字节	IPv4和IPv6网络地址和主机地址
macaddr	6字节	以太网MAC地址
macaddr8	8字节	以太网MAC地址(EUI-64)

11. XML类型

xml类型可用于存储XML数据，插入数据时会对输入的数据进行检查，使不符合XML标准的数据不能存放到数据库中，同时还提供了函数对其类型进行安全性检查，可以使用函数xmlparse将字符串转换为xml数据，如下所示：

XMLPARSE ( {DOCUMENT | CONTENT} value)

示例如下：

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

也可以使用以下两种方式

xml '<foo>bar</foo>'
'<foo>bar</foo>'::xml

12. JSON类型

JSON数据类型可以用来存储JSON数据，插入数据时会自动检测字符串是否为合法的JSON数据，同时也提供了丰富的函数

名称	存储大小	描述
json		文本json数据
jsonb		重新解析的二进制json数据

12.1. JSON和JSONB区别

JSON类型是把输入的数据原封不动的存放到数据库中（存储前会做JSON语法校验），使用时需要重新解析数据。而JSONB类型是在存储时就把JSON解析为二进制格式，使用时无须再次解析，使用JSONB时性能会更高
JSON串中key之间多余空格、key的顺序和重复key会保留，JSONB不会保留多余空格和重复key
JSONB支持在其上建立索引，而JSON不支持

12.2. JSON类型与PostgreSQL数据库类型映射

当把一个JSON类型的字符串转换为JSONB类型时，JSON字符串内的数据类型实际上被转换成了PostgreSQL数据库中的类型，映射如下表所示：

JSON类型	PostgreSQL类型	注意事项
string	text	注意字符集的一些限制
number	numeric	JSON中没有PostgreSQL中的"NaN"和"infinity"值
boolean	boolean	JSON仅能接受小写的"true"和"false"
null	(none)	SQL中的NULL代表不同意思