Update column with multiple values Postgres

Question

Sample data:

Sample Data

I am trying update a column with values from multiple columns in another table if two columns match.

Consider the following query:

UPDATE application_table
SET    asset_list = asset_table.asset_name
FROM   asset_table
WHERE  application_table.application_name = asset_table.applications;

My table structure is:

application_table:
"asset_list";         "text[]"
"application_name";   "character varying"

asset_table:
"asset_name";         "character varying"
"applications";       "character varying"

I get the following error:

ERROR:  column "asset_list" is of type text[] but expression is of type character varying
Line 12 SET    asset_list = asset_table.asset_name

It's telling me I need to cast the data, i'm not sure the correct syntax to do this. — Ohmsy
– Ohmsy, Commented Nov 14, 2018 at 14:41
Since asset_list is an array, are you trying to add asset_table.asset_name to the array, or overwrite any existing value with a new array containing only that value? — 404
– 404, Commented Nov 14, 2018 at 16:51
@eurotrash, I would like to overwrite. I would like to set this up as a job to constantly check the asset_table and add any new asset_name if they get assigned a matching application. — Ohmsy
– Ohmsy, Commented Nov 14, 2018 at 18:47

404 · Accepted Answer · 2018-11-14 19:11:33Z

1

What you need to do is aggregate the asset_name per applications value and set asset_list to that aggregated value.

Problem is you can't do something like

UPDATE ..
SET asset_list = ARRAY_AGG(asset_name)
FROM ...

because aggregate functions are not allowed in updates like that.

So here's two other ways to do it:

UPDATE app_table
SET asset_list = _asset_list
FROM (
    SELECT applications, ARRAY_AGG(asset_name ORDER BY asset_name) AS _asset_list
    FROM asset_table
    GROUP BY applications
) AS a
WHERE app_name = applications;

https://www.db-fiddle.com/f/pKB5k6Lexwzqv6ZbCCdJay/0

This first builds a result set of distinct application names and an array of all the asset_names for each of the app names. Then it updates the table as usual with that array value.

Another way is:

UPDATE app_table
SET asset_list = (SELECT ARRAY_AGG(asset_name ORDER BY asset_name)
                  FROM asset_table
                  WHERE applications = app_name)
;

https://www.db-fiddle.com/f/8oVWsubXW93n142gtZYLXB/0

This will update every record in app_table, and calculates the array value on the fly for every record.

answered Nov 14, 2018 at 19:11

404

8,7522 gold badges33 silver badges53 bronze badges

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

1 Comment

Laurenz Albe Over a year ago

Your second solution will force a nested loop join, so I think your first solution is better.

Collectives™ on Stack Overflow

Update column with multiple values Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related