Re: Problem in using PostgreSQL ODBC driver with VBA

From: pgsql-odbc-owner(at)postgresql(dot)org
> [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Fred Parkinson
> One thing I now remember from when this issue first appeared is that there
> was another possible solution.
> When trying to use FALSE or TRUE in an Access query that uses a postgres
> boolean, I would get the error:
> ODBC call failed: ERROR: operator does not exist: boolean = integer;...
>
> Apparently you can write an operator and add it to the postgres server,
> an operator that will perform the comparison between a boolean and an
> integer.
> The intern I was working with at the time tried once to write that operator,
> but when his attempt failed at the first try, we went with the kludge because
> we were in a hurry and didn't want to spend the time debugging his code.
>
> I am not sure but I think the operator was written in PHP. In any case,
> that might be a fruitful line of inquery given that you can't rewrite a
> major application to use the kludge I describe below.

A dirty hack is to run the following UPDATE only once on the target database. You can do this from any program such as psql.

UPDATE pg_cast
SET castcontext = 'I'
WHERE castsource = 'int'::regtype AND
casttarget = 'boolean'::regtype;

This will make the type cast from integer to Boolean implicit, enabling the comparison like "WHERE 1 = true".

But implicit casts might cause unexpected trouble. If you find something wrong, revert the castcontext to 'e'.

Regards
Takayuki Tsunakawa