本章节,我们将讨论 PostgreSQL 的数据类型,数据类型是我们在创建表的时候为每个字段设置的。
设置数据类型的好处:
PostgreSQL提 供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型有很多种,下面我们具体来说明。
数值类型
数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。
下表列出了可用的数值类型。
名字 | 存储长度 | 描述 | 范围 |
smallint | 2 字节 | 小范围整数 | -32768 到 +32767 |
integer | 4 字节 | 常用的整数 | -2147483648 到 +2147483647 |
bigint | 8 字节 | 大范围整数 | -9223372036854775808 到 +9223372036854775807 |
decimal | 可变长 | 用户指定的精度,精确 | 小数点前 131072 位;小数点后 16383 位 |
numeric | 可变长 | 用户指定的精度,精确 | 小数点前 131072 位;小数点后 16383 位 |
real | 4 字节 | 可变精度,不精确 | 6 位十进制数字精度 |
double precision | 8 字节 | 可变精度,不精确 | 15 位十进制数字精度 |
smallserial | 2 字节 | 自增的小范围整数 | 1 到 32767 |
serial | 4 字节 | 自增整数 | 1 到 2147483647 |
bigserial | 8 字节 | 自增的大范围整数 | 1 到 9223372036854775807 |
货币类型
money 类型存储带有固定小数精度的货币金额。
numeric、int 和 bigint 类型的值可以转换为 money,不建议使用浮点数来处理处理货币类型,因为存在舍入错误的可能性。
名字 | 存储容量 | 描述 | 范围 |
money | 8 字节 | 货币金额 | -92233720368547758.08 到 +92233720368547758.07 |
字符类型
下表列出了 PostgreSQL 所支持的字符类型:
序号 | 名字 & 描述 |
1 | character varying(n), varchar(n) 变长,有长度限制 |
2 | character(n), char(n) f定长,不足补空白 |
3 | text 变长,无长度限制 |
日期/时间类型
下表列出了 PostgreSQL 支持的日期和时间类型。
名字 | 存储空间 | 描述 | 最低值 | 最高值 | 分辨率 |
timestamp [ ( p ) ] [ without time zone ] | 8 字节 | 日期和时间(无时区) | 4713 BC | 294276 AD | 1 毫秒 / 14 位 |
timestamp [ ( p ) ] with time zone | 8 字节 | 日期和时间,有时区 | 4713 BC | 294276 AD | 1 毫秒 / 14 位 |
date | 4 字节 | 只用于日期 | 4713 BC | 5874897 AD | 1 天 |
time [ ( p ) ] [ without time zone ] | 8 字节 | 只用于一日内时间 | 00:00:00 | 24:00:00 | 1 毫秒 / 14 位 |
time [ ( p ) ] with time zone | 12 字节 | 只用于一日内时间,带时区 | 00:00:00+1459 | 24:00:00-1459 | 1 毫秒 / 14 位 |
interval [ fields ] [ ( p ) ] | 12 字节 | 时间间隔 | -178000000 年 | 178000000 年 | 1 毫秒 / 14 位 |
布尔类型
PostgreSQL 支持标准的 boolean 数据类型。
boolean 有"true"(真)或"false"(假)两个状态, 第三种"unknown"(未知)状态,用 NULL 表示。
名称 | 存储格式 | 描述 |
boolean | 1 字节 | true/false |
枚举类型
枚举类型是一个包含静态和值的有序集合的数据类型。
PostgreSQL 中的枚举类型类似于 C 语言中的 enum 类型。
与其他类型不同的是枚举类型需要使用 CREATE TYPE 命令创建。
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
创建一周中的几天,如下所示:
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
就像其他类型一样,一旦创建,枚举类型可以用于表和函数定义。
几何类型
几何数据类型表示二维的平面物体。
下表列出了 PostgreSQL 支持的几何类型。
最基本的类型:点。它是其它类型的基础。
名字 | 存储空间 | 说明 | 表现形式 |
point | 16 字节 | 平面中的点 | (x,y) |
line | 32 字节 | (无穷)直线(未完全实现) | ((x1,y1),(x2,y2)) |
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 字节 | 圆 | (圆心和半径) |
网络地址类型
PostgreSQL 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型。
用这些数据类型存储网络地址比用纯文本类型好, 因为这些类型提供输入错误检查和特殊的操作和功能。
名字 | 存储空间 | 描述 |
cidr | 7 或 19 字节 | IPv4 或 IPv6 网络 |
inet | 7 或 19 字节 | IPv4 或 IPv6 主机和网络 |
macaddr | 6 字节 | MAC 地址 |
在对 inet 或 cidr 数据类型进行排序的时候, IPv4 地址总是排在 IPv6 地址前面,包括那些封装或者是映射在 IPv6 地址里的 IPv4 地址, 比如 ::10.2.3.4 或 ::ffff:10.4.3.2。
位串类型
位串就是一串 1 和 0 的字符串。它们可以用于存储和直观化位掩码。 我们有两种 SQL 位类型:bit(n) 和bit varying(n), 这里的n是一个正整数。
bit 类型的数据必须准确匹配长度 n, 试图存储短些或者长一些的数据都是错误的。bit varying 类型数据是最长 n 的变长类型;更长的串会被拒绝。 写一个没有长度的bit 等效于 bit(1), 没有长度的 bit varying 意思是没有长度限制。
文本搜索类型
全文检索即通过自然语言文档的集合来找到那些匹配一个查询的检索。
PostgreSQL 提供了两种数据类型用于支持全文检索:
序号 | 名字 & 描述 |
1 | tsvector tsvector 的值是一个无重复值的 lexemes 排序列表, 即一些同一个词的不同变种的标准化。 |
2 | tsquery tsquery 存储用于检索的词汇,并且使用布尔操作符 &(AND),|(OR)和!(NOT) 来组合它们,括号用来强调操作符的分组。 |
UUID 类型
uuid 数据类型用来存储 RFC 4122,ISO/IEF 9834-8:2005 以及相关标准定义的通用唯一标识符(UUID)。 (一些系统认为这个数据类型为全球唯一标识符,或GUID。) 这个标识符是一个由算法产生的 128 位标识符,使它不可能在已知使用相同算法的模块中和其他方式产生的标识符相同。 因此,对分布式系统而言,这种标识符比序列能更好的提供唯一性保证,因为序列只能在单一数据库中保证唯一。
UUID 被写成一个小写十六进制数字的序列,由分字符分成几组, 特别是一组8位数字+3组4位数字+一组12位数字,总共 32 个数字代表 128 位, 一个这种标准的 UUID 例子如下:
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
XML 类型
xml 数据类型可以用于存储XML数据。 将 XML 数据存到 text 类型中的优势在于它能够为结构良好性来检查输入值, 并且还支持函数对其进行类型安全性检查。 要使用这个数据类型,编译时必须使用 configure --with-libxml。
xml 可以存储由XML标准定义的格式良好的"文档", 以及由 XML 标准中的 XMLDecl? content 定义的"内容"片段, 大致上,这意味着内容片段可以有多个顶级元素或字符节点。 xmlvalue IS DOCUMENT 表达式可以用来判断一个特定的 xml 值是一个完整的文件还是内容片段。
创建XML值
使用函数 xmlparse: 来从字符数据产生 xml 类型的值:
数组类型可以是任何基本类型或用户定义类型,枚举类型或复合类型。
JSON 类型
json 数据类型可以用来存储 JSON(JavaScript Object Notation)数据, 这样的数据也可以存储为 text,但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。
此外还有相关的函数来处理 json 数据:
实例 | 实例结果 |
array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
数组类型
PostgreSQL 允许将字段定义成变长的多维数组。
数组类型可以是任何基本类型或用户定义类型,枚举类型或复合类型。
声明数组
创建表的时候,我们可以声明数组,方式如下:
CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] );
pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。
我们也可以使用 "ARRAY" 关键字,如下所示:
CREATE TABLE sal_emp ( name text, pay_by_quarter integer ARRAY[4], schedule text[][] );