sql - 如何将数组项插入 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 的方法。

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允许任意数组索引:

• Normalize array subscripts for 1-dimensional array so they start with 1

无论实际的数组下标如何，这都有效。