之前这篇文章主要介绍过15和16版本应用开发方面的一些特性：‍‍‍‍

本文将更新即将发布的17版本在应用开发方面的一些特性。‍

一、分区表功能增强

声明式分区表功能已经比较成熟，16版本分区表的工作流程管理局限于：创建分区、挂接分区、卸载分区。即将发布的17将增加SPLIT PARTITION和MERGE PARTITIONS功能，可将现有的单个分区拆分或对现有的多个分区进行合并。

参考语法为：


ALTER TABLE ... SPLIT PARTITIONS ...

ALTER TABLE ... MERGE PARTITIONS ...

拆分和合并分区功能测试可参考文章：分区表的两大刚需｜拆分分区｜合并分区

同时分区还支持identity columns自增属性，分区之间可以共享一个自增序列，直接插入到分区也能使用。另外ATTACH和DETACH操作也能适配自增属性。

排他约束在17也能在分区表使用，参考示例如下：

CREATE TABLE booking(
    room integer PRIMARY KEY,
    during tstzrange NOT NULL
) PARTITION BY RANGE(room);

CREATE TABLE booking_1_10
PARTITION OF booking FOR VALUES FROM (1) TO (10);
CREATE TABLE booking_11_20
PARTITION OF booking FOR VALUES FROM (11) TO (20);

借阅书时为了不允许在同一阅读室出现时间交叉重叠，使用排他约束

CREATE EXTENSION btree_gist;

ALTER TABLE booking ADD CONSTRAINT no_intersect
EXCLUDE USING gist(room WITH =, during WITH &&);

INSERT INTO booking(room, during) VALUES
    (1, '[today 13:00,today 16:00)'::tstzrange),
    (11, '[today 13:00,today 16:00)'::tstzrange);

下面的插入语句由于时间重叠会报错。

INSERT INTO booking(room, during)
VALUES (1, '[today 14:00,today 18:00)'::tstzrange);

二、Merge Into语法增强

15版本引入了Merge Into语法，17版本对Merge Into语句做了4点增强：增加了returning关键字可以返回DML操作的行，增加merge_action()函数返回DML操作类型，增加WHEN NOT MATCHED BY SOURCE子句。

参考示例如下：


MERGE INTO base_data AS b
USING incr AS i ON b.name = i.name
WHEN MATCHED THEN
UPDATE SET val = b.val + i.diff
WHEN NOT MATCHED THEN
INSERT (name, val) VALUES (i.name, i.diff)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
RETURNING *, merge_action()
;

这篇文章也做了详细测试：飘香新特性之Merge语法增强

17版本也允许可更新的视图进行Merge操作，以前版本遇到视图时会提示如下错误：

ERROR:  cannot execute MERGE on relation "XXX"
DETAIL:  This operation is not supported for views.

三、JSON增强

新增JSON转换及构造相关的函数：json_table、json_scalar、 json_serialize、json_exists、json_query、json_value

json_table示例1：

SELECT * FROM json_table(
'[{"a":10,"b":20},{"a":30,"b":40}]'::jsonb,
'$[*]'
COLUMNS (
        column_a int4 path '$.a',
        column_b int4 path '$.b'
    )
);

json_table示例2：

SELECT * FROM json_table(
'[{"a":10,"b":20},{"a":30,"b":40}]'::jsonb,
'$[*]'
COLUMNS (
id FOR ORDINALITY,
        column_a int4 path '$.a',
        column_b int4 path '$.b',
        a int4,
        b int4,
        c text
    )
);

json_table示例3：

SELECT jt.* FROM sample,
lateral json_table(
    j,
'$[*]'
COLUMNS (
id FOR ORDINALITY,
        author text,
        title text,
NESTED PATH '$.comments[*]'
COLUMNS (
            comment_author text PATH '$.author',
            comment_body text PATH '$.body'
        )
    )
) AS jt;

json_exists示例如下

SELECT json_exists( '{"a":123}'::jsonb, '$.a' );

json_query示例如下

SELECT json_query('[{"x": "aaa"},{"x": "bbb"},{"x": "ccc"}]'::jsonb, '$[1].x' );

json_value示例如下

SELECT json_value('[{"x": "aaa"},{"x": "bbb"},{"x": "ccc"}]'::jsonb, '$[1].x' );

jsonpath增加如下方法：.bigint(), .boolean(), .date(), .decimal(), .integer(), .number(), .string(), .time(), .time_tz(), .timestamp(), .timestamp_tz()

参考示例如下：


\set json '{"key1": 42, "key2": "t", "key3": "Hello, World!"}'


SELECT jsonb_path_query(:'json', '$.key1.integer()'),
jsonb_path_query(:'json', '$.key2.boolean()'),
jsonb_path_query(:'json', '$.key3.string()');

结果如下


jsonb_path_query | jsonb_path_query | jsonb_path_query
------------------+------------------+------------------
42               | true             | "Hello, World!"
(1 row)

四、COPY命令增强

COPY FROM命令在使用text或者csv格式时，可以忽略格式转换错误以及打印详细提示信息：

ON_ERROR选项可设置为ignore忽略错误格式的数据，默认值为stop遇到错误中止
LOG_VERBOSITY选项在ON_ERROR选项设置为ignore时可控制日志详细级别

示例如下：

先准备如下的数据文件data.txt

1 aaaa 1 aaaa
2 bbbb 2 bbbb
3 cccc 3 cccc
4 dddd 4 dddd
5 eeee 5 eeee
6 ffff 6 ffff
7 gggg 7 gggg
8 hhhh 8 hhhh
9 jjjjjjjjj 9 jjjj
10 kkkk 10 kkkk
11 llll 11 lllllll
12 mmmm 12 mmmm
13 nnnn 13 nnnn

创建测试表import

CREATE TABLE import ( a int, b varchar(4), c int, d varchar(4));

data.txt文件的第9行和第11行数据过长，下面使用两个新选项进行测试：


COPY import FROM '/home/postgres/data.txt' 
with (DELIMITER ' ',ON_ERROR 'ignore',LOG_VERBOSITY 'verbose');

输出信息如下：

NOTICE:  skipping row due to data type incompatibility at line 9 for column b: "jjjjjjjjj"
NOTICE:  skipping row due to data type incompatibility at line 11 for column d: "lllllll"
NOTICE:  2 rows were skipped due to data type incompatibility
COPY 11

可以看到前两行输出详细的提示信息。

查询表数据，除了第9和11行，其他行数据正常入库。

postgres=# SELECT import FROM import;
      import       
-------------------
 (1,aaaa,1,aaaa)
 (2,bbbb,2,bbbb)
 (3,cccc,3,cccc)
 (4,dddd,4,dddd)
 (5,eeee,5,eeee)
 (6,ffff,6,ffff)
 (7,gggg,7,gggg)
 (8,hhhh,8,hhhh)
 (10,kkkk,10,kkkk)
 (12,mmmm,12,mmmm)
 (13,nnnn,13,nnnn)
(11 rows)

另外FORCE_NOT_NULL和FORCE_NULL选项允许使用*匹配全部字段


CREATE TABLE t (col1 text NOT NULL, col2 text NOT NULL);


COPY t FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *);

五、虚拟存储列可修改表达式

12版本引入了generated column生成存储列的功能，CREATE TABLE时使用。

13版本增加了ALTER TABLE…ALTER COLUMN…DROP EXPRESSION可以删除表达式，让虚拟存储列重新变为普通列。

17版本增加了ALTER TABLE…ALTER COLUMN…SET EXPRESSION可以修改表达式。

示例如下：

CREATE TABLE test (
id int8 generated always AS IDENTITY PRIMARY KEY,
    when_ts TIMESTAMP,
    when_year int4 generated always AS ( EXTRACT(YEAR FROM when_ts)::int4 ) stored
);

test表的when_year字段存储的4位的年份值，可修改表达式只关注最后的2位年份值即可：

ALTER TABLE test ALTER COLUMN when_year 
SET expression AS ( EXTRACT(YEAR FROM when_ts)::int4 - 2000 );

六、PL/PgSQL增强

PL/pgSQL函数或存储过程里增强了以下特性：

允许%TYPE和%ROWTYPE伪类型使用数组来定义变量或表的行列
允许%TYPE用作自定义复合类型的字段

相关测试可参考文章：PostgreSQL 17新增Oracle兼容性

七、新增事务超时控制参数

17增加了事务超时的控制参数transaction_timeout，需要注意它受另外两个超时参数最小值的影响。

statement_timeout
idle_in_transaction_session_timeout

transaction_timeout参数并不推荐在postgresql.conf文件直接进行设置，而应在单个会话连接或客户端应用程序里设置来预防长事务。该参数只对用户连接里的事务有效，预备事务不受影响

八、开发函数相关

random函数增加范围选项：random(1, 100)
支持integer, bigint, numeric范围
新增整数转二进制和十六进制串函数：to_bin、to_oct
新增SQL标准函数xmltext，将字符串转换为xml值，对xml标记符号和特殊值进行转义
uuid增加两个函数可分别获取版本信息和时间戳信息(为v7预留接口)：uuid_extract_version、uuid_extract_timestamp

还有一些json函数请参考第三部分。

与我联系

微信公众号：象楚之行
墨天轮：https://www.modb.pro/u/15675
微信：skypkmoon

勤耕细作，用心积微；静待花开，量变质成。

ITPub博客

PostgreSQL 17应用开发篇