PostgreSQL的基本类型和 Schema，我又学废了

数据库系列

1、Linux安装Postgres，你学费了吗？
2、PostgreSQL的基本类型和 Schema，我又学废了

前言

一、PostGreSQL 数据类型

PostgreSQL 数据库提供了及其丰富的数据类型，我们在定义表结构的时候，要指定每个字段的类型，就需要明了每个数据类型的特性，以便我们在指定字段类型的时候能根据业务需要指定合适的类型。
更全面的了解可移步PG中文官网：http://www.postgres.cn/docs/11/datatype.html

1.1 字符串类型

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

1.2 数字类型

类型	存储长度	描述	范围
smallint	2字节	小范围整数	-32768 ~ 32767
integer	4字节	整数范围	-2147483648 to +2147483647
bigint	8字节	大范围整数	-9223372036854775808 to +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

1.3 日期/时间类型

类型	存储长度	描述	最小值	最大值	解析度
timestamp [ without time zone ]	8字节	日期和时间（无时区）	4713 BC	294276 AD	1微妙
timestamp [ with time zone ]	8字节	日期和时间（有时区）	4713 BC	294276 AD	1微妙
date	4字节	日期	4713 BC	294276 AD	1日
time [ without time zone ]	8字节	一天中的时间（无日期）	00:00:00	24:00:00	1微妙
time [ with time zone ]	12字节	一天中的时间（无日期），有时区	00:00:00+1459	24:00:00-1459	1微妙
interval	16字节	时间间隔	-178000000年	178000000年	1微妙

注意：SQL只要写 timestamp 等效于 timestamp whtiout time zone，并且 PG 也是鼓励这种方式，timestampz 则是 timestamp with time zone 的一种简写，PostgreSQL 的扩展类型。

1.4 JSON 类型

1.4.1 json 和 jsonb

有两种 JSON 类型：json 和 jsonb。它们都可以接受 json 格式的值的集合作为输入，主要的区别之一就是效率。

• json
  数据类型的输入是 json 文本的精准拷贝，处理函数必须在每次执行是重新解析该数据。
  可能会保留语法上的不明显的、存在与记号之间的空格，还有 JSON 对象内键的顺序，如果一个键包含多次，所有的键值都会被保留（处理函数会把最后的值当作有效值）。
• jsonb
  数据被存储在一种解析好的二进制格式中，它在输入时要稍微慢一些，因为需要做附加的转换。但是 jsonb 在处理时间要快很多，因为不需要解析。jsonb 也支持索引，这是一个非常令人瞩目的优势。
  jsonb 不保留空格，不保留对象键的顺序，也不保留重复的对象键，如果输入了重复的键，最后一个将会被保留。

通常，除非有特别的需要，大多数应用更愿意把 JSON 数据存储在 jsonb 中。

1.4.2 JSON 和 PostgreSQL 类型对应

JSON基本类型和相应的 PostgreSQL 类型：

JSON基本类型	PostgreSQL 类型	注释
string	text	不允许\u0000，如果数据库编码不是 UTF8，非 ASCII Unicode 转义也是这样
number	numeric	不允许NaN 和 infinity值
boolean	boolean	只接受小写true和false拼写
null	(无)	SQL NULL是一个不同的概念

1.4.3 有效合理的使用 JSON

将数据存储为 JSON 类型比传统的数据模型更加灵活，在需求不固定时，这种优势更加突出优势。不过，即便是一个要求最大灵活性的应用中，还是推荐使用 JSON 文档固定的格式，虽然格式不是固定的，但是有一个可预测的结构会使书写概括一个表中的数据查询更容易。
JSON 存储在表中时，

1.4.4 jsonb 包含和存在

1. 简单的标量/基本值只包含相同的值

select '"foo"'::jsonb @> '"foo"'::jsonb;

2. 右边的数字被包含在左边的数组中

select '[1,2,3]'::jsonb @> '[1,2,2]'::jsonb;

3. 数组元素的顺序没有意义，因此这个例子也返回真

select '[1,2,3]'::jsonb @> '[3,1]'::jsonb;

4. 重复的数组元素也没有关系

select '[1,2,3]'::jsonb @> '[1,2,2]'::jsonb;

5. 右边具有一个单一键值对的对象被包含在左边的对象中

select '{"product":"pg", "version":9.4, "jsonb":true}'::jsonb @> '{"version":9.4}'::jsonb;

6. 右边的数组不会被认为包含在左边的数组中，即使其中嵌入了一个相似的数组

select '[1,2,[1,3]]'::jsonb @> '[1,3]'::jsonb;

但是如果同样也有嵌套，包含就成立

select '[1,2,[1,3]]'::jsonb @> '[[1,3]]'::jsonb;

7. 包含一个顶层键和一个空对象

select '{"foo":{"bar": "baz"}}'::jsonb @> '{"foo":{}}'::jsonb;

8. 字符串作为一个对象键存在

select '{"foo":"bar"}'::jsonb ? 'foo';

9. 和包含一样，存在必须在顶层匹配

select '{"foo":{"bar":"baz"}}'::jsonb ? 'bar';

1.5 货币类型

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

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

1.6 其他类型

除了以上数据类型，还包括其他数据类型，包括：二进制数据类型、布尔类型、枚举类型、几何类型、网络地址类型、位串类型、文本搜索类型、UUID类型、XML类型、数组类型、范围类型等。
后续更新这些类型使用。

二、PostgreSQL 中的 Schema

2.1 Schema 是什么？

一个 PostgreSQL 数据库集群中包含一个或多个数据库，角色和一些其他对象类型被整个集群共享，连接到服务器的客户端只能访问单个数据库中的数据，可以在连接请求中指定那一个。

一个数据库包含一个或者多个命名模式，模式中包含着表。模式还包含其他类型的命名对象，包括 数据类型、函数 和 操作符。相同的对象名称可以被用于不同模式中而不会出现冲突，比如 mySchema 和 phenSchema 都可以创建 tb_user 的表，见下图：

和数据库不同，模式并不是严格地隔离：一个用户可以访问它所连接的数据库中的所有模式内的对象，只要有足够的权限。

2.2 Schema 有什么好处？

使用模式带来的好处如下：

1. 允许多个用户使用同一个数据库并且不会互相干扰。
2. 将数据库对象组织成逻辑组以便容易进行管理。
3. 第三方应用的对象可以放在独立的模式中，这样就不会和其他对象的名称发生冲突。.

2.3 公共模式

在上图中可以看到在 postgres 数据库模式下有一个名叫 public 的模式，默认情况下不指定模式，这些表（以及其他对象）会自动的被放入 “public” 的模式中。任何新数据库都包含这样一个模式，因此下面命令是等效的：

create table tb_user(...);
create table public.tb_user(...)

三 数据类型总结

类型	别名	描述
bigint	int8	有符号的 8 字节整数
bigserial	serial8	自动增长的 8 字节整数
bit varying[ n ]	varbit [ n ]	变长位串
boolean	bool	逻辑布尔值
bytea		二进制数据【字节数组】
character [ n ]	char [ n ]	定长字符串
character varying	varchar [ n ]	变长字符串
date		日历日期（年、月、日）
double precision	float 8	双精度浮点数（8字节）
integer	int, int4	有符号4字节整数
interval [ fields ] [ § ]		时间段
json		文本JSON数据
jsonb		二进制 JSON 数据，已分解
money		货币数量
numeric [ (p, s) ]	decimal [ (p, s) ]	可选择进度的精确数字
pg_lsn		PostgreSQL日志序列号
real	float 4	单精度浮点数（4字节）
smallint	int 2	有符号2字节整数
smallserial	serial2	自动增长的2字节整数
serial	serial4	自动增长的4字节整数
text		变长字符串
time [ § ] [ without time zone ]		一天中的时间（无时区）
time [ § ] with time zone	timetz	一天中的时间，包括时区
timestamp [ § ] with time zone		日期和时间，包括时区
timestamp [ § ] with time zone	timestamptz	日期和时间，包括时区
tsquery		文本搜索查询
tsvector		文本搜索文档
txid_snapshot		用户级别事务ID快照
uuid		通用唯一标识码
xml		XML数据
bit [ (n) ]		定长位串
box		平面上的普通方框
cidr		IPv4或IPv6网络地址
inet		IPv4或IPv6主机地址
circle		平面上的圆
line		平面上的无限长的线
lseg		平面上的线段
macaddr		MAC（Media Access Control）地址
macaddr8		MAC（Media Access Control）地址（EUI-64格式）
path		平面上的几何路径
point		平面上的几何点
polygon		平面上的封闭几何路径