Heroku: Postgres type operator error after migrating DB from MySQL
- by sevennineteen
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.
http://stackoverflow.com/questions/2935985/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!
