The Access ODBC PostgreSQL boolean mess

I like using PostgreSQL with an MS Access frontend for various little database applications, but always had trouble with boolean fields. Finally, after all sorts of tests, I think I nailed down what is needed to make it work, and avoid all these errors like "operator does not exist: boolean = integer", "Data Type mismatch in criteria expression", "invalid input syntax for type boolean: "-" (#7)", and another one which I can't remember. What we want is simple:

• Booleans should be usable in Access queries with simple statements like SELECT x FROM y WHERE z; (and no silly stuff like where z=true, z='t', z=-1 or the insane where cbool(z)=true)
• Booleans should appear as check boxes in Access forms and tables

I still don't quite understand why this is not hanlded transparently by the ODBC driver, but anyway here is what seems to be needed with PostgreSQL 7.4:

• In PostgreSQL, make sure your boolean fields have a default value. Access cannot handle NULLs in booleans.
• Configure the ODBC driver for "Bools as Char": no, "True is -1": yes.
• Create these functions and operators in your database and/or in template1 for new databases:

  CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
  AS '$libdir/plpgsql', 'plpgsql_call_handler'
  LANGUAGE c;
  
  CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
  
  CREATE FUNCTION inttobool(integer, boolean) RETURNS boolean
  AS '
  begin
    if $1=0 and not $2 then
    return true;
    elsif $1<>0 and $2 then
    return true;
    else
            return false;
    end if;
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION inttobool(boolean, integer) RETURNS boolean
  AS '
  begin
    return inttobool($2, $1);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION notinttobool(boolean, integer) RETURNS boolean
  AS '
  begin
    return not inttobool($2,$1);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION notinttobool(integer, boolean) RETURNS boolean
  AS '
  begin
  return not inttobool($1,$2);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE OPERATOR = (
  PROCEDURE = inttobool,
  LEFTARG = boolean,
  RIGHTARG = integer,
  COMMUTATOR = =,
  NEGATOR = <>
  );
  
  CREATE OPERATOR <> (
  PROCEDURE = notinttobool,
  LEFTARG = integer,
  RIGHTARG = boolean,
  COMMUTATOR = <>,
  NEGATOR = =
  );
  
  CREATE OPERATOR = (
  PROCEDURE = inttobool,
  LEFTARG = integer,
  RIGHTARG = boolean,
  COMMUTATOR = =,
  NEGATOR = <>
  );
  
  CREATE OPERATOR <> (
  PROCEDURE = notinttobool,
  LEFTARG = boolean,
  RIGHTARG = integer,
  COMMUTATOR = <>,
  NEGATOR = =
  );

  After linking the tables in Access, if you want check boxes in table view, you need to go into table design mode. It will show an error because it's a linked table; just ignore it. Select your boolean field(s) and set their Lookup -> Display Control to Check Box.

Update: See also this page.

The Access ODBC PostgreSQL boolean mess

I like using PostgreSQL with an MS Access frontend for various little database applications, but always had trouble with boolean fields. Finally, after all sorts of tests, I think I nailed down what is needed to make it work, and avoid all these errors like "operator does not exist: boolean = integer", "Data Type mismatch in criteria expression", "invalid input syntax for type boolean: "-" (#7)", and another one which I can't remember. What we want is simple:

• Booleans should be usable in Access queries with simple statements like SELECT x FROM y WHERE z; (and no silly stuff like where z=true, z='t', z=-1 or the insane where cbool(z)=true)
• Booleans should appear as check boxes in Access forms and tables

I still don't quite understand why this is not hanlded transparently by the ODBC driver, but anyway here is what seems to be needed with PostgreSQL 7.4:

• In PostgreSQL, make sure your boolean fields have a default value. Access cannot handle NULLs in booleans.
• Configure the ODBC driver for "Bools as Char": no, "True is -1": yes.
• Create these functions and operators in your database and/or in template1 for new databases:

  CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
  AS '$libdir/plpgsql', 'plpgsql_call_handler'
  LANGUAGE c;
  
  CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
  
  CREATE FUNCTION inttobool(integer, boolean) RETURNS boolean
  AS '
  begin
    if $1=0 and not $2 then
    return true;
    elsif $1<>0 and $2 then
    return true;
    else
            return false;
    end if;
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION inttobool(boolean, integer) RETURNS boolean
  AS '
  begin
    return inttobool($2, $1);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION notinttobool(boolean, integer) RETURNS boolean
  AS '
  begin
    return not inttobool($2,$1);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION notinttobool(integer, boolean) RETURNS boolean
  AS '
  begin
  return not inttobool($1,$2);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE OPERATOR = (
  PROCEDURE = inttobool,
  LEFTARG = boolean,
  RIGHTARG = integer,
  COMMUTATOR = =,
  NEGATOR = <>
  );
  
  CREATE OPERATOR <> (
  PROCEDURE = notinttobool,
  LEFTARG = integer,
  RIGHTARG = boolean,
  COMMUTATOR = <>,
  NEGATOR = =
  );
  
  CREATE OPERATOR = (
  PROCEDURE = inttobool,
  LEFTARG = integer,
  RIGHTARG = boolean,
  COMMUTATOR = =,
  NEGATOR = <>
  );
  
  CREATE OPERATOR <> (
  PROCEDURE = notinttobool,
  LEFTARG = boolean,
  RIGHTARG = integer,
  COMMUTATOR = <>,
  NEGATOR = =
  );

  After linking the tables in Access, if you want check boxes in table view, you need to go into table design mode. It will show an error because it's a linked table; just ignore it. Select your boolean field(s) and set their Lookup -> Display Control to Check Box.

Update: See also this page.