PGError: ERROR: operator does not exist: boolean ~~* unknown

Question

Locally I’m developing with SQLite, but on my production host I’m running PostgreSQL. Locally everything’s fine but not so on the production host.

I have built kind of a search form with which I can evaluate all data in my database with any combination I’d like. This seems to work fine as long as I don’t use boolean and/or date fields. PostgreSQL doesn’t seem to like my code very much…

So, here’s some example code:

unless params[:analysis][:sporty].blank?
  tmp_conditions_customer << ["(sporty ILIKE ?)", "%#{params[:analysis][:sporty]}%"]
end

This evaluates to

SELECT COUNT(*) FROM "customers" WHERE ((sporty ILIKE '%%') 

Why’s that anyway? Why the '%%'?

For testing the deployment I’m using Heroku with the Exceptional plugin. This plugin gives me the following hint:

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Thanks Exceptional, but what the heck does that mean? :-D Type casts for SQL queries? How’s that gonna work?

In my migration the database field looks like this:

t.boolean :sporty

And in the form where I’m creating this data I’m using this code

<%= f.label :sporty %><br />
<%= f.select :sporty, options_for_select({ "Ja"  => true, "Nein" => false }), { :include_blank => '-----'}  %>

As I already mentioned, SQLite is my friend, seems to be the much stricter evaluation of PostgreSQL which causes the trouble.

Thanks for your help in advance.

Answer 1

Direct answer is near the bottom . . .

This evaluates to

SELECT COUNT(*) FROM "customers" WHERE ((sporty ILIKE '%%') 

Why’s that anyway? Why the '%%'?

In SQL, the '%' is a wildcard. But your problem seems to be that you're building a WHERE clause that has two opening parens, but only one closing paren.

A WHERE clause like this will probably return (or count) all rows:

WHERE (sport ILIKE '%%')

Type casts for SQL queries? How’s that gonna work?

Standard SQL has a CAST() function. Skeleton syntax is

CAST (expression AS type)

So, for example, you can write

CAST (<any timestamp> AS DATE) 

to change a timestamp into a date data type, or

CAST ('32' AS INTEGER)

to change the string '32' to the integer 32.

In my migration the database field looks like this:

t.boolean :sporty

If the column "sporty" is Boolean, this is your real problem. If you try to use a string comparison on a Boolean (which you did: WHERE ((sporty ILIKE '%%') ) you'll get the error message you saw. You want the statement to read more like these:

SELECT COUNT(*) FROM "customers" WHERE sporty;
SELECT COUNT(*) FROM "customers" WHERE sporty = true;

or

SELECT COUNT(*) FROM "customers" WHERE NOT sporty;
SELECT COUNT(*) FROM "customers" WHERE sporty = false;