PostgreSQL 数据类型

1. 数字类型

数字类型由 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

2. 货币类型

money 类型存储固定小数精度的货币数字, 小数的精度由数据库的lc_monetary设置决定

numeric、int 和 bigint 类型的值可以转换为 money, 不建议使用浮点数来处理处理货币类型, 因为存在舍入错误的可能性

名字	存储尺寸	描述	范围
`money`	8 bytes	货币额	-92233720368547758.08到+92233720368547758.07

3. 字符类型

名字	描述
character varying(n), varchar(n)	有限制的变长
character(n), char(n)	定长, 空格填充
text	无限变长

4. 二进制数据类型

名字	存储尺寸	描述
`bytea`	1 或 4 字节外加真正的二进制串	变长二进制串

5. 日期/时间类型

名字	存储尺寸	描述	最小值	最大值	解析度
timestamp [ (p) ] [ without time zone ]	8字节	包括日期和时间（无时区）	4713 BC	294276 AD	1微秒
timestamp [ (p) ] with time zone	8字节	包括日期和时间，有时区	4713 BC	294276 AD	1微秒
date	4字节	日期（没有一天中的时间）	4713 BC	5874897 AD	1日
time [ (p) ] [ without time zone ]	8字节	一天中的时间（无日期）	00:00:00	24:00:00	1微秒
time [ (p) ] with time zone	12字节	仅仅是一天中的时间（没有日期），带有时区	00:00:00+1459	24:00:00-1459	1微秒
interval [ fields ] [ (p) ]	16字节	时间间隔	-178000000年	178000000年	1微秒

6. 布尔类型

PostgreSQL 支持标准的 boolean 数据类型。

boolean 有 "true" (真) 或 "false" (假) 两个状态, 第三种 "unknown" (未知)状态, 用 NULL 表示

名字	存储字节	描述
`boolean`	1字节	状态为真或假

输入函数接受这些字符串表示"真"状态: true yes 1 on
输入函数接受这些字符串表示"假"状态: false no 0 off

这些字符串的唯一前缀也可以接受, 例如 t 或 n, 前端或尾部的空格将被忽略, 并且大小写不敏感

7. 枚举类型

枚举(enum)类型是由一个静态、值的有序集合构成的数据类型, 它们等效于很多编程语言所支持的 enum 类型

声明方式

枚举类型的声明通过 create type 命令创建

sql

create type mood as enum ('sad', 'ok', 'happy')

排序: 一个枚举类型的值的排序是该类型被创建时所列出的值的顺序
类型安全性: 每一种枚举数据类型都是独立的并且不能和其他枚举类型相比较
实现细节: 枚举标签是大小写敏感的, 因此 'happy' 与 'HAPPY' 是不同的, 标签中的空格也是有意义的

8. 几何类型

几何数据类型表示二维的空间物体

名字	存储尺寸	表示	描述
`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>（中心点和半径）

9. 网络地址类型

PostgreSQL 提供用于存储 IPv4、IPv6 和 MAC 地址的数据类型, 用这些数据类型存储网络地址比用纯文本类型好, 因为这些类型提供输入错误检查以及特殊的操作符和函数

名字	存储尺寸	描述
`cidr`	7或19字节	IPv4和IPv6网络
`inet`	7或19字节	IPv4和IPv6主机以及网络
`macaddr`	6字节	MAC地址
`macaddr8`	8 bytes	MAC地址（EUI-64格式）

在对 inet 或者 cidr 数据类型进行排序的时候, IPv4 地址将总是排在 IPv6 地址前面, 包括那些封装或者是映射在 IPv6 地址里的 IPv4 地址, 例如 ::10.2.3.4 或者 ::ffff::10.4.3.2

10. 位串类型

位串就是一串 1 和 0 的串, 它们可以用于存储和可视化位掩码, 我们有两种类型的 SQL 位类型: bit(n) 和 bit varying(n), 其中 n 是一个正整数
bit 类型的数据必须准确匹配长度 n, 试图存储短些或者长一些的位串都是错误的, bit varying 数据是最长 n 的变长类型, 更长的串会被拒绝
写一个没有长度的 bit 等效于 bit(1), 没有长度的 bit varying 意味着没有长度限制

11. 文本搜索类型

全文搜索是一种在自然语言的文档集合中搜索以定位那些最匹配一个查询的文档的活动
PostgreSQL 提供两种数据类型, 它们被设计用来支持全文搜索

名字	描述
tsvector	tsvector 的值是一个无重复值的 lexemes 排序列表, 即一些同一个词的不同变种的标准化
tsquery	tsquery 存储用于检索的词汇, 并且使用布尔操作符 &(AND), \|(OR)和!(NOT) 来组合它们, 括号用来强调操作符的分组。

12. UUID 类型

数据类型 uuid 存储由 RFC 4122、ISO/IEC 9834-8:2005 以及相关标准定义的通用唯一标识符（UUID）（某些系统将这种数据类型引用为全局唯一标识符GUID）; 这种标识符是一个128位的量, 它由一个精心选择的算法产生, 该算法能保证在已知空间中任何其他使用相同算法的人能够产生同一个标识符的可能性非常非常小; 因此, 对于分布式系统, 这些标识符相比序列生成器而言提供了一种很好的唯一性保障, 序列生成器只能在一个数据库中保证唯一
一个 UUID 被写成一个小写十六进制位的序列, 该序列被连字符分隔成多个组: 首先是一个8位组, 接下来是三个4位组, 最后是一个12位组; 总共的32位（十六进制位）表示了128个二进制位; 一个标准形式的UUID类似于:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

PostgreSQL也接受另一种输入形式: 使用大写位、标准格式被花括号包围、忽略某些或者全部连字符、在任意4位组后面增加一个连字符

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

输出总是采用标准形式

13. XML 类型

xml 数据类型可以被用来存储 XML 数据; 它比直接在一个 text 域中存储 XML 数据的优势在于, 它会检查输入值的结构是不是良好, 并且有支持函数用于在其上执行类型安全的操作, 使用这种数据类型要求在安装时用 configure --with-libxml 选项编译
xml 类型可以存储格式良好的遵循 XML 标准定义的"文档", 以及"内容"片段, 它是通过引用更宽泛的"document node" XQuery 和 XPath 数据模型来定义的
大致上说, 这意味着内容片段中可以有多于一个的顶层元素或字符节点
表达式 xmlvalue IS DOCUMENT 可以被用来评估一个特定的 xml 值是一个完整文档或者仅仅是一个文档片段
创建方式: xmlparse ( { document | content } value)

sql

xmlparse (document '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
xmlparse (content 'abc<foo>bar</foo><bar>foo</bar>')

14. JSON 类型

JSON 数据类型是用来存储 JSON（JavaScript Object Notation）数据的; 这种数据也可以被存储为 text, 但是 JSON 数据类型的优势在于能强制要求每个被存储的值符合 JSON 规则; 也有很多 JSON 相关的函数和操作符可以用于存储在这些数据类型中的数据

PostgreSQL 提供存储JSON数据的两种类型: json 和 jsonb
区别:

	JSON	JSONB
输入值	几乎完全相同的值集合	几乎完全相同的值集合
存储格式	文本形式, 保留空格和键的顺序	分解的二进制格式, 不保留空格和键的顺序
解析	每次执行时必须重新解析	不需要解析
转换	需要附加的转换	在输入时需要稍慢一些
索引	不支持索引	支持索引
键处理	保留所有键/值对，包括重复的键/值对	只保留最后一个键/值对

通常, 除非有特别特殊的需要（例如遗留的对象键顺序假设）, 大多数应用应该更愿意把 JSON 数据存储为 jsonb

15. 数组类型

PostgreSQL 允许一个表中的列定义为变长多维数组; 可以创建任何内建或用户定义的基类、枚举类型、组合类型或者域的数组

数组类型定义: 在创建表时, 可以这样子声明数组

sql

create table sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组, 我们也可以使用 "ARRAY" 关键字, 如下所示:

sql

create table sal_emp (
   name text,
   pay_by_quarter integer ARRAY[4],
   schedule text[][]
);

PostgreSQL 数据类型

1. 数字类型

2. 货币类型

3. 字符类型

4. 二进制数据类型

5. 日期/时间类型

6. 布尔类型

7. 枚举类型

8. 几何类型

9. 网络地址类型

10. 位串类型

11. 文本搜索类型

12. UUID 类型

13. XML 类型

14. JSON 类型

15. 数组类型

16. 组合类型

17. 范围类型

18. 域类型

19. 对象标识符类型

20. 索引

21. 伪类型

PostgreSQL 数据类型 ​

1. 数字类型 ​

2. 货币类型 ​

3. 字符类型 ​

4. 二进制数据类型 ​

5. 日期/时间类型 ​

6. 布尔类型 ​

7. 枚举类型 ​

8. 几何类型 ​

9. 网络地址类型 ​

10. 位串类型 ​

11. 文本搜索类型 ​

12. UUID 类型 ​

13. XML 类型 ​

14. JSON 类型 ​

15. 数组类型 ​

16. 组合类型 ​

17. 范围类型 ​

18. 域类型 ​

19. 对象标识符类型 ​

20. 索引 ​

21. 伪类型 ​

PostgreSQL 数据类型

1. 数字类型

2. 货币类型

3. 字符类型

4. 二进制数据类型

5. 日期/时间类型

6. 布尔类型

7. 枚举类型

8. 几何类型

9. 网络地址类型

10. 位串类型

11. 文本搜索类型

12. UUID 类型

13. XML 类型

14. JSON 类型

15. 数组类型

16. 组合类型

17. 范围类型

18. 域类型

19. 对象标识符类型

20. 索引

21. 伪类型