Postgresql breaking rails querying functionality

Question

I have a rails app that was working fine with sqlite but upon switching over to postgre I'm having an issue with this query:

User.find(1).ratings

Querying for just a works, e.g.

User.find(1)

produces

SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 1]]

however if I append ratings like so:

User.find(1).ratings

produces

  User Load (1.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 1]]
  Rating Load (0.9ms)  SELECT "ratings".* FROM "ratings" WHERE "ratings"."user_id" = 1
PG::Error: ERROR:  operator does not exist: character varying = integer
LINE 1: ...CT "ratings".* FROM "ratings"  WHERE "ratings"."user_id" = 1
                                                                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "ratings".* FROM "ratings"  WHERE "ratings"."user_id" = 1
ActiveRecord::StatementInvalid: PG::Error: ERROR:  operator does not exist: character varying = integer
LINE 1: ...CT "ratings".* FROM "ratings"  WHERE "ratings"."user_id" = 1
                                                                    ^

Whether a pass the :id as an int or string it still produces the above error. The models are set up so :ratings belongs_to User which has_many :ratings. Any ideas? Thanks in advance.

Versions: Rails 3.1.1, Ruby 1.9.2, devise 1.5.1

Is the user_id field/foreign key on the ratings table definitely an integer? — pjumble
– pjumble, Commented May 3, 2012 at 1:18
No, it's not I changed it and it worked thank you. Why would that work on sqlite and not on postgre? — Steve
– Steve, Commented May 3, 2012 at 1:41
SQLite has a rather loose type system, PostgreSQL does not. You should be developing and deploying on the same stack. — mu is too short
– mu is too short, Commented May 3, 2012 at 1:53
possible duplicate of Rails 3.1. Heroku PGError: operator does not exist: character varying = integer — mu is too short
– mu is too short, Commented May 3, 2012 at 1:54

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-05-03 01:44:25Z

5

This error

ERROR: operator does not exist: character varying = integer

usually means that you're trying to compare two different data types for equality. Some platforms will let you do that by silently coercing one to the other. SQLite will, and it will let you do a lot of things that are not compliant with SQL standards. PostgreSQL usually won't allow comparisons like this. Since it prints

WHERE "ratings"."user_id" = 1

in the error message, it's telling you that, although you supplied an integer (1), the column ratings.user_id is actually a varchar().

SQLite isn't SQL. Here I use the universal data type "wibble".

sqlite> create table test (n integer not null, s wibble not null);
sqlite> insert into test values (1, 'Wibble');
sqlite> insert into test values ('Wibble', 1);
sqlite> select * from test;
1|Wibble
Wibble|1

SQLite is happy to let me insert "Wibble" into an integer column. That should at least give you pause, if not nightmares.

PostgreSQL is free and available for many platforms. If you're anticipating deployment on Heroku, I can't see any reason not to install PostgreSQL and develop against it instead of using SQLite.

answered May 3, 2012 at 1:44

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mu is too short Over a year ago

Rails defaults to SQLite (unfortunately) because it is supposed to be "easy" to use. Shame that the Rails people don't have any respect for databases.

Collectives™ on Stack Overflow

Postgresql breaking rails querying functionality

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related