postgresql 10 数据类型 (完整版)

官方数据类型document https://www.postgresql.org/docs/10/static/datatype.html

PostgreSQL拥有丰富的数据类型供用户使用。用户也可以使用 CREATE TYPE 命令向PostgreSQL添加新类型。

通用的数据类型

表1是内置的通用数据类型， “别名”列中是PostgreSQL内部使用的名称，这是由于历史原因，某些内部使用或已弃用的类型未在此处列出。
此表基本能涵盖大部分正常的需求，更丰富的数据类型参考下面的明细，部分不常用的没来得及整理先参考官方链接的文档~~

类型名称	别名	描述
bigint	int8	有符号八字节整数
bigserial	serial8	自动递增八字节整数
bit (n)		固定长度的bit字符串
bit varying (n)	varbit (n)	可变长度的bit字符串
boolean	bool	逻辑布尔值（true / false）
box		平面上的矩形框
bytea		二进制数据（“字节数组”）
character (n)	char (n)	固定长度的字符串
character varying (n)	varchar (n)	可变长度字符串
cidr		IPv4或IPv6网络地址
circle		平面上的圆圈
date		日历日期（年，月，日）
double precision	float8	双精度浮点数（8字节）
inet		IPv4或IPv6主机地址
integer	int, int4	有符号的四字节整数
interval [ fields ] [ (p) ]		时间跨度
json		文本JSON数据
jsonb		二进制JSON数据
line		平面上的线
lseg		平面上的线段
macaddr		MAC（媒体访问控制）地址
macaddr8		MAC（媒体访问控制）地址（EUI-64格式）
money		货币金额
numeric (p, s)	decimal (p, s)	可选精度的精确数字
path		平面上的几何路径
pg_lsn		PostgreSQL日志序列号
point		平面上的几何点
polygon		平面上的闭合的几何路径
real	float4	单精度浮点数（4个字节）
smallint	int2	签名的双字节整数
smallserial	serial2	自动递增两个字节的整数
serial	serial4	自动递增四字节整数
text		可变长度字符串
time (p) without time zone		一天中的时间（没有时区）
time (p) with time zone	timetz	一天中的时间，含时区
timestamp (p) without time zone		日期和时间（没有时区）
timestamp (p) with time zone	timestamptz	日期和时间，含时区
tsquery		文本搜索查询
tsvector		文本搜索文档
txid_snapshot		用户级事务ID快照
uuid		普遍唯一的标识符
xml		XML数据

1、数字类型

Numeric Types - https://www.postgresql.org/docs/10/static/datatype-numeric.html

类型名称	存储大小	描述	范围
smallint	2 bytes	整数(小范围)	-32768 至 +32767
integer	4 bytes	整数(常用)	-2147483648 至 +2147483647
bigint	8 bytes	整数(大范围)	-9223372036854775808 至 +9223372036854775807
decimal	variable	可指定精确精度	小数点前最多131072位; 小数点后最多16383位
numeric (p,s)	variable	可指定精确精度	小数点前最多131072位; 小数点后最多16383位
real	4 bytes	精度可变	精度至少6位
double precision	8 bytes	精度可变	精度至少15位
smallserial	2 bytes	自动递增整数(小范围)	1 至 32767
serial	4 bytes	自动递增整数	1 至 2147483647
bigserial	8 bytes	自动递增整数(大范围)	1 至 9223372036854775807

2、货币类型

Monetary Types - https://www.postgresql.org/docs/10/static/datatype-money.html

类型名称	存储大小	描述	范围
money	8 bytes	货币金额，保留2位小数	-92233720368547758.08 至 +92233720368547758.07

money固定小数点后2位，超过2位长度会四舍五入，仔细看会发现金额前面有货币类型，这个是根据服务端的lc_monetary设置的。

select '12.34567'::money;
---------------------
￥12.35

/*查看货币类型*/
show lc_monetary;
---------------------
Chinese (Simplified)_People's Republic of China.936

/*重设货币类型*/
set lc_monetary='C';

/*再次查询货币，发现货币类型改了*/
select '12.34567'::money;
---------------------
$12.35

比较坑爹的是lc_monetary的设置是跟着数据库走的，也就是一个数据库只能有一种lc_monetary，所以不建议用money类型，改用numeric

3、字符类型

Character Types - https://www.postgresql.org/docs/10/static/datatype-character.html

类型名称	存储大小	描述
character (n)	n	如果存储的值比字段申明长度短，会用空白代替，不声明长度n默认是1
character varying (n)	n	如果存储的值比字段申明长度短，不会用空白代替，不声明长度n代表任意长度
text	任意长度	大文本使用
"char"	1 byte	注意数据类型有引号，非一般用户用，主要用于内部系统目录中作为简单的枚举类型
name	64 bytes	固定长度，非一般用户用，主要用于内部系统目录中存储标识符

character实际存储和显示的会用空白填充长度，但是比较字符串或者转换成其他字符串，填充的空白会被删除
character varying和text不会默认用空白填充长度，但文本本身尾部有空白的话不会默认去掉，这种空白是有意义的
大多数情况下，字符串类型一般用character varying或text

4、二进制数据类型

Binary Data Types - https://www.postgresql.org/docs/10/static/datatype-binary.html

类型名称	存储大小	描述
bytea	1或4个字节加上实际的二进制字符串	可变长度二进制字符串

bytea 类型支持两种输入和输出格式："escape" 和 "hex"
输出格式默认是 “hex”，可以通过配置参数bytea_output 进行修改，如下代码第一行，window下实测不改也不影响中文呈现，liunx下可能需要设置

set bytea_output = 'escape';
/* 创建表 */
CREATE TABLE testbytea (sid serial,info bytea);
/* 插入数据 */
insert into testbytea(info) values('内容1')
/* 如果数据量很大可以用 || 分批次追加更新内容，更新完后完整内容是 "内容1 hello2" */
update testbytea set info=info||cast(' hello2' as bytea) where sid=1;

/* 中文查询方式1：用默认的解码方式，再转一遍utf8可以直接解决中文呈现问题 */
select sid,convert_from(info, 'utf8') from testbytea;
-------+-------------
  1    |  内容1 hello2

/* 中文查询方式2： 指定解码方式直接查询，中文显示有问题，字母和数字正常 */
select sid,encode(info,'escape')as info from testbytea;
--------+------------
  1     | \345\206\205\345\256\2711 hello2

/* 中文查询方式2：注意convert_from没法直接放在上面的encode里嵌套，需要复制下文本再转换一下utf8 */
SELECT convert_from('\345\206\205\345\256\2711 hello2', 'utf8');
---------------------
内容1 hello2

5、日期/时间类型

Date/Time Types - https://www.postgresql.org/docs/10/static/datatype-datetime.html

类型名称	存储大小	描述	最小值	最大值	最小精度
timestamp (p) without time zone	8 bytes	日期和时间 (不含时区)	公元前 4713年	公元 294276年	微秒
timestamp (p) with time zone	8 bytes	日期和时间 (含时区)	公元前 4713年	公元 294276年	微秒
date	4 bytes	日期，不含时间	公元前 4713年	公元 5874897年	天
time (p) without time zone	8 bytes	时间，不含日期 (不含时区)	00:00:00	24:00:00	微秒
time (p) with time zone	12 bytes	时间，不含日期 (含时区)	00:00:00+1459	24:00:00-1459	微秒
interval [ fields ] (p)	16 bytes	时间间隔	-178000000 年	178000000 年	微秒
timestamp如果只填写时间则认为没有时区，时区作为一个可选参数
time、timestamp和interval接受一个可选的精度值p，指在seconds中保留的小数位数，默认没有限制但不能超过6 ( 注意： p范围是0到6 )

/*没有时区，p>6时的结果与p=6结果一致。使用pgAdmin添加timestamp字段时提示长度不能大于9是错误的，应是不能大于6*/
select timestamp(6) without time zone '2018-08-01 12:01:01.12345678910';
---------------------
2018-08-01 12:01:01.123457

/*有时区 */
select timestamp(6) with time zone '2018-08-01 12:01:01.12345678910';
---------------------
2018-08-01 12:01:01.123457+08

interval 类型有一个附加选项，它通过编写以下短语之一来限制存储字段集：

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

6、布尔类型

Boolean Type Types - https://www.postgresql.org/docs/10/static/datatype-boolean.html

类型名称	存储大小	描述
boolean	1 bytes	true 或 false

true状态的有效字符为： TRUE 't' 'true' 'y' 'yes' 'on' '1'
false状态的有效字符为： FALSE 'f' 'false' 'n' 'no' 'off' '0'

7、枚举类型

Enumerated Types - https://www.postgresql.org/docs/10/static/datatype-enum.html
使用CREATE TYPE命令创建枚举类型

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

和代码里的枚举类似，每个枚举有对应的一个值，所以可以实现 WHERE current_mood > 'sad' (查出'ok' 和 'happy') 或者 ORDER BY current_mood DESC (根据枚举对应的值进行排序，非字符串)

8、几何类型

Geometric Types - https://www.postgresql.org/docs/10/static/datatype-geometric.html

类型名称	存储大小	描述	示例
point	16 bytes	平面坐标	(x,y)
line	32 bytes	无穷线
lseg	32 bytes	线段	((x1,y1),(x2,y2))
box	32 bytes	矩形框	((x1,y1),(x2,y2))
path	16+16n bytes	多边形封闭路径	((x1,y1),...)
path	16+16n bytes	开放路径	[(x1,y1),...]
polygon	40+16n bytes	多边形（类似封闭路径）	((x1,y1),...)
circle	24 bytes	圆形	<(x,y),r> (center point and radius)

9、网络地址类型

Network Address Types - https://www.postgresql.org/docs/10/static/datatype-net-types.html

类型名称	存储大小	描述
cidr	7 or 19 bytes	IPv4 and IPv6 networks
inet	7 or 19 bytes	IPv4 and IPv6 hosts and networks
macaddr	6 bytes	MAC addresses
macaddr8	8 bytes	MAC addresses (EUI-64 format)

tsvector：类型表示以文本搜索优化的形式的文档
tsquery：类型类似地表示文本查询

12、UUID类型

UUID Type - https://www.postgresql.org/docs/10/static/datatype-uuid.html
此数据类型称为全局唯一标识符，或称为GUID，此标识符是128位数量，由选择的算法生成，其他人不基本不可能生成相同的唯一标识。
示例标识符如下：

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 Type - https://www.postgresql.org/docs/10/static/datatype-xml.html
用于存储XML数据，它与在文本字段中直接存储XML数据的优势是它会检查输入值的格式是否正确，并且有支持函数对其执行类型安全操作;

14、JSON类型

JSON Type - https://www.postgresql.org/docs/10/static/datatype-json.html
此类数据也可以存储为text，但JSON数据类型的优点是可以根据JSON规则强制每个存储的值有效。还有各种特定于JSON的函数和运算符可用于存储在这些数据类型中的数据，JSON有两种数据类型 json和jsonb，两种类型的主要区别是效率：
json：可以理解为纯文本插入，插入效率高，实际解析时候需要重新执行分析
jsonb：存储为二进制，插入效率略慢，但处理要快很多，因为不需要重新分析，且jsonb还支持索引，一般大部分程序会使用jsonb。