PostgreSQL如何实现数组新增元素后自动限制长度为5并移除旧元素

原写法存在的问题 #

• 仅做数组拼接没有长度限制，元素数量会持续增长
• 新内容拼在原数组尾部，不符合「新头条添加到数组顶部」的需求
• 你给出的可选方案里数组切片下标有误，PostgreSQL默认数组下标从1开始而非0

实现代码 #

首先修正建表语句的笔误（原语句大括号应为小括号）：

CREATE TABLE place(
     name text PRIMARY KEY,
     coords text,
     headlines json[]
);

满足需求的Upsert语句：

INSERT INTO place VALUES ('giglio','52.531677;13.381777',
                      ARRAY[
'{"timestamp":"2012-01-13T13:37:27+00:00","headline":"costa concordia sunk"}'
]::json[]) 
ON CONFLICT ON CONSTRAINT place_pkey DO
UPDATE SET headlines = (EXCLUDED.headlines || place.headlines)[1:5]
RETURNING *;

逻辑说明 #

• 把EXCLUDED.headlines放在拼接操作符前面，实现新插入的头条放在数组最顶部
• 拼接完成后直接用切片截取前5个元素：PostgreSQL默认数组下标从1开始，所以写[1:5]即可；如果你手动修改过数组默认下标偏移为0，改成[0:4]即可
• 不管原数组有多少内容，最终数组长度最多为5，自动丢弃超过长度的最早旧头条
• 首次插入无冲突时直接写入新数组，长度不足5时也不会有异常

内容的提问来源于stack exchange，提问作者user2741831