CODEWITHSUNDEEP
sqlarrayspostgresql
ustroetz
ustroetz

Reputation: 6292

How to insert array items into PostgreSQL table

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.

Upvotes: 13

Views: 26213

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

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;

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:

This works irregardless of actual array subscripts.

Upvotes: 9

user330315
user330315

Reputation:

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.

Upvotes: 5

avk
avk

Reputation: 871

like this

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

Upvotes: -5

Related Questions