How to insert array items into PostgreSQL table

Question

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:

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.

you want to add whole array element in one column or in separate column or in separate row — Ameya Deshpande, Mar 20 '15 at 10:50
Which programming language are you using? How did you define that array in your language? What if the array has more or less elements than you have rows in your table? Is the element index position always equal to the value in `column1`? (Remember: rows in a relational table are ***not*** sorted, so you can't map array elements to the table rows by "position") — , Mar 20 '15 at 11:01
I am using Python with psycopg2 but I am wondering if I can do it with pure SQL. The array will always have the same amount of elements as rows in the table. I will use a where statment to ensure the right array value is mapped to the right row. — ustroetz, Mar 20 '15 at 11:52
Is the value in `column1` really supposed to be the the array index? Or is this just a misleading example? And, as always, your version of Postgres is relevant. — Erwin Brandstetter, Mar 20 '15 at 23:26

score 9 · Answer 1 · edited May 23 '17 at 12:17

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

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

Assuming that column1 represents the position of column2 in 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).

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

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

This works irregardless of actual array subscripts.

score 5 · Accepted Answer · answered Mar 20 '15 at 12:20

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

If the column1 value always matches the array index, you can do it like this.

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

However if the value in column1 does 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:

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 ctid column.

Could you please explain character by character the right side of the `set column2 = (array[foo])[bar]` thing? — LauriK, Jul 04 '16 at 07:47

score -5 · Answer 3 · answered Mar 20 '15 at 10:53

-5

like this

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

answered Mar 20 '15 at 10:53

avk

871
1
9
22

1

`dual` has no special meaning in Postgres. And this is not even close to an answer. – Erwin Brandstetter Mar 20 '15 at 23:37

How to insert array items into PostgreSQL table

3 Answers3