Postgresql alter table -> add column + values

Question

I need to alter a table in my DB. What I want is to ad a column with type integer[] and add values to this column directly from another query which result is 4 id numbers.

What I got currently:

ALTER TABLE resourcen add rs_insurance integer[] 
update resourcen set rs_insurance = (select li_id from li_versicherungsart)

The second command returns following error :

ERROR: column "rs_insurance" is of type integer[] but expression is of type integer
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 37

How can I cast all IDs found to fit into int[] array?

select array_agg(li_id) ... but you most definitely need some kind of co-related subquery. But it's hard to tell without more detailed information on those two tables and how they relate to each other — user330315
– user330315, Commented Nov 23, 2016 at 9:44
Yes that is exaclty what I want. But the li_versicherungsart is different on different installations. — Jacob
– Jacob, Commented Nov 23, 2016 at 9:53

SaiNageswar S · Accepted Answer · 2016-11-23 09:48:05Z

4

You can use postgres array aggregation function:

update resourcen set rs_insurance = (select array_agg(li_id) from li_versicherungsart)

Reference: https://www.postgresql.org/docs/current/static/functions-aggregate.html

edited Nov 23, 2016 at 9:48

user330315

answered Nov 23, 2016 at 9:47

SaiNageswar S

1,35615 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql alter table -> add column + values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related