How to add an array column to a table containing values from another column in the same table-CodePudding

Consider table

CREATE TABLE users
(
    user_id VARCHAR(128) PRIMARY KEY,
)

After adding a auth_identities varchar(128)[] column I would not like to fill in the column with the values from user_id (as we want to support multiple different user identities in the future).

One way I managed to do this was

ALTER TABLE users
    ADD auth_identities VARCHAR(128)[];

UPDATE users u SET auth_identities=array((SELECT user_id FROM users u0 WHERE u0.user_id = u.user_id));

However, I don't particularly like how that UPDATE looks. Is there any way to make it a bit more idiomatic?

CodePudding user response：

No need for a sub-select, just build an array from the existing column:

UPDATE users 
  SET auth_identities = array[user_id];