将数组下标标准化，使其从1开始。

Question

PostgreSQL可以使用从任何位置开始的数组下标。
考虑以下示例，创建一个包含3个元素的数组，其下标从5到7：

SELECT '[5:7]={1,2,3}'::int[];

返回：

[5:7]={1,2,3}

我们在下标为5的位置获取第一个元素：

SELECT ('[5:7]={1,2,3}'::int[])[5];

我希望将一维数组规范化为以1作为起始下标。以下是我能想到的最好方法：

SELECT ('[5:7]={1,2,3}'::int[])[array_lower('[5:7]={1,2,3}'::int[], 1):array_upper('[5:7]={1,2,3}'::int[], 1)]

相同，更易于阅读：

WITH   cte(a) AS (SELECT '[5:7]={1,2,3}'::int[])
SELECT a[array_lower(a, 1):array_upper(a, 1)]
FROM   cte;

你知道更简单/更快或至少更优雅的方法吗？

在Postgres 9.5上与旧解决方案进行基准测试

db<>fiddle 这里

在Postgres 14上包括新解决方案的基准测试

db<>fiddle 这里

- Erwin Brandstetter

---

切片（正如@DanielVérité所建议的）是我首先想到的。当然，你可以通过编写C函数来获得最快的转换速度，尽管它可能与切片时间非常接近。我能想到的唯一其他选择是将数组转换为文本，解析出位于“=”右侧的子字符串，然后将其重新转换为正确类型的数组。而且我相信这比切片更丑陋和脆弱。 - kgrittn

Erwin已经发布了更新版本的更优雅的解决方案。但是...对于任何在这里玩代码（仅用于学习目的）的人来说，这里发布的一些代码无法运行（如果曾经可以的话）。编辑队列已满，因此我将在几个评论中提供更正，以便更轻松地阅读。缺少的是围绕一些关键值的一组括号。至少，这是我的经验，在dbeaver中运行这些查询。 - Wellspring

SELECT ('[5:7]={1,2,3}'::int[])[(array_lower('[5:7]={1,2,3}'::int[], 1)):(array_upper('[5:7]={1,2,3}'::int[], 1))] - Wellspring

@Wellspring：记录一下，没有漏掉任何括号。当时没有，现在也没有。我添加了一些示例来证明。 - Erwin Brandstetter

@ErwinBrandstetter 我跟进了您的建议。https://github.com/dbeaver/dbeaver/issues/15575#issuecomment-1048788950 我猜这只是一个 DBeaver 的 bug。 - Wellspring

Answer 1

最终，Postgres 9.6 出现了一个更加优雅的 解决方案。手册链接：

可以省略切片指示符的下限和/或上限；缺失的边界将被替换为数组下标的下限或上限。例如:

所以现在很简单：

SELECT my_arr[:];

使用我的示例数组字面量时，您需要加上括号以使语法明确：

SELECT <b>(</b>'[5:7]={1,2,3}'::int[]<b>)[:]</b>;

与丹尼尔的硬编码最大数组下标解决方案的性能相同 - 这仍然是在使用Postgres 9.5或更早版本时应采取的方法。

- Erwin Brandstetter

---

如何在不使用字符串符号的情况下声明？类似于 select array[1,2,4]::int[0:2] ; 的语句。 - Peter Krauss

@Peter：手册链接： 使用 ARRAY 构造的数组值的下标始终从 1 开始。但是，通过下标赋值有办法解决。如果您对此感兴趣，请提出一个新问题。 - Erwin Brandstetter

例如，在将oidvector转换为oid[]的情况下...数组索引从0开始...所以我需要使用： ... (proargtypes :: oid []) [:] = (__in_regtypes_ :: oid []) [:] ...在我正在进行的与pg_proc信息的比较中。 - sol

Answer 2

有一种更简单但有点丑陋的方法，我认为在技术上是正确的：提取数组中可能最大的切片，而不是计算范围内的准确切片。 这避免了两个函数调用。

示例：

select ('[5:7]={1,2,3}'::int[])[-2147483648:2147483647];

results in:

  int4   
---------
 {1,2,3}

- Daniel Vérité

---

其实，我已经记住了2147483647... 对我来说，使用它似乎是相当简单的答案。4294967295也是如此。 :) - ErikE

这是最好的答案 - 除非出现更优雅的东西。 - Erwin Brandstetter

最终，Postgres 9.6 出现了更加优雅的解决方案。请参见添加的答案。 - Erwin Brandstetter

Answer 3

不太确定是否已经有相关内容，但是：

SELECT array_agg(v) FROM unnest('[5:7]={1,2,3}'::int[]) AS a(v);

为了测试性能，我不得不在测试表中添加id列。速度变慢了。

- vyegorov

---

如何在不使用字符串符号的情况下声明？类似于 select array[1,2,4]::int[0:2] ; 的语句。 - Peter Krauss