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

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?

sqlpostgresql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions