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

Question

Locally Im developing using SQLite, but PostgreSQL is running on my working host Im. Locally, everything is fine, but not so on the host.

I created a search form with which I can evaluate all the data in my database with any similar combination. This seems to work fine until I use boolean and / or date fields. PostgreSQL doesn't really like my code ...

So here is a sample code:

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

It is rated as

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

What is that so? Why is "%%"?

To test Im deployment using Heroku with an 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. Exceptionally, but what does this mean?: - D The cast type for SQL queries? Do you want to work?

In my migration, the database field looks like this:

 t.boolean :sporty 

And in the form in which Im creates this data Im using this code

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

As I mentioned, SQLite is my friend, it seems to be a much more rigorous evaluation of PostgreSQL, which is causing problems.

Thanks for your help in advance.

Answer 1

The direct answer is at the bottom.,.

It is rated as

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

What is that so? Why is "%%"?

In SQL, '%' is a wildcard. But it seems your problem is that you are creating a WHERE clause in which there are two initial passwords, but only one closed finger.

A WHERE clause like this is likely to return (or count) all rows:

 WHERE (sport ILIKE '%%') 

Cast type for SQL queries? Reports that you are going to work?

Standard SQL has a CAST () function. Skeleton Syntax

 CAST (expression AS type) 

So, for example, you can write

 CAST (<any timestamp> AS DATE) 

to change the timestamp to 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 "sporty" column is boolean, this is your real problem. If you try to use string comparison in boolean (what you did: WHERE ((sporty ILIKE '%%)), you will get the error message that you saw. You want the statement to look like this:

 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;