---

🗻 简介:

往期精选文章，精彩不容错过，求赞求关注！

🤣一文带你玩转Kubernetes🤣

Proxy 代理的源码和原理解析

Spring + LDAP + JPA 实现事务管理器

容器服务Istio实践 Istio Sidecar 注入方式

Java EasyPoi之复杂多表头多sheet页excel模板导出实例

[整理归纳系列]还搞不懂PG的数据类型？看这篇就够了🥳

🍻🍺🍸🛌

🍻简介

PostgreSQL 提供了丰富的数据类型，包括 数字类型、货币类型、字符类型、二进制数据类型、时间/日期类型、布尔类型、枚举类型、JSON 类型、几何类型、网络地址类型、bite类型、文本搜索类型、UUID 类型、XML 类型、数组类型、集合类型、区间类型、位字符串类型等等。除此之外，PostgreSQL 还提供给我们DIY的乐趣，可以自定义自己想要的数据类型。

看到PG数据库存在这么多数据类型，你的脑中是不是也像下图一样，充满了大大的问号呢？

🍻PostgreSQL 常用数据类型

看下表就够了

数据类型	别名/简称	说明
bigint	int8	带符号八字节整数
bigserial	serial8	自动递增八字节整数
bit [ (n) ]		定长位字符串
bit varying [ (n) ]	varbit	可变长度位字符串
boolean	bool	布尔值 (真/假)
box		矩阵
bytea		二进制数据 (“字节数组”)
character varying [ (n) ]	varchar [ (n) ]	可变长度字符串
character [ (n) ]	char [ (n) ]	固定长度字符串
cidr		IPv4或IPv6网络地址
circle		图
date		日历日期 (年、月、日)
double precision	float8	双精度浮点数 (8字节)
inet		IPv4或IPv6主机地址
integer	int, int4	带符号四字节整数
interval [ fields ] [ (p) ]		时间跨度
line		线
lseg		线段
macaddr		媒体访问控制地址MAC
money		货币金额
numeric [ (p, s) ]	decimal [ (p, s) ]	可选精度的精确数字
path		平面上的几何路径
point		平面上的几何点
polygon		平面上的封闭几何路径
real	float4	单精度浮点数 (4字节)
smallint	int2	带符号两字节整数
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		uuid
xml		xml

🍻PostgreSQL 数据类型详解

🍺PostgreSQL 数字类型

思维导图

表格整理：

数据类型	存储大小	描述	范围
smallint	2 bytes	小范围整数	-32768 到 +32767
integer	4 bytes	典型的整数选择	-2147483648 到+2147483647
bigint	8 bytes	大范围整数	-9223372036854775808 到9223372036854775807
decimal	variable	用户指定的精度，精确	小数点前最多131072位数字；小数点后最多16383位数字
numeric	variable	用户指定的精度，精确	小数点前最多131072位数字；小数点后最多16383位数字
real	4 bytes	可变精度，非精确	小数点后6位数的精度
double precision	8 bytes	可变精度，不精确	15位小数的精度
serial	4 bytes	自动递增的整数	1 到2147483647
bigserial	8 bytes	大型自动递增整数	1 到9223372036854775807

三种常用的整数数据类型：SMALLINT，INTEGER，和 BIGINT。

🍸语法

定义语法

column_name {SMALLINT | INTEGER | BIGINT} column_constrait

❗注意， INT 是 INTEGER 的同义词，可以使用 INT 代替 INTEGER。

定义主键列，语法如下：

column_name {SMALLINT | INTEGER | BIGINT} GENERATED ALWAYS AS IDENTITY PRIMARY KEY

定义标识列，语法如下：

column_name {SMALLINT | INTEGER | BIGINT} GENERATED ALWAYS AS IDENTITY

定义SERIAL列，语法如下：

column_name SERIAL PRIMARY KEY

❗注意， SMALLSERIAL 的内部类型为 SMALLINT，SERIAL 的内部类型为 INTEGER，BIGSERIAL 的内部类型为 BIGINT。

🍺PostgreSQL 货币类型

数据类型	存储大小	描述	范围
money	8 bytes	货币金额	-92233720368547758.08 到+92233720368547758.07

numeric、int和bigint数据类型的值可以转换为money。从 实数和双精度数据类型的转换，可以通过先转换为数字来完成，例如：

SELECT '12.34'::float8::numeric::money;

但是，通常情况下我们不建议这样做。 因为可能会出现转换错误，不应使用浮点数来处理货币。

所以我们可以先将货币值转换为数字，不损失精度。 转换为其他类型可能会丢失精度，必须分两个阶段完成：

SELECT '123.69'::money::numeric::float8;

🍺PostgreSQL 字符类型

思维导图

整理表格如下：

数据类型	说明
character varying(n), varchar(n)	有限制的可变长度
character(n), char(n)	固定长度
text	可变的无限长度

🍸语法

建表语句示例：

CREATE TABLE test_character_type (
  varchar_10 VARCHAR(10),
  char_1 CHAR(1),
  txt TEXT
);

❗注意：在 PostgreSQL 中，三种字符类型之间没有太大的性能差异，按需使用即可

🍺PostgreSQL 二进制数据类型

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

说明.二进制字符串是八位字节（或字节）的序列。二进制字符串与字符串有两种区别。首先，二进制字符串特别允许存储值为零的八位位组和其他“不可打印”八位位组（通常是 32 到 126 范围之外的八位位组）。字符串不允许零八位字节，也不允许任何其他八位字节值和八位字节值序列根据数据库选择的字符集编码无效。其次，对二进制字符串的操作处理实际字节，而字符串的处理取决于区域设置。简而言之，二进制字符串适用于存储程序员认为是“原始字节”的数据，而字符串适用于存储文本。

🍺PostgreSQL 日期、时间数据类型

思维导图

表格整理：

数据类型	存储大小	描述	最小值	最大值	最小单位
timestamp [ (p) ] [ without time zone ]	8 bytes	日期和时间 (无时区)	4713 BC	294276 AD	1微秒/14位数
timestamp [ (p) ] with time zone	8 bytes	日期和时间，带时区	4713 BC	294276 AD	1微秒/14位数
date	4 bytes	日期 (无时间)	4713 BC	5874897 AD	1天
time [ (p) ] [ without time zone ]	8 bytes	一天中的时间 (无日期)	00:00:00	24:00:00	1微秒/14位数
time [ (p) ] with time zone	12 bytes	仅限一天中的时间，带时区	00:00:00+1459	24:00:00-1459	1微秒/14位数
interval [ fields ] [ (p) ]	16 bytes	时间间隔	-178000000 years	178000000 years	1微秒/14位数

time、timestamp和interval接受一个可选的精度值 p ，它指定秒字段中保留的小数位数。默认情况下，对精度没有明确的限制。对于timestamp和interval类型， p 的允许范围是 0 到 6 。

注意！ 当时间戳值存储为 8 字节整数（当前为默认值）时，微秒精度可用于整个值范围。当时间戳值存储为双精度浮点数时（不推荐使用的编译时选项），精度的有效限制可能小于 6。

🍺PostgreSQL 布尔类型

数据类型	存储大小	描述
boolean	1 byte	true 或false

❗注意：在PostgreSQL 中，

• true, 'true', 't', 'yes', 'y', '1' 都被视为真
• false, 'false', 'f', 'no', 'n', '0' 都被视为假

注意，以上所有列出的值均不区分大小写。除了 true 和 false，其他的都是字符类型。

❗注意：PostgreSQL 布尔类型允许 NULL 值。

❗注意：请注意，在 SQL 标准中，一个布尔值只接受 TRUE， FALSE，和 NULL。

🍺PostgreSQL 枚举类型

枚举（enum）类型是包含一组静态、有序值的数据类型。它们等效于许多编程语言中支持的枚举类型。枚举类型的一个示例可能是星期几，或者是一组数据的状态值。

声明

以下示例展示枚举的用法：

CREATE TYPE mood AS ENUM ('369', 'Kanavi', 'Yaogao');

创建后，枚举类型可以像任何其他类型一样在表和函数定义中使用

CREATE TYPE pro AS ENUM ('369', 'Kanavi', 'Yaogao');

CREATE TABLE user (
    name text,
    current_pro pro
);

INSERT INTO user VALUES ('上单', '369');

SELECT * FROM user WHERE current_pro = '369';
 name | current_pro 
------+--------------
 上单  | 369
(1 row)

🍺PostgreSQL 几何类型

思维导图

整理表格

数据类型	存储大小	表示	描述
point	16 bytes	平面上的点	(x,y)
line	32 bytes	无限线（未完全实现）	((x1,y1),(x2,y2))
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> (中心点和半径)

🍺PostgreSQL 网络地址类型

数据类型	存储大小	描述
cidr	7 or 19 bytes	IPv4和IPv6网络
inet	7 or 19 bytes	IPv4和IPv6主机和网络
macaddr	6 bytes	MAC地址

inet和cidr数据类型两者之间的本质区别是 inet接受网络掩码右侧的非零位值，而 cidr 则不。

提示: 如果不喜欢inet或cidr值的输出格式，请尝试使用函数host、text和abbrev。

🍺PostgreSQL bite类型

bite类型是 1 和 0 的字符串。它们可用于存储或可视化位掩码。有两种 SQL 位类型：bit(n) 和 bit varying(n)，其中n是一个正整数。

位类型数据必须与长度 n完全匹配；尝试存储较短或较长的位串是错误的。位变化数据的长度可变，直到最大长度n；较长的字符串将被拒绝。写入没有长度的位相当于bit(1)，而 没有长度规范的位变化意味着无限长度。

🍸示例

下面是使用bite类型的示例

CREATE TABLE test (a BIT(3), b BIT VARYING(5));
INSERT INTO test VALUES (B'101', B'00');
INSERT INTO test VALUES (B'10', B'101');
ERROR:  bit string length 2 does not match type bit(3)

INSERT INTO test VALUES (B'10'::bit(3), B'101');
SELECT * FROM test;
  a  |  b
-----+-----
 101 | 00
 100 | 101

🍺PostgreSQL 文本搜索类型

PostgreSQL提供了两种数据类型支持全文搜索，即在自然语言 文档集合中进行搜索以找到与查询最匹配的那些活动。

tsvector类型表示为文本搜索优化的形式的文档；

tsquery类型同样代表一个文本查询。

🍸tsvector示例

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
                      tsvector
----------------------------------------------------
 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'

🍸tsquery示例

SELECT 'fat & rat'::tsquery;
    tsquery    
---------------
 'fat' & 'rat'
 

SELECT 'fat & (rat | cat)'::tsquery;
          tsquery          
---------------------------
 'fat' & ( 'rat' | 'cat' )
 

SELECT 'fat & rat & ! cat'::tsquery;
        tsquery         
------------------------
 'fat' & 'rat' & !'cat'

🍺PostgreSQL UUID类型

数据类型uuid存储了RFC 4122、ISO/IEC 9834-8:2005和相关标准所定义的通用唯一标识符（UUID）。(有些系统将这种数据类型称为全局唯一标识符，或GUID。

这个标识符是一个128位的数量，它是由一种算法产生的，选择这种算法是为了使其他人使用相同的算法产生相同的标识符的可能性非常小。

UUID被写成小写的十六进制数字序列，分几组由连字符隔开，具体来说就是一组8位数字，后面是三组4位数字，再后面是一组12位数字，总共32位数字代表128位。这种标准形式的UUID的一个例子是：

q0eebc99-9c2b-4efg-bb6d-62b9bd3s0a11

🍺PostgreSQL XML 类型

🍸语法

以下说明创建xml的语法

XMLPARSE ( { DOCUMENT | CONTENT } value)

你也可以用以下方式声明XML

XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

🍸示例：

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

🍸编码处理

在客户端、服务器和通过它们传递的 XML 数据中处理多个字符编码时必须小心。

当使用文本模式向服务器传递查询并将查询结果传递给客户端时（这是普通模式），PostgreSQL 将客户端和服务器之间传递的所有字符数据转换为各自端的字符编码；这包括 XML 值的字符串表示形式，例如上面的示例。

这通常意味着包含在 XML 数据中的编码声明可能会因为字符数据在客户端和服务器之间传输时被转换为其他编码而变得无效，因为嵌入的编码声明不会更改。

为了应对这种错误，包含在输入到xml类型的字符串中的编码声明将 被忽略，并且内容假定为当前服务器编码。因此，为了正确处理，必须从客户端以当前客户端编码发送 XML 数据的字符串。客户端有责任在将文档发送到服务器之前将其转换为当前的客户端编码，或者适当地调整客户端编码。在输出时， xml类型的值将没有编码声明，并且客户端应该假定所有数据都在当前客户端编码中。

由于 XML 数据在内部以 UTF-8 进行处理，因此如果服务器编码也是 UTF-8，则计算效率最高。

🍺PostgreSQL ARRAY 数据类型

🍸PostgreSQL 数组用法

和其他很多语言一样，在 PostgreSQL 中，使用 data_type[] 表示一个数组类型，其中 data_type 是数组中元素的数据类型。比如， TEXT 的数组类型是 TEXT[]， INTEGER 的数组类型是 INTEGER[]， BOOLEAN 的数组类型是 BOOLEAN[]，等等。

🍸构造 PostgreSQL 数组

要表示一个数组数据，我们可以使用以下两种方法：

• 使用 ARRAY() 构造函数构造一个数组：

ARRAY(elem1, elem2, ...)

比如， ARRAY(1, 2, 3) 是一个整数数组。

• 使用花括号构造一个 PostgreSQL 数组：

'{elem1, elem2, ...}'::data_type[]

比如： '{1, 2, 3}'::integer[] 是一个整数数组。

🍸访问 PostgreSQL 数组元素

要访问 PostgreSQL 数组中的元素，我们可以使用数组下标。默认情况下，PostgreSQL 对数组元素使用从 1 开始的编号。这意味着第一个数组元素的下标是 1， 第二个数组元素的下表是 2，以此类推。

🍸定义 PostgreSQL 数组列

要在 PostgreSQL 数据库中定义一个数组列，请使用如下语法：

column_name data_type[] column_constraint

🍸PostgreSQL 数组实例

为演示 PostgreSQL 数组类型的用法，请使用以下 CREATE TABLE 语句创建 user_follow 表，其中 follows 列定义为文本数组。

CREATE TABLE user_follows  (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(50),
  follows  TEXT[]
);

其中， follows 列是一个一维数组，其中包含用户的关注列表。

以下语句使用 ARRAY() 构造函数构造了一个文本数组，并向 user_follow 表中插入一条数据：

INSERT INTO user_follows  (name, follows)
VALUES ('JG', ARRAY['369', 'Kanavi', 'Yagao', 'Hope', 'Missing'])
RETURNING *;

 id | name |           follows
----+------+---------------------------------
  1 |  JG  | {369,Kanavi,Yagao,Hope,Missing}
(1 row)

以下语句使用花括号构造了一个文本数组，并向 user_follow 表中插入一条数据：

INSERT INTO user_follows  (name, follows)
VALUES ('TOP', ARRAY['369'])
RETURNING *;

 id | name |       follows
----+------+----------------------
  2 | TOP  |       {369}
(1 row)

我们可以使用 SELECT 语句查询数组数据，如下所示：

SELECT
    name,
    follows
FROM
    user_follows;

 id | name |           follows
----+------+---------------------------------
  1 |  JG  | {369,Kanavi,Yagao,Hope,Missing}
  2 |  Top |             {369}
(2 rows)

我们可以使用下标访问数组元素。比如，要获取用户的第一个follows，请使用如下的语句：

SELECT
  name,
  follows[1]
FROM
  user_follows;

 name |       follows
------+----------------------
  JG  |       {369}
 TOP  |       {369}
(2 rows)

要从 PostgreSQL 数组中搜索是否包含某个指定的元素，请使用 ANY() 运算符。比如，要查找那些业余爱好为足球的同学，请使用以下查询：

SELECT
  name,
  follows
FROM
  user_follows
WHERE
  'Hope' = ANY (follows);

 name |           follows
------+---------------------------------
  JG  | {369,Kanavi,Yagao,Hope,Missing}
(1 row)

PostgreSQL 允许使用数组下标修改数组中的每个元素，比如：

UPDATE user_follows
SET follows[1] = 'Zoom'
WHERE ID = 1
RETURNING *;

 name |            follows
------+---------------------------------
  JG  | {Zoom,Kanavi,Yagao,Hope,Missing}
(1 row)

我们可以更新整个数组，比如：

UPDATE user_follows
SET follows = '{"Zoom"}'
WHERE ID = 1
RETURNING *;

 id | name |  follows
----+------+------------
  1 |  JG  |  {Zoom}
(1 row)

🍸PostgreSQL 数组函数

PostgreSQL 提供了很多数组相关的函数。下面是几个常用的 PostgreSQL 数组函数：

• array_append(): 将指定的元素追加到指定的数组。

• array_cat(): 将两个指定的数组合并为一个数组。

• array_length(): 返回指定的数组中指定维度的长度。

• array_prepend(): 将指定的元素添加到指定的数组的开头。

• array_replace(): 在指定的数组中使用一个新元素替换指定的元素。

• array_to_string(): 将数组中的所有元素使用分隔符连接起来。

• unnest(): 将指定的数组展开为一个行的集合。

例如：要将 follows 数组中的每一个业余爱好扩展为一行，请使用如下语句：

SELECT
  name,
  unnest(follows)
FROM
  user_follows;

 name |  unnest
------+----------
TOP	  |  369
JG	  |  Zoom
JGD	  |  369
JGD	  |  Kanavi
JGD	  |  Yagao
JGD	  |  Hope
JGD	  |  Missing
(7 rows)

🍺PostgreSQL 复合类型

下面是定义复合类型的两个简单示例：

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

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

语法类似于CREATE TABLE，除了只能指定字段名称和类型；目前不能包含任何约束（例如NOT NULL ）。请注意，AS关键字是必不可少的；没有它，系统会认为是另一种CREATE TYPE命令，你会得到奇怪的语法错误。

定义完类型后，我们可以使用它们来创建表：

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

或者创建方法

CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS 'SELECT $1.price * $2' LANGUAGE SQL;

SELECT price_extension(item, 10) FROM on_hand;

🍺PostgreSQL 对象标识符类型

数据类型	参考	描述	示例
oid	any	数字对象标识符	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

🍺PostgreSQL 伪类型

PostgreSQL类型系统包含许多特殊用途的条目，统称为伪类型。伪类型不能用作列数据类型，但可以用于声明函数的参数或结果类型。在函数的行为不对应于简单地获取或返回特定SQL数据类型的值的情况下，每种可用的伪类型都很有用。

以下是伪类型的整理表格：

类型	描述
any	指示函数接受任何输入数据类型。
anyarray	指示函数接受任何数组数据类型
anyelement	指示函数接受任何数据类型
anyenum	指示函数接受任何枚举数据类型
anynonarray	指示函数接受任何非数组数据类型
cstring	指示函数接受或返回以null终止的C字符串。
internal	指示函数接受或返回服务器内部数据类型。
language_handler	过程语言调用处理程序声明为returnlanguage_handler
fdw_handler	外部数据包装处理程序声明为returnfdw_handler
record	标识返回未指定行类型的函数
trigger	触发器 函数声明
void	指示函数不返回值。
opaque	以前用于上述所有目的的过时类型名称

🍻总结

PostgreSQL比MySQL新增了更多的数据类型，丰富了类型库，可以在不同的场景下选用不同的类型，但是也要注意数据库与Java格式的匹配问题，PostgreSQL的日期类型虽然更丰富，但是在转换为Java类型时可能会出现格式不匹配问题，所以在开发时要注意。

未完待续...既然看到这里了，不妨来个大大的点赞叭