PostgreSQL中TIMESTAMP[]转INTEGER[]（Unix时间戳数组）报错求助

你的问题出在直接对整个时间戳数组做类型转换——PostgreSQL无法自动将timestamp[]直接转成integer[]，必须逐个处理数组里的每个时间戳元素，先转成Unix时间戳再重新组合成数组。

替换成下面的SQL语句即可完成转换：

ALTER TABLE some_table
ALTER COLUMN phases_started_at TYPE integer[]
USING array(SELECT EXTRACT(EPOCH FROM elem)::integer FROM unnest(phases_started_at) AS elem);

语句说明： #

• unnest(phases_started_at)：把原时间戳数组拆分成独立的timestamp元素
• EXTRACT(EPOCH FROM elem)：将单个时间戳转换为Unix时间戳（以秒为单位，返回值为double precision类型），再通过::integer转成整数
• array(...)：把处理后的所有整数重新组合成integer[]类型的数组

额外注意： #

• 如果需要毫秒级Unix时间戳，把EXTRACT(EPOCH FROM elem)::integer改成(EXTRACT(EPOCH FROM elem) * 1000)::bigint，同时列类型要改为bigint[]（因为毫秒级时间戳数值超过了integer的最大值范围）
• 执行前建议先备份表，或者用事务包裹测试：

  BEGIN;
  -- 执行上面的ALTER语句
  -- 检查数据是否正确
  SELECT phases_started_at FROM some_table LIMIT 10;
  -- 没问题就提交，有问题回滚
  -- COMMIT;
  -- ROLLBACK;
  

内容的提问来源于stack exchange，提问作者floppy2029