轨道与Postgres：迁移到change_colomn会产生错误“无法转换为没有时区的timestamp类型”。

问题描述

Rails迁移将 deleted_at时间列转换为日期时间列失败。关于如何解决这个问题的任何想法？如果需要的话，它是Postgres的全新安装。

A Rails migration to turn a "deleted_at" time column to a datetime column failed. Any ideas on how to solve this? It's a fresh install of Postgres if that is relevant.

-- change_column(:products, :deleted_at, :datetime)


 PGError: ERROR:  column "deleted_at" cannot be cast to type timestamp without time zone
: ALTER TABLE "products" ALTER COLUMN "deleted_at" TYPE timestamp

推荐答案

您不能随时间更改字段的类型（ datetime） ，因为值无法转换-数据库不知道日期。

You can't alter a field's type from time to timestamp ("datetime"), because the values couldn't be converted -- the database doesn't know the date.

但是，您可以删除并重新输入-创建列：

You can, however, drop and re-create the column:

ALTER TABLE products DROP COLUMN deleted_at;
ALTER TABLE products ADD COLUMN deleted_at timestamp;

或者如果此字段设置为NOT NULL，则应该执行以下操作：

Or if this field was set to NOT NULL, you should instead do:

ALTER TABLE products ADD COLUMN deleted_at timestamp NOT NULL;

但是，如果您坚持像Sean这样在表中保留假值，则可以使用ALTER ... TYPE ...的用法如下：

But if you insist on retaining fake values in this table like Sean, you can use ALTER...TYPE...USING like this:

ALTER TABLE products ALTER COLUMN deleted_at TYPE timestamp USING
    CASE WHEN deleted_at IS NOT NULL THEN timestamp '1970-01-01 00:00:00' END;
-- Or:
ALTER TABLE products ALTER COLUMN deleted_at
    TYPE timestamp USING date '1970-01-01' + deleted_at;

这篇关于轨道与Postgres：迁移到change_colomn会产生错误“无法转换为没有时区的timestamp类型”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！