Rails - PostgreSQL boolean where statement, ERROR: operator does not exist: boolean = integer

Question

I have a model that contains a boolean attribute, Member can be admin. When I create a new Team I want to set this value to true for the Member that have created that Team. When I used SQLite it worked fine. The line that causes the error looks like this:

def set_admin
  if self.members.admins.any? == false
    self.members.first.update_attributes admin: true
  end
end

My query (members.admins) that looks like this:

has_many :admins, -> { where("admin = 1") }, through: :members, source: :user

Note I have also tried:

has_many :admins, -> { where(admin = true) }, through: :members, source: :user

But I get the same error:

The full error message looks like this:

PG::UndefinedFunction: ERROR: operator does not exist: boolean = integer LINE 1: ...s" WHERE "members"."team_id" = $1 AND "members"."admin" = 1 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. : SELECT COUNT(*) FROM "members" WHERE "members"."team_id" = $1 AND "members"."admin" = 1

And I'n not sure how to solve it. Any ideas?

spickermann · Accepted Answer · 2014-07-19 12:07:54Z

2

IMHO it is just:

# in team.rb
has_many :admins, -> { where(admin: true) }, through: :members, source: :user
# or if you use postgresql's boolean datatype
has_many :admins, -> { where('admin = \'t\'') }, through: :members, source: :user

def set_admin
  members.first.update_attributes(admin: true) if admins.none?
end

edited Jul 19, 2014 at 12:07

answered Jul 19, 2014 at 8:50

spickermann

108k9 gold badges115 silver badges147 bronze badges

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

1 Comment

Anders Over a year ago

Thanks, updating the set_admin made it work, but I had to escape the query as Зелёный wrote: has_many :admins, -> { where('admin = \'t\'') }, through: :members, source: :user.

Roman Kiselenko · Accepted Answer · 2014-07-19 08:32:57Z

1

Try this:

has_many :admins, -> { where("admin = ?", true) }, through: :members, source: :user

sql query:

SELECT COUNT(*) FROM "members" WHERE "members"."team_id" = $1 AND "members"."admin" = t

or try escape query:

has_many :admins, -> { where('admin = \'t\'') }, through: :members, source: :user

or type cast on integer:

has_many :admins, -> { where("admin = 1::int::bool") }, through: :members, source: :user

Reference: Postgres#datatype-boolean

edited Jul 19, 2014 at 8:32

answered Jul 19, 2014 at 8:16

Roman Kiselenko

44.5k9 gold badges100 silver badges111 bronze badges

3 Comments

Anders Over a year ago

Thanks, I have tried both of your suggestions, but I get the same error.

Roman Kiselenko Over a year ago

@Anders what type column of admin? this should work :(

Anders Over a year ago

Thanks, got it to work together with @spickermann's answer. I had to escape it as you suggested: has_many :admins, -> { where('admin = \'t\'') }, through: :members, source: :user, but I also had to update my set_admin method as spickermanns answer.

Collectives™ on Stack Overflow

Rails - PostgreSQL boolean where statement, ERROR: operator does not exist: boolean = integer

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related