Index an array expression directly in PostgreSQL
- by wich
I'm trying to insert data into a table from a template table. I need to rewrite one of the columns for which I wanted to use a directly indexed array expression, but I can't seem to find how to do this, if it is even possible. The scenario:
create table template (
id integer,
index integer,
foo integer);
insert into template values
(0, 1, 23), (0, 2, 18), (0, 3, 16), (0, 4, 7),
(1, 1, 17), (1, 2, 26), (1, 3, 11), (1, 4, 3);
create table data (
data_id integer,
foo integer);
Now what I'd like to do is the following:
insert into data
select (array[3,7,5,2])[index], foo
from template
where id = 1;
But this doesn't work, the (array[3,7,5,2])[index] syntax isn't valid. I tried a few variants, but was unable to get anything working and wasn't able to find the correct syntax in the docs, nor even whether this is at all possible or not.
As a current workaround I've devised the following, but it is less than ideal, from an elegance perspective at least, but it may also be a performance hit, I haven't looked into that yet.
insert into data
select arr[index], foo
from template, (select array[3,7,5,2] as arr) as q
where id = 1;
If anyone could suggest a (better) alternative to accomplish this I'd like to hear that as well.
