将JSON列更改为INTEGER [] ARRAY

一尘不染

将JSON列更改为INTEGER [] ARRAY

sql

我有一个包含整数数组的JSON列。我试图将其转换为INTEGER []列，但遇到了转换错误。

这是我的最终变更版本：

ALTER TABLE namespace_list ALTER COLUMN namespace_ids TYPE INTEGER[] USING string_to_array(namespace_ids::integer[], ',');

但是，这将引发以下错误： ERROR: cannot cast type json to integer[]

有什么想法可以解决这个转换吗？我已经尝试了几件事，但最终还是遇到了相同的错误。似乎去json->字符串->->数组不起作用。我有什么选择？

编辑：

表定义：

db => \d+ namespace_list;

Column         |   Type   |  Table "kiwi.namespace_list" Modifiers|
---------------+----------+--------------------------------------+
id             | integer  | not null default nextval('namespace_list_id_seq'::regclass)
namespace_ids  | json     | not null default '[]'::json

样本数据：

id | namespace_ids | 
-------------------+
1 | [1,2,3]        |

阅读 197

2021-05-30

共1个答案

一尘不染

如果您的数组中没有无效字符，则可以完成此工作：

ALTER TABLE namespace_list
ALTER COLUMN namespace_ids TYPE INTEGER[]
      USING translate(namespace_ids::text, '[]','{}')::int[];

- > SQLfiddle的

特殊的困难在于，USING子句中不能有子查询表达式，因此取消嵌套和重新聚合 不是一种选择 ：

SELECT ARRAY(SELECT(json_array_elements(json_col)::text::int))
FROM   namespace_list;

因此，我求助于字符串操作以为整数数组生成有效的字符串常量并将其强制转换。

柱子 `DEFAULT`

根据注释中的每个请求
，由于缺少表定义，因此我首先自己构建了测试用例。因此，我错过default '[]'::json了稍后添加的实际表定义。
解决方案：在执行上述操作 之前， 请删除默认列。我更新了小提琴。

ALTER TABLE namespace_list ALTER COLUMN namespace_ids DROP DEFAULT;

DEFAULT如果需要，可以在之后添加一个新的。最好在同一笔交易中，不要错过任何东西。

2021-05-30