PostgreSQL9.6数据类型梳理

数据类型指定要在表字段中存储哪种类型的数据。 在创建表时，对于每列必须使用数据类型。
PotgreSQL中主要有三种类型的数据类型。 此外，用户还可以使用CREATE TYPE SQL命令创建自己的自定义数据类型。

以下是PostgreSQL中主要有三种类型的数据类型：

• 数值数据类型
• 字符串数据类型
• 日期/时间数据类型

数值数据类型

数字数据类型用于指定表中的数字数据。

名称	描述	存储大小	范围
smallint	存储整数，小范围	2字节	-32768 至 +32767
integer	存储整数。使用这个类型可存储典型的整数	4字节	-2147483648 至 +2147483647
bigint	存储整数，大范围。	8字节	-9223372036854775808 至 9223372036854775807
decimal	用户指定的精度，精确	变量	小数点前最多为131072个数字; 小数点后最多为16383个数字
numeric	用户指定的精度，精确	变量	小数点前最多为131072个数字; 小数点后最多为16383个数字
real	可变精度，不精确	4字节	6位数字精度
double	可变精度，不精确	8字节	15位数字精度
smallserial	自动递增整数	2字节	1 至 32767
serial	自动递增整数	4字节	1 至 2147483647
bigserial	大的自动递增整数	8字节	1 至 9223372036854775807

整数类型

常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 smallint类型。而只有在integer的范围不够的时候才使用bigint。

字符串数据类

数据类型	描述
char(size)	这里size是要存储的字符数。固定长度字符串，右边的空格填充到相等大小的字符。
character(size)	这里size是要存储的字符数。 固定长度字符串。 右边的空格填充到相等大小的字符。
varchar(size)	这里size是要存储的字符数。 可变长度字符串。
character varying(size)	这里size是要存储的字符数。 可变长度字符串。
text	可变长度字符串。

如果我们明确地把一个值造型成character varying(n)或者character(n)，那么超长的值将被截断成n个字符，而不会抛出错误（这也是SQL标准的要求）。

varchar(n)和char(n)的概念分别是character varying(n)和character(n)的别名。没有长度声明词的character等效于character(1)。如果不带长度说明词使用character varying，那么该类型接受任何长度的串。后者是一个PostgreSQL的扩展。

类型character的值物理上都用空白填充到指定的长度n， 并且以这种方式存储和显示。不过，拖尾的空白被当作是没有意义的，并且在比较两个 character类型值时不会考虑它们。

日期/时间数据类型

名称	描述	存储大小	最小值	最大值	解析度
timestamp [ (p) ] [ without time zone ]	日期和时间(无时区)	8字节	4713 bc	294276 ad	1微秒/14位数
timestamp [ (p) ]with time zone	包括日期和时间，带时区	8字节	4713 bc	294276 ad
date	日期(没有时间)	4字节	4713 bc	5874897 ad	1微秒/14位数
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位数

特殊日期/时间输入

输入串	合法类型	描述
epoch	date, timestamp	1970-01-01 00:00:00+00（Unix系统时间0）
infinity	date, timestamp	比任何其他时间戳都晚
-infinity	date, timestamp	比任何其他时间戳都早
now	date, time, timestamp	当前事务的开始时间
today	date, timestamp	当日午夜
tomorrow	date, timestamp	明日午夜
yesterday	date, timestamp	昨日午夜
allballs	time	00:00:00.00 UTC

当前日期/时间

PostgreSQL提供了许多返回当前日期和时间的函数。这些 SQL 标准的函数全部都按照当前事务的开始时刻返回值：

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)
CURRENT_TIMESTAMP::timestamp(0)without time zone

一些其他数据类型

布尔类型：

名称	描述	存储大小
boolean	它指定true或false的状态。	1字节

货币类型：

名称	描述	存储大小	范围
money	货币金额	8字节	-92233720368547758.08 至+92233720368547758.07

几何类型：

名称	存储大小	表示	描述
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),…)
polygon	40+16n字节	多边形(类似于封闭路径)	((x1,y1),…)
circle	24字节	圆	<(x，y)，r>(中心点和半径)

网络地址类型

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

名称	存储尺寸	描述
cidr	7或19字节	IPv4和IPv6网络
inet	7或19字节	IPv4和IPv6主机以及网络
macaddr	6字节	MAC地址

UUID类型

数据类型uuid存储由RFC 4122、ISO/IEC 9834-8:2005以及相关标准定义的通用唯一标识符（UUID）（某些系统将这种数据类型引用为全局唯一标识符GUID）。这种标识符是一个128位的量，它由一个精心选择的算法产生，该算法能保证在已知空间中任何其他使用相同算法的人能够产生同一个标识符的可能性非常非常小。因此，对于分布式系统，这些标识符相比序列生成器而言提供了一种很好的唯一性保障，序列生成器只能在一个数据库中保证唯一。

XML类型

xml数据类型可以被用来存储XML数据。它比直接在一个text域中存储XML数据的优势在于，它会检查输入值的结构是不是良好，并且有支持函数用于在其上执行类型安全的操作,使用这种数据类型要求在安装时用configure –with-libxml选项编译。

JSON与JSONB类型

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

有两种 JSON 数据类型：json 和 jsonb。它们 几乎接受完全相同的值集合作为输入。主要的实际区别之一是效率。json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。而jsonb数据被存储在一种分解好的二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是jsonb在处理时要快很多，因为不需要解析。另外，jsonb能在整列建立GIN/GIST索引，而json不能建立这样的索引。

数组

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

9.6版本的实现忽略任何提供的数组尺寸限制，即其行为与未指定长度的数组相同。

也不会强制所声明的维度数。一个特定元素类型的数组全部被当作是相同的类型，而不论其尺寸或维度数。因此，在CREATE TABLE中声明数组的尺寸或维度数仅仅只是文档而已，它并不影响运行时的行为。
多维数组的每一维都必须有相匹配的长度，不匹配会造成错误