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.
PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.
You can easily create arrays in PostgreSQL by adding square brackets [] immediately after the data type for the column. create table employees ( first_name varchar, last_name varchar, phone_numbers integer[] ); In the above example, we have created column phone_numbers as an array of integers.
First get the element to be inserted, say x. Then get the position at which this element is to be inserted, say pos. Then shift the array elements from this position to one position forward(towards right), and do this for all the other elements next to pos.
PostgreSQL INSERT Multiple Rows First, specify the name of the table that you want to insert data after the INSERT INTO keywords. Second, list the required columns or all columns of the table in parentheses that follow the table name. Third, supply a comma-separated list of rows after the VALUES keyword.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With