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

How to insert array items into PostgreSQL table

提问by ustroetz

Give an array like this:

给出一个这样的数组：

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

and a table like this:

和这样的表：

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

How can I insert the array values into a table. Roughly I want to do something like this with SQL:

如何将数组值插入表中。粗略地说，我想用 SQL 做这样的事情：

for item in my_array:
 UPDATE my_table SET colum2 = item

The updated table should be like this

更新后的表应该是这样的

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

UPDATE: I am using Python psycopg2 but I am wondering if there is a way with pure SQL.

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

采纳答案by a_horse_with_no_name

You need to somehow generate an array "index" for each row in the table.

您需要以某种方式为表中的每一行生成一个数组“索引”。

Ifthe column1value alwaysmatches the array index, you can do it like this.

如果该column1值始终与数组索引匹配，则可以这样做。

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

However if the value in column1does not reflect the array index, you need to generate the array index based on the sort order in the table. If that is the case you can do something like this:

但是如果in的值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;

If your table has a proper primary key, then you can use that instead of the ctidcolumn.

如果您的表有一个正确的主键，那么您可以使用它而不是ctid列。

回答by Erwin Brandstetter

In Postgres 9.4 use the WITH ORDINALITYfor this. Faster and cleaner than anything else.

在 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

• 带有元素编号的 PostgreSQL unnest()

Assuming that column1represents the position of column2in the given array, this only updates columns that are supposed to be updated and does not touch other rows (like the simple query in @a_horse's answer would).

假设column1代表column2在给定数组中的位置，这只会更新应该更新的列并且不会触及其他行（就像@a_horse 的答案中的简单查询一样）。

The ordinal position of an element is also the default array subscript in a 1-dimensional array, but Postgres allows arbitrary array indices:

元素的序数位置也是一维数组中的默认数组下标，但 Postgres 允许任意数组索引：

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

• 规范化一维数组的数组下标，使它们从 1 开始

This works irregardless of actual array subscripts.

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

回答by avk

like this

像这样

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