PostgreSQL error: checkbox filter

This applies to version 2.2 as well:

Drupal configuration:

Acquia drupal 1.2 (6.8 core)
PostgreSQL 8.3

Problem: When adding a 'checkbox' filter from a profile field.
Diagnose:

1. PostgreSQL has strict data type enforcement, the table column 'value' from table {profile_values} is a type of text, the SQL syntax of WHERE profile_values_profile_partner_foggle_rolereg0.value <> 0 should be WHERE profile_values_profile_partner_foggle_rolereg0.value <> '0' to add explicit type casting

2. The sub-query SELECT COUNT(*) FROM (SELECT ....) also have the wrong syntax in postgreSQL, it needs to have an alias.

SELECT COUNT(*) FROM (SELECT ....) AS 'foo_bar'

I have attached the corrected SQL query for this error and, this query has been tested and it works.

Live SQL output:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: text <> integer LINE 8: ...RE profile_values_profile_partner_foggle_rolereg0.value <> 0 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in /usr/local/apache2/htdocs/acdrupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(*) FROM (SELECT users.uid AS uid FROM ac_users users LEFT JOIN ac_profile_values profile_values_profile_partner_foggle_rolereg0 ON users.uid = profile_values_profile_partner_foggle_rolereg0.uid AND profile_values_profile_partner_foggle_rolereg0.fid = '73' LEFT JOIN ac_profile_values profile_values_profile_first_name ON users.uid = profile_values_profile_first_name.uid AND profile_values_profile_first_name.fid = '1' LEFT JOIN ac_profile_values profile_values_profile_last_name ON users.uid = profile_values_profile_last_name.uid AND profile_values_profile_last_name.fid = '2' LEFT JOIN ac_profile_values profile_values_profile_company_website ON users.uid = profile_values_profile_company_website.uid AND profile_values_profile_company_website.fid = '19' LEFT JOIN ac_profile_values profile_values_profile_country ON users.uid = profile_values_profile_country.uid AND profile_values_profile_country.fid = '27' WHERE profile_values_profile_partner_foggle_rolereg0.value <> 0 ) count_alias in /usr/local/apache2/htdocs/acdrupal/modules/acquia/views/includes/view.inc on line 699.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: text <> integer LINE 15: ...RE profile_values_profile_partner_foggle_rolereg0.value <> 0 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in /usr/local/apache2/htdocs/acdrupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT users.uid AS uid, users.name AS users_name, profile_values_profile_first_name.value AS profile_values_profile_first_name_value, profile_values_profile_last_name.value AS profile_values_profile_last_name_value, profile_values_profile_company_website.value AS profile_values_profile_company_website_value, profile_values_profile_country.value AS profile_values_profile_country_value, users.created AS users_created, users.login AS users_login FROM ac_users users LEFT JOIN ac_profile_values profile_values_profile_partner_foggle_rolereg0 ON users.uid = profile_values_profile_partner_foggle_rolereg0.uid AND profile_values_profile_partner_foggle_rolereg0.fid = '73' LEFT JOIN ac_profile_values profile_values_profile_first_name ON users.uid = profile_values_profile_first_name.uid AND profile_values_profile_first_name.fid = '1' LEFT JOIN ac_profile_values profile_values_profile_last_name ON users.uid = profile_values_profile_last_name.uid AND profile_values_profile_last_name.fid = '2' LEFT JOIN ac_profile_values profile_values_profile_company_website ON users.uid = profile_values_profile_company_website.uid AND profile_values_profile_company_website.fid = '19' LEFT JOIN ac_profile_values profile_values_profile_country ON users.uid = profile_values_profile_country.uid AND profile_values_profile_country.fid = '27' WHERE profile_values_profile_partner_foggle_rolereg0.value <> 0 LIMIT 10 OFFSET 0 in /usr/local/apache2/htdocs/acdrupal/modules/acquia/views/includes/view.inc on line 725.

Exported View:

$view = new view; 
$view->name = 'partner_lead'; 
$view->description = 'To show registered users who are interested in become partners'; $view->tag = 'partner lead'; 
$view->view_php = ''; $view->base_table = 'users'; $view->is_cacheable = FALSE; $view->api_version = 2; 
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */ 
$handler = $view->new_display('default', 'Defaults', 'default'); 
$handler->override_option('fields', array( 'name' => array( 'id' => 'name', 'table' => 'users', 'field' => 'name', ), 'value_2' => array( 'id' => 'value_2', 'table' => 'profile_values_profile_first_name', 'field' => 'value', ), 'value_3' => array( 'id' => 'value_3', 'table' => 'profile_values_profile_last_name', 'field' => 'value', ), 'value' => array( 'id' => 'value', 'table' => 'profile_values_profile_company_website', 'field' => 'value', ), 'value_1' => array( 'id' => 'value_1', 'table' => 'profile_values_profile_country', 'field' => 'value', ), 'created' => array( 'label' => 'Join Date', 'date_format' => 'small', 'custom_date_format' => '', 'exclude' => 0, 'id' => 'created', 'table' => 'users', 'field' => 'created', 'override' => array( 'button' => 'Override', ), 'relationship' => 'none', ), 'login' => array( 'id' => 'login', 'table' => 'users', 'field' => 'login', ), )); $handler->override_option('arguments', array( 'value' => array( 'id' => 'value', 'table' => 'profile_values_profile_registration_source', 'field' => 'value', ), )); 
$handler->override_option('filters', array( 'value' => array( 'operator' => '=', 'value' => '1', 'group' => '0', 'exposed' => FALSE, 'expose' => array( 'operator' => FALSE, 'label' => '', ), 'id' => 'value', 'table' => 'profile_values_profile_partner_foggle_rolereg0', 'field' => 'value', 'relationship' => 'none', ), )); 
$handler->override_option('access', array( 'type' => 'none', ));
 $handler->override_option('use_pager', '1'); 
$handler->override_option('style_plugin', 'table'); 
$handler->override_option('style_options', array( 'grouping' => '', 'override' => 1, 'sticky' => 0, 'order' => 'asc', 'columns' => array( 'name' => 'name', 'value_2' => 'value_2', 'value_3' => 'value_3', 'value' => 'value', 'value_1' => 'value_1', 'created' => 'created', 'login' => 'login', 'rid' => 'rid', ), 'info' => array( 'name' => array( 'sortable' => 0, 'separator' => '|', ), 'value_2' => array( 'sortable' => 0, 'separator' => '|', ), 'value_3' => array( 'sortable' => 0, 'separator' => '|', ), 'value' => array( 'sortable' => 0, 'separator' => '|', ), 'value_1' => array( 'sortable' => 0, 'separator' => '|', ), 'created' => array( 'sortable' => 0, 'separator' => '|', ), 'login' => array( 'sortable' => 0, 'separator' => '|', ), 'rid' => array( 'separator' => '|', ), ), 'default' => '-1', )); 
$handler = $view->new_display('page', 'Page', 'page_1'); 
$handler->override_option('path', 'view/partner_lead'); 
$handler->override_option('menu', array( 'type' => 'none', 'title' => '', 'weight' => 0, 'name' => 'navigation', )); $handler->override_option('tab_options', array( 'type' => 'none', 'title' => '', 'weight' => 0, ));