Heroku: Postgres type operator error after migrating DB from MySQL

Posted by sevennineteen on Stack Overflow See other posts from Stack Overflow or by sevennineteen
Published on 2010-05-31T13:05:53Z Indexed on 2010/05/31 13:23 UTC
Read the original article Hit count: 227

Filed under:
|
|
|

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!

© Stack Overflow or respective owner

Related posts about postgresql

Related posts about heroku