Postgres error with Sinatra/Haml/DataMapper on Heroku

Question

I'm trying to move a simple Sinatra app over to Heroku. Migration of the Ruby app code and existing MySQL database using Taps went smoothly, but I'm getting the following Postgres error:

PostgresError - ERROR: operator does not exist: text = integer LINE 1: ...d_at", "post_id" FROM "comments" WHERE ("post_id" IN (4, 17,... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

It's evident that the problem is related to a type mismatch in the query, but this is being issued from a Haml template by the DataMapper ORM at a very high level of abstraction, so I'm not sure how I'd go about controlling this...

Specifically, this seems to be throwing up on a call of p.comments from my Haml template, where p represents a given post.

The Datamapper models are related as follows:

class Post
    property :id, Serial
    ...
    has n, :comments
end

class Comment
    property :id, Serial
    ...
    belongs_to :post
end

This works fine on my local and current hosted environment using MySQL, but Postgres is clearly more strict.

There must be hundreds of Datamapper & Haml apps running on Postgres DBs, and this model relationship is super-conventional, so hopefully someone has seen (and determined how to fix) this. Thanks!

UPDATE: See http://stackoverflow.com/questions/2943693/heroku-postgres-type-operator-error-after-migrating-db-from-mysql for resolution.

Answer 1

It looks like post_id is of type TEXT instead of INTEGER. To fix this, you have to change the datatype. This has been changed in version 8.3, older version have an implicit cast. You can tell PostgreSQL to do so:

CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;

CREATE FUNCTION pg_catalog.text(smallint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int2out($1));';
CREATE CAST (smallint AS text) WITH FUNCTION pg_catalog.text(smallint) AS IMPLICIT;

CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;

See also http://wiki.postgresql.org/wiki/Image:Pg83-implicit-casts.sql