postgresql Rails 和 Postgres：迁移到 change_colomn 给出错误“无法强制转换为没有时区的时间戳”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5522520/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails & Postgres: Migration to change_colomn gives error "cannot be cast to type timestamp without time zone"
提问by Hyman Kinsella
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.
将“deleted_at”时间列转换为日期时间列的 Rails 迁移失败。关于如何解决这个问题的任何想法？如果相关，它是 Postgres 的全新安装。
-- 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
采纳答案by intgr
You can't alter a field's type from time to timestamp ("datetime"), because the values couldn't be converted -- the database doesn't knowthe 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;
Or if this field was set to NOT NULL, you should instead do:
或者，如果此字段设置为 NOT NULL，则应改为：
ALTER TABLE products ADD COLUMN deleted_at timestamp NOT NULL;
But if you insist on retaining fake values in this table like Sean, you can use ALTER...TYPE...USING like this:
但是，如果您坚持像 Sean 一样在此表中保留假值，则可以像这样使用 ALTER...TYPE...USING：
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;
回答by Paul Odeon
In Rails this would look something like
在 Rails 中，这看起来像
class ChangeStatusUpdatedAtToDateTime < ActiveRecord::Migration
def up
remove_column :bookings, :status_updated_at
add_column :bookings, :status_updated_at, :datetime
end
def down
remove_column :bookings, :status_updated_at
add_column :bookings, :status_updated_at, :time
end
end
