如何将数组项目插入到PostgreSQL表中

Question

给出这样的数组：

my_array = [2,3,5,23,4]

和下面这样的表格：

 column1 | column2 
---------+----------
   1     |     
   2     |     
   3     |     
   4     |     
   5     | 

我该如何将数组的值插入表格中？大致上我想用 SQL 做类似这样的事情：

for item in my_array:
 UPDATE my_table SET colum2 = item

更新后的表格应该是这样的

 column1 | column2 
---------+----------
   1     |     2 
   2     |     3 
   3     |     5 
   4     |     23 
   5     |     4 

更新： 我正在使用Python的psycopg2库，但是我想知道是否有纯SQL的方法。

- ustroetz

---

你想要将整个数组元素添加到一列中、分别添加到不同的列中还是分别添加到不同的行中？ - Ameya Deshpande

你使用哪种编程语言？你是如何在你的语言中定义那个数组的？如果数组的元素数量比表格的行数多或少怎么办？元素索引位置是否总是等于“column1”中的值？（请记住：关系表中的行不排序，因此您不能通过“位置”将数组元素映射到表格行） - user330315

我正在使用Python和psycopg2，但我想知道是否可以使用纯SQL完成。数组中的元素数量始终与表中的行数相同。我将使用where语句确保正确的数组值映射到正确的行。 - ustroetz

column1 中的值真的应该是数组索引吗？还是这只是一个误导性的例子？并且，像往常一样，您使用的 Postgres 版本也很重要。 - Erwin Brandstetter

Answer 1

在Postgres 9.4中，可以使用WITH ORDINALITY来实现此功能。比其他任何方法都更快、更简洁。

UPDATE test t
SET    column2 = a.column2
FROM   unnest('{2,3,5,23,4}'::int[]) WITH ORDINALITY a(column2, column1)
WHERE  t.column1 = a.column1;

• PostgreSQL unnest() with element number

假设column1表示给定数组中column2的位置，这仅会更新应该被更新的列，并不会影响其他行（就像@a_horse回答中的简单查询那样）。

元素的序号也是一维数组默认的数组下标，但Postgres允许任意的数组下标：

• 将一维数组的下标规范化为从1开始

这适用于实际数组下标无关的情况。

- Erwin Brandstetter

Answer 2

你需要为表格中的每一行生成一个名为“index”的数组。如果column1的值总是匹配数组索引，可以按如下方式完成。

update test  
  set column2 = (array[2,3,5,23,4])[column1];

但是，如果column1中的值不反映数组索引，则需要根据表格中的排序顺序生成数组索引。如果是这种情况，您可以像这样操作：

with numbered_data as (
  select ctid,
         row_number() over (order by column1) as rn --<< this generates the array index values 
  from test         
)
update test  
  set column2  = (array[2,3,5,23,4])[nd.rn]
from numbered_data nd
where nd.ctid = test.ctid;

如果您的表有适当的主键，则可以使用该主键而不是ctid列。

- user330315

---

请问您能逐个字符地解释一下 set column2 = (array[foo])[bar] 语句右侧的内容吗？ - LauriK

Answer 3

像这样

insert into my_table( ..., my_column, ... )
select ..., item, ...
from   dual, ...
where item in (<your array> )

- avk

---

在Postgres中，“dual”没有特殊的含义。而且这甚至不接近于一个答案。 - Erwin Brandstetter