Heroku：从 MySQL 迁移数据库后 Postgres 类型运算符错误
这是我之前提出的一个问题的后续问题，该问题将其表述为更多的是编程问题而不是数据库问题。
Heroku 上的 Sinatra/Haml/DataMapper 出现 Postgres 错误
我相信运行db:push后，问题已被隔离到 Heroku 的 Postgres 数据库中 ID 列的存储。
简而言之，我的应用程序在原始 MySQL 数据库上运行正常，但在 ID 列上执行任何查询时，在 Heroku 上抛出 Postgres 错误，该 ID 列似乎已作为 TEXT 存储在 Postgres 中，尽管它在 MySQL 中存储为 INT。我的问题是，为什么在将数据传输到 Heroku 时，在 Postgres 中将 ID 列创建为 INT，以及是否有任何方法可以防止这种情况发生。
以下是 heroku 控制台 会话的输出，演示了该问题：
Ruby console for myapp.heroku.com
>> Post.first.title
=> "Welcome to First!"
>> Post.first.title.class
=> String
>> Post.first.id
=> 1
>> Post.first.id.class
=> Fixnum
>> Post[1]
PostgresError: ERROR: operator does not exist: text = integer
LINE 1: ...", "title", "created_at" FROM "posts" WHERE ("id" = 1) ORDER...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Query: SELECT "id", "name", "email", "url", "title", "created_at" FROM "posts" WHERE ("id" = 1) ORDER BY "id" LIMIT 1
谢谢！
This is a follow-up to a question I'd asked earlier which phrased this as more of a programming problem than a database problem.
Postgres error with Sinatra/Haml/DataMapper on Heroku
I believe the problem has been isolated to the storage of the ID column in Heroku's Postgres database after running db:push.
In short, my app runs properly on my original MySQL database, but throws Postgres errors on Heroku when executing any query on the ID column, which seems to have been stored in Postgres as TEXT even though it is stored as INT in MySQL. My question is why the ID column is being created as INT in Postgres on the data transfer to Heroku, and whether there's any way for me to prevent this.
Here's the output from a heroku console session which demonstrates the issue:
Ruby console for myapp.heroku.com
>> Post.first.title
=> "Welcome to First!"
>> Post.first.title.class
=> String
>> Post.first.id
=> 1
>> Post.first.id.class
=> Fixnum
>> Post[1]
PostgresError: ERROR: operator does not exist: text = integer
LINE 1: ...", "title", "created_at" FROM "posts" WHERE ("id" = 1) ORDER...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Query: SELECT "id", "name", "email", "url", "title", "created_at" FROM "posts" WHERE ("id" = 1) ORDER BY "id" LIMIT 1
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我在 Heroku 提出了支持请求，他们的人员能够快速为我解决这个问题。在向他们发送我的 MySQL 表架构后，他们建议我从 ID 列中删除
UNSIGNED：完成此操作后，我就可以像以前使用 db:push 一样迁移数据库，并且该应用程序功能齐全。
Heroku 的平台和支持给我们留下了越来越深刻的印象。
I opened a support request at Heroku and their guys were able to quickly resolve this for me. After sending them my MySQL table schema, they suggested that I remove
UNSIGNEDfrom my ID columns:Once I did that, I was able to migrate the database the same way as before using
db:push, and the app was fully functional.Continually more impressed w/ Heroku, both for their platform and support.
你不能将它拉到本地 postgres 数据库吗？是否需要执行必要的 ALTER/COPY?MOVE TABLE 魔法并将其再次推回？
Can't you pull it to a local postgres database. Do the necessaery ALTER/COPY?MOVE TABLE magic and push it back again?