PostgreSQL新特性之开发篇

目前越来越多的用户开始使用15甚至16新版本，其中有些客户更关注开发相关的特性，本文将以此为主题重点介绍15及以上版本在应用开发方面的几个主要特性。

1.关于分区表

声明式分区表在版本11功能已经趋于成熟，在此之前一般需要结合pg_partman进行管理，版本17之前，分区表的工作流程管理局限于：创建分区、挂接分区、卸载分区。今年即将发布的17将增加SPLIT PARTITION和MERGE PARTITIONS功能，可将现有的单个分区拆分或对现有的多个分区进行合并。

参考语法为：

CREATE TABLE ... PARTITION BY ... 
CREATE TABLE ... PARTITION OF ...

ALTER TABLE ... ATTACH PARTITION ...

ALTER TABLE ... DETACH PARTITION ...

ALTER TABLE ... SPLIT PARTITIONS ...

ALTER TABLE ... MERGE PARTITIONS ...

抢鲜体验请参考<<分区表的两大刚需｜拆分分区 ｜合并分区>>

2.关于逻辑复制

逻辑复制自版本9.4引入以来，新版本不断完善功能、改进性能来提供可靠稳定的订阅发布服务，正如文章<<逻辑复制7大工艺：历经8载的淬炼>>所述：历经版本10～13四轮次的清新刚劲，14～16三轮次的细腻收敛，每轮次均蕴含独特的风味和年轮记忆，7个引人入胜的逻辑复制特性值得细品。

基于低阶函数进行应用接口编程还可关注logical_replication_mode参数，可选择解析逻辑日志的时机，它支持设置为buffered或者immediate, 设置为immediate后不需要填满logical_decoding_work_mem即可实时解析，非常方便开发测试，同时也可为降低大事务的时间消耗以及为未来实现并行回放大事务做铺垫。

3.SQL:2023标准

Peter Eisentraut在下面的文章分析了PostgreSQL对SQL:2023标准的支持情况：

https://peter.eisentraut.org/blog/2023/04/18/postgresql-and-sql-2023

备注：

• ancient：PostgreSQL 10之前已支持，广泛使用中。
• not planned：社区可能觉得该功能已过时，没有价值做。
• future：未来的版本准备做。

UNIQUE null treatment 请参考文章<<NULL值案例>>

NULL值允许重复，使用唯一约束一般同时加上非空约束，这样可以避免插入多个NULL值。

UNIQUE NULLS DISTINCT 
UNIQUE NULLS NOT  DISTINCT

从版本15开始可以对NULL值是否重复进行语法控制。

Non-decimal integer literals 新标准对数字类型常量增加了多种表示形式，实现了十六进制、八进制、二进制形式的整型常量，非十进制的表示形式可以更容易阅读和理解代码。比如四字节整型最大值很容易用8个F表示。

十进制形式

SELECT 1234;

十六进制形式

SELECT 0x4D2 hex_int;

八进制形式

SELECT 0o2322 oct_int;

二进制形式

SELECT 0b10011010010 bin_int;

作者Peter刚开始是在GO语言的issue里发现该特性，然后作者先提交到SQL 2023，最后提交到PG 16实现这一标准，前后历经了四年，作者博客也有篇文章详细介绍该特性提交的过程。

另外笔者上周五发表的文章<<十大顶尖高手之一吐槽：投身贡献PG的过程相当艰难！>>文章作者也详细介绍提交增量备份的功能到PG 17的过程，也引起一些人的共鸣，本人对那些大佬也一直保持崇高的敬意！

Underscores in numeric literals：使用下划线进行虚拟分组：

例如测试函数里使用虚拟分组，对数据量一目了然

SELECT count(*) FROM generate_series(1, 1_000_000);

查询条件里同样如此

SELECT ... WHERE a > 0 and a < 3.14159_26535_89793/2;

或者用于update语句里，二进制的虚拟分组对程序员非常友好

UPDATE ... SET a = 0x_FFFF_FFFF ...

ANY_VALUE 可以对分组后的数据去重只返回单行，示例如下：

SELECT dept, any_value(person) FROM t1 GROUP BY dept;

使用any_value函数可以在select列表包含非聚合列而不会影响group by的正常行为，这不仅提供了开发人员的查询便利性，同时也提升了性能。

4.merge语法

PG 15新增的merge语法可以对源表和目标表进行匹配后进行DML操作，墨天轮的这篇文章里结合6种常见的模式进行了详细的示例：

https://www.modb.pro/db/388052

5.COPY导入重设默认值

使用COPY命令导入数据文件时，如果数据文件中的某些数据项导入时不希望设置为NULL而使用表字段的默认值来替代。这个场景在PG 16里可以使用COPY命令的default选项实现，例如时间字段导入到数据库时设置为服务端的导入时间。

表结构如下：

CREATE TABLE t1(
a int,
b text default 'unknown',
c date default current_date
);

待导入的数据文件内容如下

1,value,2022-07-04 
2,\D,2022-07-03
3,\D,\D

使用COPY FROM命令结合default选项进行导入：

postgres=# \copy t1 from t1.csv with(format 'csv' ,default '\D')
COPY 3 

当数据文件中包含default选项所指定的字符串"\D"时，将按字段定义的默认值进行替换。

在外部表的options里也同时支持default选项：

CREATE FOREIGN TABLE copy_default ( 
a integer, 
b text default 'unknown', 
c date default '2022-01-01' 
) server file_server 
OPTIONS (format 'csv', filename '/home/postgres/t1.csv', default '\D');

6.易用的开发函数

https://www.modb.pro/db/576451

上面的文章链接里详细介绍了PostgreSQL里一些易用的开发函数：

• JSON函数：从15到17增加了大量的SQL/JSON path函数
• XML函数：xmltable函数、XPATH函数、XML字符串格式化等
• 正则表达式函数：regexp*多种POSIX风格的正则函数
• date_bin函数：将指定的时间戳强制截断到最接近时间间隔的开头
• string_to_table：对将字符串按分隔符拆分为数据行，等价于unnest+string_to_array