PostgreSQL数据库相比其他数据库,支持更多的数据类型,包括常用的数值类型、字符串类型、日期/时间类型外,还有几何类型、网络地址类型、xml类型和json类型,且还可以使用CREATE TYPE自行添加数据类型,本文主要介绍PostgreSQL数据库主要的数据类型
1. 数值类型
常用数值类型如下表所示:
名称 | 别名 | 存储大小 | 范围 | 描述 | 与其他数据库对比 |
smallint | int2 | 2字节 | -225~215-1 | 有符号的2字节整数 | MySQL中smallint,Oracle中number(5) |
integer | int,int4 | 4字节 | -231~231-1 | 有符号的4字节整数 | MySQL中int,Oracle中number(38,0) |
bigint | int8 | 8字节 | -263~263-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代表不同意思 |