在 Postgres 中将列从 Integer[] (ARRAY) 更改为 Integer

Question

如何使用 Postgrestype integer[]其中的数组将整个列/所有数据行重新转换为type integer？

下面，user_email_idtype integer[]

d emails
Column     |           Type
---------------+------------------------
id            | integer
user_id       | integer
user_email_id | integer[]
SELECT id, user_id, user_email_id FROM emails;
id | user_id | user_email_id
----+---------+---------------
65 |       1 | {98,110}
66 |       1 | {99}

对于 user_email_id={99} 的行，这有效：

ALTER table emails 
ALTER COLUMN user_email_id type integer 
USING user_email_id[1]::INTEGER;

id=66 的预期输出;

d emails
Column     |           Type
---------------+------------------------
id            | integer
user_id       | integer
user_email_id | integer
SELECT id, user_id, user_email_id FROM emails where id=66;
id | user_id | user_email_id
----+---------+---------------
66 |       1 | 99

但是，如果数组{98,110}内有两个值，该怎么办？在这种情况下，我想我要么必须转换为字符串，要么必须为 id=65; 的记录创建两行？

Answer 1

我认为您不想尝试将多元素数组混合成字符串或类似的东西，"列中的CSV"是一种众所周知的反模式，只会导致痛苦和痛苦。您最好将多元素数组转换为多行。在注释中，您说没有 FK 引用此表，因此您不必担心创建新行。

你可以分步完成。首先扩展数组以使用unnest和横向连接分隔列：

insert into emails (user_id, user_email_id)
select user_id, array[eid]
from emails,
unnest(user_email_id) as dt(eid)
where array_length(user_email_id, 1) > 1

这将为您提供如下所示的内容：

id | user_id | user_email_id
----+---------+---------------
65 |       1 | {98,110}
66 |       1 | {99}
X1 |       1 | {98}
X2 |       1 | {110}

X1行和X2行来自65.

然后删除刚刚展开的行：

delete from emails
where array_length(user_email_id, 1) > 1

获得：

id | user_id | user_email_id
----+---------+---------------
66 |       1 | {99}
X1 |       1 | {98}
X2 |       1 | {110}

最后，一个带有 USING 子句的 ALTER 列来替换数组：

alter table emails
alter column user_email_id
type int using user_email_id[1]

这会给你留下：

id | user_id | user_email_id
----+---------+---------------
66 |       1 | 99
X1 |       1 | 98
X2 |       1 | 110