PostgreSQL数据类型

PostgreSQL 作为功能强大的关系型数据库，不仅支持标准 SQL 类型，还扩展了丰富的数据类型体系，满足开发者在数值计算、文本处理、时间计算、结构化数据等多方面的需求。

相较于传统的MySQL支持的类型更加丰富且强大，主要可以分为以下几种：

数据类型	说明	MySQL对比
数值类型	整数、浮点、小数、高精度计算	无较大差别✅
字符类型	定长、变长字符串（上限1G），文本	MySQL支持✅
二进制类型	存储非文本数据（如文件、图像）	MySQL支持（blob）✅
日期/时间类型	支持时区、间隔计算	无较大差别✅
布尔类型	true/false/null	MySQL中不支持~❗
枚举类型	自定义常量集合	MySQL支持✅
几何类型	点，直线，线段，圆 …	MySQL支持GIS类型✅
位图/串类型	定长位图、可变长度位图	MySQL支持✅
网络地址类型	IPv4、IPv6、MAC 地址等	MySQL不支持~❗
JSON 类型	json 与 jsonb，支持结构化文档存储	MySQL8.X支持✅
数组类型	支持多维数组	MySQL不支持~❗
范围类型	范围表示如 [1, 10)	MySQL也不支持~❗
UUID 类型	适合分布式唯一标识	MySQL也不支持~❗
XML 类型	XML 结构支持	MySQL也不支持~❗
组合类型/自定义类型	用户可以定义自己的数据结构	MySQL也不支持~❗
特殊/其他类型	money、tsvector等	MySQL也不支持~❗

中文官网：http://www.postgres.cn/docs/current/datatype.html

---

1 三种类型转换方式

第一种方式：只需要在值的前面，添加上具体的数据类型即可

-- 将字符串转成位图类型
select bit '010101010101001';

第二种方式：也可以在具体值的后面，添加上::类型 ，来指定

-- 数据类型
select '2011-11-11'::date;
select '101010101001'::bit(20);
select '13'::int;

第三种方式：使用CAST函数

-- 类型转换的完整写法
select CAST(varchar '100' as int);

---

2 数值类型

在PostgreSQL中数值类型主要可以分为三类：整型、浮点型和序列，如下

类型	Alias	占用空间	描述	范围
smallint	int2	2 字节	小范围整数	-32,768 到 +32,767
integer	int4	4 字节	整数的典型选择	-2,147,483,648 到 +2,147,483,647
bigint	int8	8 字节	大范围整数	-9,223,372,036,854,775,808 到 +9,223,372,036,854,775,807
numeric	decimal	可变	用户指定精度，精确数值	小数点前最多 131072 位，小数点后最多 16383 位
real	float4	4 字节	单精度浮点，非精确	约 ±1E-37 到 ±1E+37，精度约 6 位十进制数
double precision	float8	8 字节	双精度浮点，非精确	约 ±1E-307 到 ±1E+308，精度约 15 位十进制数
smallserial	serial2	2 字节	自动增长的小整数（序列）	1 到 32,767
serial	serial4	4 字节	自动增长的整数（序列）	1 到 2,147,483,647
bigserial	serial8	8 字节	自动增长的大整数（序列）	1 到 9,223,372,036,854,775,807

1_整数与浮点类型

整型、浮点型没什么好说的，与MySQL相同用法也没什么区别。

不过PostgreSQL的整形类型并没有无符号（UNSIGNED）这一概念，这点与MySQL略有不同。

-- 整数试图存储超出范围以外的值将导致一个错误overflow
select '12':: smallint; -- 12
select '12':: int2; -- 12
select '2147483647'::integer; -- 2147483647
select 2147483647::int4; -- 2147483647
-- 精度5,标度2 数字总共5位,2位小数 
select 620.1234::decimal(5,2); -- 620.12，小数位被截取，整数位不能超过3(5-2)否则将报错
-- 精度必须是正数，而标度可以是正数或负数(postgre15开始支持)
select 620.1234::decimal(2,-1); -- 620 等价于(3,0)
select 620.1234::numeric; -- 620.1234
-- 不精确类型不支持指定精度和标度，这与mysql中不同
select 620.1234::double precision; -- 620.1234
select 620.1234::float8; -- 620.1234
select 620.1234::float4; -- 620.1234
-- 另外浮点类型还有三个特殊值 Infinity、-Infinity、NaN 分别表示无穷大，无穷小、和不是一个数字
select 'Infinity'::float4;  -- Infinity
select '-Infinity'::float4; -- -Infinity
select 'NaN'::float4; 		-- NaN
-- 有些Infinity计算不符合数学期望将出现 NAN
select 'Infinity'::float4 + '-Infinity'::float4; -- NaN
select 'inf'::float4 + 'inf'::float4; -- Infinity ,inf和-inf是缩写
select 'Infinity'::float4 + 'NaN'::float4; -- NaN
select 'Infinity'::float4 - 'inf'::float4; -- NaN
select '-Infinity'::float4 - 'inf'::float4; -- -Infinity
select '-Infinity'::float4 + '-inf'::float4; -- -Infinity
-- 指定了精度的精确类型不能使用Infinity\-Infinity,因为它名义上超过了任何有限精度限制
select 'Infinity'::decimal; -- Infinity
select 'Infinity'::decimal(5,1); -- ERROR overflow

2_序列

序列类型不是真正的类型，它们只是为了创建唯一标识符列而存在的方便符号。

类似其它一些数据库中支持的AUTO_INCREMENT属性，MySQL中没有序列的对象、而PostgreSQL中没有auto_increment。

序列的正常构建及使用方式：

-- 创建序列 默认 bigint类型 as bigint 可以省略
create sequence table_id_seq as bigint;
-- 查询下一个值
select nextval('table_id_seq');
-- 查询当前值
select currval('table_id_seq');
-- 创建一张表使用该序列
create table test(
	id int8 not null PRIMARY KEY default nextval('table_id_seq'),
	name varchar(16)
);
-- 设置序列的归属关系: 当你 DROP TABLE 或 DROP COLUMN 时，序列也会被一并清除，保证资源自动管理。
alter sequence table_id_seq owned by test.id;
-- 可以插入一条数据后查看效果 
insert into test(name) values ('shenyang')
-- 主键自增
select currval('table_id_seq');
-- 上面这种写法没有问题，但是很不爽~很麻烦，直接使用序列类型
create table test(
	id serial8 not null PRIMARY KEY,
	name varchar(16)
);
-- 默认会创建一个 表名_列名_seq 的序列

因为 smallserial 、serial 和 bigserial 是用序列实现的，所以即使没有删除过行，在出现在列中的序列值可能有“空洞”或者间隙。

如果一个从序列中分配的值被用在一行中，即使该行最终没有被成功地插入到表中，该值也被“用掉”了（比如，当插入事务回滚时）。

为一个serial列创建的序列会在所属的列被删除的时候自动删除。

如果在不删除列的情况下删除序列，那么该列的默认值表达式会被强制删除。

-- 由于存在依赖关系,所以需要级联删除,普通删除将提示错误
DROP SEQUENCE test_id_seq CASCADE;

3_数值的常见操作

PostgreSQL不像MySQL那样，仅支持加、减、乘、除、取余这些基础的运算操作，而其他高级运算主要依靠函数来完成。

PostgreSQL的操作符非常强大，如下

操作符	描述	示例	结果
^	幂	2 ^ 3	8
|/	平方根	|/ 36	6
@	绝对值	@ -5	5
&	与	31 & 16	16
|	或	31|32	63
<<	左移	1<<1	2
>>	右移	16>>1	8

当然，PostgreSQL 也支持许多函数，所以此处了解即可，毕竟还是使用函数更加规范一些。

---

3 字符串类型

字符串类型主要有以下三种，使用起来基本与MySQL没有差别

名字	别名（Alias）	描述
character varying(n)	varchar(n)	有限制的变长
character(n)	char(n)	定长，空格填充
text	无	无限变长

char 和 varchar 最大可以存储 1G 的数据，这要比MySQL大很多。

这三种类型之间没有性能差别，只不过是在使用填充空白的类型的时候需要更多存储尺寸，以及在存储到一个有长度约束的列时需要少量额外CPU周期来检查长度。

虽然在某些其它的数据库系统里，character(n)有一定的性能优势，但在PostgreSQL里没有（TOAST 存储策略）。

事实上，character(n)通常是这三种类型之中最慢的一个，因为它需要额外的存储开销。

因此，在大多数情况下，应该使用text或者character varying。

-- 注意列的类型,默认是text
select 'abc';
-- 除非强转 character varying
select 'abc'::varchar;
--字符串的拼接一要要使用||来拼接
select 'abc' || 'efd'; -- abcefd
--其他的函数，可以查看  http://www.postgres.cn/docs/current/functions-string.html
--mysq中的函数类型一般都支持

---

4 日期类型

PostgreSQL支持SQL中所有的日期和时间类型，如下表所示：

名称	存储尺寸	描述	最小值	最大值	精度 / 解析度
timestamp[(p)] [without time zone]	8 字节	日期和时间，无时区	4713 BC	294276 AD	1 微秒
timestamp[(p)] with time zone	8 字节	日期和时间，带时区（也称为 timestamptz）	4713 BC	294276 AD	1 微秒
date	4 字节	仅日期，无时间	4713 BC	5874897 AD	1 天
time[(p)] [without time zone]	8 字节	一天中的时间，无日期	00:00:00	24:00:00	1 微秒
time[(p)] with time zone	12 字节	时间 + 时区，无日期	00:00:00+1459	24:00:00−1459	1 微秒
interval [fields] [(p)]	16 字节	时间间隔，如“1年2月”	-178,000,000 年	+178,000,000 年	1 微秒

PostgreSQL符合SQL规范的要求，只写timestamp等效于timestamp without time zone（没有时区，其他时间类型也一样）。

time、timestamp 和 interval 接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。

缺省情况下，在精度上没有明确的边界，p允许的范围是从 0 到 6。

-- epoch(1970~)、infinity、-infinity、now、today、tomorrow、yesterday、allballs都是特殊值
-- 可以使用now或作为当前系统时间（没有时区的概念）
select 'now'::timestamp; -- 合法类型date、time、timestamp
-- 获取当前时间包含时区, timestamptz 为 timestamp with time zone 简写
select 'now'::timestamptz; select 'now'::timestamp with time zone;
-- 也可以使用current_timestamp函数获取（推荐，默认东八区）
select current_timestamp; --带有时区
-- 还有其他常见的函数 CURRENT_DATE、CURRENT_TIME、LOCALTIME、 LOCALTIMESTAMP
-- 日期支持的风格如下 ISO、SQL、Postgres、German
select '1997-12-17 07:37:16-08'::timestamp; --ISO
select '12/17/1997 07:37:16.00 PST'::timestamp; --SQL
select 'Wed Dec 17 07:37:16 1997 PST'::timestamp; --Postgres原始风格
--日期计算(可以进行加减操作) date + interval → timestamp
select date '2001-09-28' + interval '1 hour'; -- 2001-09-28 01:00:00
-- date + time → timestamp
select date '2011-11-11' + time '12:12:12'; --2011-11-11 12:12:12
-- 可以针对timestamp使用interval的方式进行 +，-操作，在查询以时间范围为条件的内容时，可以使用 
select timestamp '2011-11-11 12:12:12' + interval '1day' + interval '1minute' + interval '1month';
--2011-12-12 12:13:12
-- date - date → integer
select date '2001-10-01' - date '2001-09-28'; -- 3
-- 比较时间
select current_timestamp > now() - interval '7 days'; -- true

使用建议：用 timestamptz，统一处理时区问题，比较和计算时，interval 很有用。

---

5 布尔类型

布尔类型（Boolean）非常简单，只有三个值：true，false，null 。

下面是他们的计算规则，主要是进行 and 和 or 的计算。

字段A	字段B	a and b	a or b
true	true	true	true
true	false	false	true
true	NULL	NULL	true
false	false	false	false
false	NULL	false	NULL
NULL	NULL	NULL	NULL

可以看到这里面有几种情况非常特殊

select true and NULL; -- NULL
select true or NULL; -- true
select false and NULL; -- NULL
select false or NULL; -- true

---

6 枚举类型

枚举（enum）类型是由一个静态、值的有序集合构成的数据类型。

-- 声明一个星期的枚举，值自然只有周一~周日。
create type week as enum ('Mon','Tues','Sun');
-- 如果测试表以及存在则清除
DROP TABLE IF EXISTS test;
-- 声明一张表，表中的某个字段的类型是上面声明的枚举。
create table test(
    id bigserial,
    weekday week
);
-- 插入数据
insert into test(weekday) values ('Mon'); -- successfully 
insert into test(weekday) values ('Fri'); -- ERROR 没有这种类型
-- 枚举类型中值的排序是创建类型时列出值的顺序
SELECT * FROM test WHERE weekday >= 'Mon' ORDER BY weekday; -- 1，Mon
-- 所有标准比较运算符和相关的聚合函数都支持枚举类型
SELECT * FROM test WHERE weekday = (SELECT MIN(weekday) FROM test); -- 1，Mon

枚举类型MySQL也同样支持，只不过没怎么用，更多的是使用代码中的枚举与数值类型进行映射。

不过枚举类型也有好处比如：语义明确、可读性高，存储空间小（用整数索引）。

注意

尽管枚举类型的主要目的是用于值的静态集合，但也有方法在现有枚举类型中增加新值和重命名值（ALTER TYPE）。

但是不能从枚举类型中去除现有的值，也不能更改这些值的排序顺序，如果要那样做只能删除并且重建枚举类型。

---

7 JSON类型

PostgreSQL 提供存储JSON数据的两种类型：json 和 jsonb。

类型	描述
json	原始 JSON 字符串（存储原样）
jsonb	二进制结构，支持索引和高效查询

JSON在MySQL8.x中也做了支持，但是MySQL的支持并不友好，因为在使用JSON类型做查询时，无法给JSON字段做索引（Json串中的id属性）。

json数据类型存储输入文本的精准拷贝，处理函数必须在每次执行时必须重新解析该数据。

而jsonb数据被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。

但是 jsonb在处理（输出）时要快很多，因为不需要解析。

JSON和JSONB的区别：

• JSON类型无法构建索引，JSONB类型可以创建索引。
• JSON类型的数据中多余的空格会被存储下来，JSONB会自动取消多余的空格。
• JSON类型甚至可以存储重复的key，以最后一个为准；JSONB不会保留多余的重复key（保留最后一个）。
• JSON会保留存储时key的顺序，JSONB不会保留原有顺序。

JSON 基本类型都会有相应的PostgreSQL类型对应，所以需要满足对应的PostgreSQL类型约束，比如字段范围，除此之外还有一些额外要求，如下：

JSON 基本类型	PostgreSQL类型	注释
string	text	\u0000是不允许的，因为 Unicode 转义表示数据库编码中不可用的字符
number	numeric	不允许NaN 和 infinity值
boolean	boolean	只接受小写true和false拼写
null	(无)	SQL NULL是一个不同的概念

简单标量/基本值、数组和对象的使用

-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json; -- 5
-- 有零个或者更多元素的数组（元素不需要为同一类型）
SELECT '[1, 2, "foo", null]'::json; -- [1, 2, "foo", null]
-- 包含键值对的对象
-- 注意对象键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
-- {"bar": "baz", "balance": 7.77, "active": false}
-- jsonb将去除空格
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::jsonb;
-- {
--   "bar": "baz",
--   "active": false,
--   "balance": 7.77
-- }
-- 数组和对象可以被任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::jsonb;
-- 在jsonb中，数字将根据底层的numeric类型的行为进行打印
SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
-- {"reading": 1.230e-5} | {"reading": 0.00001230}

常见运算符 ->、->>、#>、#>>（josn和jsonb都支持）

-- '->' 提取出的元素是json类型 '->>' 提取出来的元素是 text 类型
-- 提取JSON数组的第n个元素 
-- 数组元素从0开始索引
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2; --  {"c":"baz"} json
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json ->> 2; --  {"c":"baz"} text
-- 负整数从末尾开始计数
select  '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> -3; -- {"a":"foo"}
-- 用给定的键提取JSON对象字段
select '{"a": {"b":"foo"}}'::json -> 'a'; -- {"b":"foo"}
-- '->' 或 '->>' 想要获取内层元素只能不断嵌套，很麻烦 比如提取bar元素
select  '{"a": {"b": ["foo","bar"]}}'::jsonb -> 'a' -> 'b' -> 1; -- "bar"
-- '#>' 用于提取指定路径下的JSON子对象，路径元素可以是字段键或数组索引
select  '{"a": {"b": ["foo","bar"]}}'::json #> '{a,b,1}'; -- "bar" josn
-- '#>>' 用于提取对象为text
select  '{"a": {"b": ["foo","bar"]}}'::json #>> '{a,b,1}'; -- "bar" josn
-- 总结: '-' 用于获取外层元素 '#' 获取嵌套元素 '>' 提取json类型 '>>'提取 text类型

jsonb 包含和存在运算（json不支持）

-- 简单的标量/基本值只包含相同的值：
SELECT '"foo"'::jsonb @> '"foo"'::jsonb; -- true
-- 右边的数字被包含在左边的数组中：
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb; -- true
-- 数组元素的顺序没有意义，因此这个例子也返回真：
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb; -- true
-- 重复的数组元素也没有关系：
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb; -- true
-- 右边具有一个单一键值对的对象被包含在左边的对象中：
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb; -- true
-- 右边的数组不会被认为包含在左边的数组中，
-- 即使其中嵌入了一个相似的数组：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- false
-- 但是如果同样也有嵌套，包含就成立：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb; -- true
-- 类似的，这个例子也不会被认为是包含：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- false
-- 包含一个顶层键和一个空对象：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb; --true
-- 一般原则是结构和数据内容匹配，但有一种特殊情况，一个数组可以包含一个基本值
-- 这个数组包含基本字符串值，反之基本包含数组则会报错
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb; -- true

jsonb存在操作符，它是包含的一种变体

-- ?用于判断一个text是否出现在jsonb值顶层的一个对象键或数组中
-- 字符串作为一个数组元素存在：
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
-- 字符串作为一个对象键存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo';
-- 不考虑对象值：
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- 得到假
-- 和包含一样，存在必须在顶层匹配：
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- 得到假
-- 如果一个字符串匹配一个基本 JSON 字符串，它就被认为存在：
SELECT '"foo"'::jsonb ? 'foo'; -- true

jsonb 下标操作

-- 通过键提取对象值
SELECT ('{"a": 1}'::jsonb)['a'];
-- 通过键路径提取嵌套对象值
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];
-- 通过索引提取数组元素
SELECT ('[1, "2", null]'::jsonb)[1];
-- 通过键更新对象值。注意'1'周围的引号：分配的值也必须是jsonb类型
UPDATE table_name SET jsonb_field['key'] = '1';
-- 如果任何记录的jsonb_field['a']['b']不是对象，则会引发错误。例如，值{"a": 1}具有键'a'的数值。
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';
-- 使用下标的WHERE子句过滤记录。由于下标的结果是jsonb，我们比较的值也必须是jsonb。双引号使"value"也成为有效的jsonb字符串。
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

其他函数参考：http://www.postgres.cn/docs/current/functions-json.html

jsonb 索引也是GIN 索引（Generalized Inverted Index，通用倒排索引），可以被用来有效地搜索在大量jsonb文档（数据）中出现的键或者键值对。

两类 GIN 操作符类，它们在性能和灵活性方面做出了不同的平衡。

-- 创建表
drop table if exists test;
CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    jdoc JSONB
);
-- 插入示例数据
INSERT INTO test (jdoc) VALUES
('{"name": "Alice", "age": 30, "tags": ["dev", "postgres"]}'),
('{"name": "Bob", "age": 25, "tags": ["test", "mysql"]}'),
('{"name": "Charlie", "age": 35, "tags": ["dev", "redis"], "address": {"city": "Shanghai"}}');
-- 创建索引的两种写法,一种对文档索引、一种使用路径
-- 创建默认操作符类
CREATE INDEX idxgin ON test USING GIN (jdoc);
-- 创建非默认操作符类jsonb_path_ops  GIN (jdoc jsonb_path_ops);
CREATE INDEX idxginp ON test USING GIN ((jdoc -> 'tags'));
-- 查询包含"address"键的文档
EXPLAIN ANALYZE SELECT * FROM test WHERE jdoc ? 'address';
-- 查询包含 {"tags": ["dev"]} 的文档
EXPLAIN ANALYZE SELECT * FROM test WHERE jdoc @> '{"tags": ["dev"]}';
-- 查找地址城市为"Shanghai"的文档
EXPLAIN ANALYZE SELECT * FROM test WHERE jdoc @? '$.address.city ? (@ == "Shanghai")';

默认操作符类：jsonb_ops

• 支持的操作符：?、?|、?&、@>、@?、@@
• 索引细粒度：对每一个键和值单独建索引项（键值分离）。
• 示例：{"a": 1, "b": {"c": 2}} 会索引 a, 1, b, c, 2。
• 灵活性：支持各种类型的查询，但效率较低（需多条件合并）。
• 缺点：索引体积较大。
• 场景：需要检查键存在性（如 jdoc ? 'tags'）

优化型操作符类：jsonb_path_ops（优先选择）

• 支持的操作符：@>、@?、@@
• 索引细粒度：将键和值哈希合并为单一索引项（路径表达式哈希化）。
• 示例：{"a": {"b": "c"}} 会哈希 a + b + c 生成单一索引项。
• 优点：索引更小，查询更快，特别高频路径查询（特异性强）。
• 缺点：不支持 ? 等键存在性操作，也无法检索空结构如 {"a": {}}。
• 场景：专注于路径匹配或包含性查询（如 jdoc @> '{}'）。

‌场景1：检查键是否存在，jsonb_path_ops 无法支持此查询，需全表扫描。

-- 让优化器尽量避免选择全表顺序扫描(有时数据可能重复度很高)
SET enable_seqscan=off
-- 需要 `jsonb_ops` 索引
SELECT * FROM test WHERE jdoc ? 'tags';

场景2：路径包含查询

-- 两者均支持，但 `jsonb_path_ops` 更快
SELECT * FROM test WHERE jdoc @> '{"tags": ["dev"]}';

场景3：JSONPath 匹配

-- 两者均支持，但 `jsonb_ops` 支持更多通配符
SELECT * FROM test WHERE jdoc @? '$.tags[*] ? (@ == "dev")';

若需高频查询特定键（如 tags），可创建 jsonb_path_ops 定向索引

CREATE INDEX idx_tags ON test USING GIN ((jdoc -> 'tags'));

---

8 组合类型

一个组合类型表示一行或一个记录的结构，它本质上就是一个域名和它们数据类型的列表，就好像Java中的一个对象。

比如，有如下Java对象

class Info{
   private String name;
   private Integer age;
}
public class User{
   private Integer id;
   private Info info;
}

按照上面的情况，将其构建成一个组合类型

-- 构建复合类型，映射上Info
create type info_type as (name varchar(32),age int);
-- 构建表，映射User
create table tb_user(
    id serial,
    info info_type
);

-- 添加数据 组合类型使用括号表示
insert into tb_user (info) values (('张三',23));
insert into tb_user (info) values (('露丝',233));
insert into tb_user (info) values (('jack',33));
-- ROW表达式也能被用来构建组合值
insert into tb_user (info) values (ROW('李四',24));

-- 访问组合值类型
select * from tb_user; -- 1 (李四,24)
-- 下面这种不被接受 ERROR
SELECT "info".name FROM tb_user WHERE "info".age > 9;
-- info会被当成表名,而不是tb_user的列名,必须写成下面这样
SELECT ("info").name FROM tb_user WHERE ("info").age > 9;
-- 如果还需要使用表名（例如在一个多表查询中）
SELECT (tb_user.info).name FROM tb_user WHERE (tb_user.info).age > 9;
-- 加上括号的对象就被正确地解释为对item列的引用，然后可以从中选出子域。

9 数组类型

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

数组还是要依赖其他类型，比如在设置住址，住址可能有多个住址，可以采用数组类型去修饰字符串。

PostgreSQL中，指定数组的方式就是[]，可以指定一维数组，也支持二维甚至更多维数组。

-- 创建测试表
create table test(
    id serial,
    col1 int[],
    col2 int[2],
    col3 int[][]
);
-- 构建表指定数组长度后，并不是说数组内容只有2的长度，可以插入更多数据
-- 甚至在你插入数据，如果将二维数组结构的数组扔到一维数组上，也可以存储。
-- 数组编写方式
select '{{how,are},{are,you}}'::varchar[];
select array[[1,2],[3,4]];
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{7,8,9}');
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}',array[[1,2],[3,4]]);
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{{1,2},{3,4}}');
-- 查看当前表中数据格式
select * from test;

如果现在要存储字符串数组，如果存储的数组中有双引号怎么办，有大括号怎么办。

-- 如果存储的数组中的值，有单引号怎么办？
-- 使用两个单引号，作为一个单引号使用
select '{''how''}'::varchar[];
-- 如果存储的数组中的值，有逗号怎么办？(PGSQL中的数组索引从1开始算，写0也是从1开始算。)
-- 用双引号将数组的数据包起来~
select ('{"how,are"}'::varchar[])[2];
-- 如果存储的数组中的值，有双引号怎么办？
-- 如果要添加双引号，记得转义。
select ('{"\"how\",are"}'::varchar[])[1];

数组的比较方式

-- 包含
select array[1,2] @> array[1];
-- 被包含
select array[1,2] <@ array[1,2,4];
-- 是否有相同元素
select array[2,4,4,45,1] && array[1];

数组拼接

select array[1,2] || array[1]; --{1,2,1}

数组还支持切片的功能

select ('{2,4,4,45,1}'::int[])[1:2];

带下标的赋值方式允许创建下标不是从1开始的数组。

-- 创建一个下标从 -2 到 7 的数组，共 10 个元素
SELECT '[-2:7]={1,2,3,4,5,6,7,8,9,10}'::INT[];
-- 通过切片扩展数组并重新定义下标
UPDATE test SET col1[-2:7] = ARRAY[1,2,3,4,5,6,7,8,9,10];

数组还支持一些其他函数，如 array_upper 、array_lower、array_dims、array_prepend、array_append、array_cat 等（不再过多介绍）。

10 其他类型

还有一些其他类型，很少被使用，甚至基本不会被用到，仅给出简单示例。

要知道在开发过程中，数值、字符串、日期这三种类型起始就可以满足我们90%以上的需求。

1_范围类型

适合表示闭区间、时间段、数值段等

范围类型（Range）	多重范围类型（Multirange）	基础类型	描述
int4range	int4multirange	integer (int4)	整型范围
int8range	int8multirange	bigint (int8)	大整型范围
numrange	nummultirange	numeric	高精度数值范围
tsrange	tsmultirange	timestamp	不带时区的时间范围
tstzrange	tstzmultirange	timestamp with tz	带时区的时间范围
daterange	datemultirange	date	日期范围

示例：

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');
-- 包含
SELECT int4range(10, 20) @> 3;
-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
-- 抽取上界
SELECT upper(int8range(15, 25));
-- 计算交集
SELECT int4range(10, 20) * int4range(15, 25);
-- 范围为空吗？
SELECT isempty(numrange(1, 5));
-- 查询是否重叠
SELECT * FROM reservation WHERE during && '[2000-01-01, 2025-01-10)';

---

2_UUID 类型

格式：xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

推荐用于分布式系统中的唯一主键标识。

CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid(),
  name TEXT
);

3_几何类型

PostgreSQL支持几何数据类型表示二维的空间物体，主要有以下几种

类型	存储尺寸	表示形式	描述
point	16 字节	(x, y)	平面上的一个点
line	32 字节	{A, B, C}	无限长的直线，Ax + By + C = 0
lseg	32 字节	((x1, y1), (x2, y2))	有限线段，两点间直线段
box	32 字节	((x1, y1), (x2, y2))	由两个对角点确定的矩形框
path	16 + 16n 字节	[(x1, y1), ...] 或 ((x1, y1), ...)	开放路径 [] 或封闭路径 ()
polygon	40 + 16n 字节	((x1, y1), (x2, y2), ...)	多边形，封闭图形，类似封闭路径
circle	24 字节	<(x, y), r>	圆，由中心点 (x, y) 和半径 r 表示

示例：

-- 点
select '(1,2)'::point;
-- 线:线由线性方程Ax + By + C = 0 表示
select '{1,2,3}'::line;
-- ....

如果真有几何类型需求的话，建议选择MongoDB或者ES，而不是其他产品 😃。

4_网络地址类型

PostgreSQL提供用于存储 IPv4、IPv6 和 MAC 地址的数据类型，主要用于输入错误检查以及特殊的操作符和函数计算。

类型	存储尺寸	描述
cidr	7 或 19 字节	表示 IPv4 或 IPv6 网络（如 192.168.0.0/24）
inet	7 或 19 字节	表示 IPv4 或 IPv6 主机地址或网络（如 192.168.0.1/24）
macaddr	6 字节	MAC 地址（EUI-48，如 08:00:2b:01:02:03）
macaddr8	8 字节	扩展格式的 MAC 地址（EUI-64，如 08:00:2b:ff:fe:01:02:03）

IP 示例

-- IP校验
select '192.168.200.256'::cidr; -- ERROR：256不是合法的范围
-- 建表
create table test(
    id bigserial,
    ip cidr
);
-- 模拟数据
insert into test (ip) values ('192.168.11.11');
insert into test (ip) values ('192.168.11.12');
insert into test (ip) values ('192.168.11.13');
-- 地址范围查找
select * from test where ip between '192.168.11.11' and '192.168.11.12';

5_域类型

域是一种用户定义的数据类型，它基于另一种底层类型，主要用于约束来限制底层类型的有效值。

例如，定义一个带验证的邮箱字段类型：

CREATE DOMAIN email AS TEXT
  CHECK (VALUE ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$');

6_补充

更多的其他类型就不再介绍了，比如XML之类的。

---

11 结语

PostgreSQL 的数据类型系统是其强大灵活的基础之一，既覆盖传统 SQL 标准，也支持现代开发需求如 JSON、数组、范围、网络等。

合理选择数据类型，不仅可以提升系统表达力和执行效率，还能提升数据一致性和开发体验。

在实际设计表结构时，应结合业务需求、数据特点、未来可扩展性，选择合适的数据类型，不应该选择过于复杂的类型，相反，应该越简单越好（数值、字符、日期）。