PostgreSQL -- 数据类型

主要参考 菜鸟教程

数据类型是在创建表时，为每个字段设置的；
使用 CREATE TYPE 命令创建；

1. 创建语法回顾

CREATE TYPE name AS
( attribute_name data_type [, ... ] )
CREATE TYPE name (
INPUT = input_function,
OUTPUT = output_function
[, RECEIVE = receive_function ]
[, SEND = send_function ]
[, ANALYZE = analyze_function ]
[, INTERNALLENGTH = { internal_length | VARIABLE } ]
[, PASSEDBYVALUE ]
[, ALIGNMENT = alignment ]
[, STORAGE = storage ]
[, DEFAULT = default ]
[, ELEMENT = element ]
[, DELIMITER = delimiter ]
)

2. 数值类型

名字	存储长度	描述	范围
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

3. 货币类型

• money 类型存储带有固定小数精度的货币金额。
• numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。

名字	存储容量	描述	范围
money	8 字节	货币金额	-92233720368547758.08 到 +92233720368547758.07

4. 字符类型

名字	描述
varchar(n) 或 character varying(n)	变长，有长度限制，最大为 10485760（1G）
char(n)或 character(n)	定长，不足补空白，最大为 10485760（1G）
text	变长，无长度限制

• text 和 varchar 几乎无性能差别，区别仅在于存储结构的不同；

• 长度确定的字符串使用 char；

• 长度不定的使用 varchar 或 text；

• 在 PostgreSQL 数据库中，character(n) 通常是这三个中最慢的，因为额外存储成本，故而应选择 text 或 varchar，

• 栗子：

• 创建一个表 character_tests

postgres=# CREATE TABLE character_tests (
postgres(#  id serial PRIMARY KEY,
postgres(#  x CHAR (1),
postgres(#  y VARCHAR (10),
postgres(#        z TEXT
postgres(# );
CREATE TABLE

• 给 character_tests 表中插入一些示例数据（我们定义了 char 的长度，所以过长的字符会报错）

postgres=# INSERT INTO character_tests (x, y, z)
postgres-# VALUES
postgres-# (
postgres(#  'Y',
postgres(# 'This is a test for varchar',
postgres(# 'This is a very long text for the PostgreSQL text column'
postgres(# );
错误:  对于可变字符类型来说，值太长了(10)

• 给 character_tests 表中插入正确的数据（对插入成功返回的数据还不理解，应该表示成功了）

postgres=# INSERT INTO character_tests (x, y, z)
postgres-# VALUES
postgres-# (
postgres(#  'Y',
postgres(#  'varchar(n)',
postgres(# 'This is a very long text for the PostgreSQL text column'
postgres(# );
INSERT 0 1

• 查询 character_tests 表中的数据：

postgres=# SELECT * FROM character_tests
postgres-# ;
 id | x |     y      |                            z                            
----+---+------------+---------------------------------------------------------
  1 | Y | varchar(n) | This is a very long text for the PostgreSQL text column
(1 行记录)

5. 日期/时间类型

AD 表示公元后，2021AD 表示公元 2021 年；
BC 表示公元前，230BC 表示公元前 230 年。
1 毫秒/ 14 位。这个就不太理解，时间戳一共 41 位，所以，可能是 1 毫秒可以走 14 位；

名字	存储空间	描述	最低值	最高值	分辨率
timestamp [ (p) ] [ without time zone ]	8 字节	日期和时间(无时区)	4713 BC	294276 AD	1 毫秒 / 14 位
timestamp [ (p) ] with time zone	8 字节	日期和时间(有时区)	4713 BC	294276 AD	1 毫秒 / 14 位
data	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 位

6. 布尔类型

名称	存储空间	描述
boolean	1 字节	true / false

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

7. 枚举类型

• 枚举类型是包含静态和值的有序集合的数据类型；
• 与其他类型不同的是枚举类型需要使用 CREATE TYPE 命令创建；

这一块回顾之前创建数据类型方式：x CHAR (1)；

• 创建枚举类型的命令：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

• 栗子：
• 创建枚举类型 mood

postgres=# CREATE TYPE mood AS ENUM('sad', 'ok', 'happy');
CREATE TYPE

• 创建表 person

postgres=# CREATE TABLE person(
postgres(#  name text,
postgres(#  current_mood mood
postgres(# );
CREATE TABLE

• 插入数据

postgres=# INSERT INTO person VALUES('Alice', 'ok');
INSERT 0 1

• 查看表中心情为 ok 的人

postgres=# SELECT * FROM person WHERE current_mood = 'ok';
 name  | current_mood 
-------+--------------
 Alice | ok
(1 行记录)

8. 几何类型

• 最基本的几何类型：点（point），是其他类型的基础。

名字	存储空间	说明	表现形式
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 字节	圆	<(x,y),r> (圆心和半径)

9. 网络地址类型

• PostgreSQL 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型。
• 这些类型提供输入错误检查和特殊的操作和功能。

名字	存储空间	描述
cidr	7 或 19 字节	IPv4 或 IPv6 网络
inet	7 或 19 字节	IPv4 或 IPv6 主机和网络
macaddr	6 字节	MAC 地址

• 在对 inet 或 cidr 数据类型进行排序的时候， IPv4 地址总是排在 IPv6 地址前面。

10. 位串类型

• 位串就是一串 1 和 0 的字符串。
• 它们可以用于存储和直观化位掩码。

名字	描述	说明
bit(n)	bit 等效 bit(1)	bit 类型的数据必须准确匹配长度 n，存储短些或者长一些的数据都是错误的
bit varying(n)	bit varying 表示无长度限制	bit varying 类型数据是最长 n 的变长类型，更长的串会被拒绝

11. 文本搜索类型

• 全文检索即通过自然语言文档的集合来找到那些匹配一个查询的检索。

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

12. UUID 类型

• UUID 数据类型用来存储 RFC 4122，ISO/IEF 9834-8:2005 以及相关标准定义的通用唯一标识符；
• 这个标识符是一个由算法产生的 128 位标识符，使它不可能在已知使用相同算法的模块中和其他方式产生的标识符相同。
• 对分布式系统而言，这种标识符比序列能更好的提供唯一性保证，因为序列只能在单一数据库中保证唯一。
• UUID 被写成一个小写十六进制数字的序列，由分字符分成几组：
  • 一组 8 位数字 + 3 组 4 位数字 + 一组 12 位数字；
  • 总共 32 个数字代表 128 位；
  • 一个这种标准的 UUID 例子如下：

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

13. XML 类型

• xml 数据类型可以用于存储 XML 数据。
• 将 XML 数据存到 text 类型中的优势在于它能够为结构良好性来检查输入值， 并且还支持函数对其进行类型安全性检查。
• 要使用这个数据类型，编译时必须使用 configure --with-libxml。

13.1 创建 XML 值

• 使用函数 xmlparse从字符数据产生 xml 类型的值：

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 数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。

实例	结果
array_to_json('{{1,5},{99,100}}'::int[])	[[1,5],[99,100]]
row_to_json(row(1,'foo'))	{“f1”:1,“f2”:“foo”}

15. 数组类型

• PostgreSQL 允许将字段定义成变长的多维数组。
• 数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。

15.1 声明数组

• 创建表的时候，可以声明数组，方式如下：

postgres=# CREATE TABLE sal_emp(
postgres(#  name        text,
postgres(#  pay_by_quarter    integer[],
postgres(#  schedule    text[][]
postgres(# );
CREATE TABLE

• pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。
• 也可以使用 ARRAY 关键字，如下所示：

postgres=# CREATE TABLE sal_emp(
postgres(#  name        text,
postgres(#  pay_by_quarter    integer ARRAY[4],
postgres(#  schedule    text[][]
postgres(# );
CREATE TABLE

15.2 插入值

• 插入值使用花括号 {}，元素在 {} 使用逗号隔开：

postgres=# INSERT INTO sal_emp VALUES
(                         
'Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}'
);
INSERT 0 1

postgres=# INSERT INTO sal_emp VALUES
postgres-# (
postgres(# 'Carol',
postgres(# '{20000, 25000, 25000, 25000}',
postgres(# '{{"breakfast", "consulting"}, {"meeting", "lunch"}}'
postgres(# );
INSERT 0 1

15.3 访问数组

• 查询检索在第二季度薪水变化的雇员名：

postgres=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
 name  
-------
 Carol
(1 行记录)

15.4 修改数组

postgres=# UPDATE sal_emp SET pay_by_quarter = '{19999, 39999, 39999, 39999}' WHERE name = 'Carol'；

• 也可以使用 ARRAY 构造器语法：

postgres=# UPDATE sal_emp SET pay_by_quarter = ARRAY[19999, 39999, 39999, 39999] WHERE name = 'Carol'；

15.5 数组中检索

• 要搜索一个数组中的数值，必须检查该数组的每一个值。

postgres=# SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 20000 OR
postgres-# pay_by_quarter[2] = 20000 OR
postgres-# pay_by_quarter[3] = 20000 OR
postgres-# pay_by_quarter[4] = 20000; 
 name  |      pay_by_quarter       |                 schedule                 
-------+---------------------------+------------------------------------------
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(1 行记录)

• 也可以用下面的语句找出数组中所有元素值都等于 1000 的行：

postgres=# SELECT * FROM sal_emp WHERE 1000 = ALL (pay_by_quarter);
 name |     pay_by_quarter        |                 schedule               
------+---------------------------+------------------------------------------
 Bill | {10000,10000,10000,10000} | {{breakfast,consulting},{meeting,lunch}}
(1 行记录)

16. 复合类型

• 复合类型表示一行或者一条记录的结构；
• 它实际上只是一个字段名和它们的数据类型的列表。
• PostgreSQL 允许像简单数据类型那样使用复合类型。
• 比如，一个表的某个字段可以声明为一个复合类型。

16.1 声明复合类型

postgres=# CREATE TYPE inventory_item AS (
postgres(#     name            text,
postgres(#     supplier_id     integer,
postgres(#     price           numeric
postgres(# );
CREATE TYPE

• 用 inventory_item 类型创建表：

postgres=# CREATE TABLE on_hand(
postgres(#  item      inventory_item,
postgres(#  count     integer
postgres(# );
CREATE TABLE

• 插入数据：

postgres=# INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
INSERT 0 1

• 复合类型值输入：
• 要以文本常量书写复合类型值，在圆括弧里包围字段值并且用逗号分隔他们。
• 可以在任何字段值周围放上双引号，如果值本身包含逗号或者圆括弧， 必须用双引号括起。

16.2 访问复合类型

• 要访问复合类型字段的一个域，需要一个点以及域的名字， 非常类似从一个表名字里选出一个字段
• 格式：.name
• 实际上，因为实在太像从表名字中选取字段， 所以经常需要用圆括弧来避免分析器混淆。
• 比如，你可能需要从on_hand 例子表中选取一些子域，像下面这样：

postgres=# SELECT item.name FROM on_hand WHERE item.price > 9.99;
错误:  对于表"item",丢失FROM子句项
第1行SELECT item.name FROM on_hand WHERE item.price > 9.99;
            ^

• 这样将不能工作，因为根据 SQL 语法，item 是从一个表名字选取的， 而不是一个字段名字。你必须像下面这样写：

postgres=# SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
 name 
------
(0 行记录)

• 如果你也需要使用表名字(比如，在一个多表查询里)，那么这么写：

postgres=# SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
 name 
------
(0 行记录)

• 现在圆括弧对象正确地解析为一个指向item字段的引用，然后就可以从中选取子域。

17. 范围类型

• 范围数据类型代表着某一元素类型在一定范围内的值。
• PostgreSQL 内置的范围类型有：
  • int4range — integer 的范围；
  • int8range —bigint的范围；
  • numrange —numeric的范围；
  • tsrange —timestamp without time zone的范围；
  • tstzrange —timestamp with time zone的范围；
  • daterange —date的范围；

18. 对象标识符类型

• PostgreSQL 在内部使用对象标识符(OID)作为各种系统表的主键。
• 同时，系统不会给用户创建的表增加一个 OID 系统字段(除非在建表时声明了WITH OIDS 或者配置参数default_with_oids设置为开启)。
• oid 类型代表一个对象标识符。
• 除此以外 oid 还有几个别名：regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig 和regdictionary。

名字	引用	描述	数值栗子
oid	任意	数字化的对象标识符	564182
regproc	pg_proc	函数名字	sum
regprocedure	pg_proc	带参数类型的函数	sum(int4)
regoper	pg_operator	操作符名	+
regoperator	pg_operator	带参数类型的操作符	*(integer,integer) 或 -(NONE,integer)
regclass	pg_class	关系名	pg_type
regtype	pg_type	数据类型名	integer
regconfig	pg_ts_config	文本搜索配置	english
regdictionary	pg_ts_dict	文本搜索字典	simple

19. 伪类型

• PostgreSQL 类型系统包含一系列特殊用途的条目， 它们按照类别来说叫做伪类型。
• 伪类型不能作为字段的数据类型， 但是它可以用于声明一个函数的参数或者结果类型。
• 伪类型在一个函数不只是简单地接受并返回某种 SQL 数据类型的情况下很有用。

名字	描述
any	表示一个函数接受任何输入数据类型。
anyelement	表示一个函数接受任何数据类型。
anyarray	表示一个函数接受任意数组数据类型。
anynonarray	表示一个函数接受任意非数组数据类型。
anyenum	表示一个函数接受任意枚举数据类型。
anyrange	表示一个函数接受任意范围数据类型。
cstring	表示一个函数接受或者返回一个空结尾的 C 字符串。
internal	表示一个函数接受或者返回一种服务器内部的数据类型。
language_handler	一个过程语言调用处理器声明为返回 language_handler。
fdw_handler	一个外部数据封装器声明为返回 fdw_handler。
record	标识一个函数返回一个未声明的行类型。
trigger	一个触发器函数声明为返回 trigger。
void	表示一个函数不返回数值。
opaque	一个已经过时的类型，以前用于所有上面这些用途。