问题描述
这是后面提到的一个问题,我之前问过这个问题,认为这不仅仅是一个数据库问题,而是更多的编程问题。
我认为在运行 db:push
之后,Heroku的Postgres数据库中的ID列存储问题已被隔离。
总之,我的应用程序在我的原始MySQL数据库上正常运行,但在ID列上执行任何查询时,Heroku会抛出Postgres错误,这似乎已经作为TEXT存储在Postgres中尽管它在MySQL中作为INT存储。我的问题是为什么在向Heroku传输数据时Postgres中的ID列被创建为INT,以及是否有任何方法可以阻止这种情况。
以下是输出从 heroku控制台
会话中显示问题:
myapp.heroku.com
pre>
>> Post.first.title
=> 欢迎来到第一!
>> Post.first.title.class
=>字串
>> Post.first.id
=> 1
>> Post.first.id.class
=> Fixnum
>> Post [1]
PostgresError:错误:运算符不存在:text = integer
LINE 1:...,title,created_atFROMpostsWHERE(id= 1 )ORDER ...
^
提示:没有操作符与给定的名称和参数类型匹配您可能需要添加显式类型转换
查询:SELECTid,名称,email,url,title,created_atFROMpostsWHERE(id= 1)ORDER BYidLIMIT 1
谢谢!
解决方案并且他们的人能够很快地为我解决这个问题,在他们发送我的MySQL表格模式之后,他们建议我从ID列中删除
UNSIGNED
:一旦我这样做了,我就能够o使用
db:push
以与之前相同的方式迁移数据库,并且该应用程序功能完全正常。
持续对Heroku有更深的印象,无论是平台还是支持。
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!
解决方案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
UNSIGNED
from 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.
这篇关于Heroku:从MySQL迁移数据库后Postgres输入运算符错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!