ActiveRecord where.not on text field in PostgreSQL

Question

My ActiveRecord model PaperTrail::Version has a field named whodunnit of type text.

I want to make a where.not query on this field:

PaperTrail::Version.where.not(:whodunnit => 4)

That works fine on SQLite but if fails on PostgreSQL:

2014-12-04T07:21:42.484139+00:00 app[web.1]: ActionView::Template::Error (PG::UndefinedFunction: ERROR:  operator does not exist: character varying <> integer
2014-12-04T07:21:42.484142+00:00 app[web.1]: LINE 1: ....("versions"."whodunnit" != 4)  ORD...

How should I change my query to make it work on both databases?

khampson · Accepted Answer · 2014-12-04 07:46:22Z

1

Looks like SQLite is implicily casting the varchar to an integer for you. Postgres does not do that, so as to reduce the chance for accidental data loss / unexpected behavior.

In Postgres, you can explicitly cast from an integer to a varchar or vice versa using ::<type>. (Although from the error above, it appears your Postgres model has it as a text type, which is Postgres specific, although in many ways it behaves like a varchar.)

I suspect the most straightforward way to do this without special casing is to convert the 4 to a string, which I would expect Rails to then in turn generate SQL that is more compatible.

So, something like this:

PaperTrail::Version.where.not(:whodunnit => 4.to_s)

answered Dec 4, 2014 at 7:46

khampson

15.5k4 gold badges45 silver badges44 bronze badges

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

Comments

Ken Stipek · Accepted Answer · 2014-12-04 07:49:07Z

1

Postgres doesn't change the given integer to a string before trying to apply it to the query. Simply changing the 4 to '4' should fix the issue.

PaperTrail::Version.where.not(:whodunnit => '4')

I went ahead and tested this on my local system just now, and it works to pass an integer into the query using Postgres 9.3.5. Which version are you using?

answered Dec 4, 2014 at 7:49

Ken Stipek

1,5228 silver badges13 bronze badges

2 Comments

sdabet Over a year ago

That's version 9.3.3 (on Heroku)

Ken Stipek Over a year ago

Yeah, just change 4 to '4' (or 4.to_s like khampson said) and it should work.

Collectives™ on Stack Overflow

ActiveRecord where.not on text field in PostgreSQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related