PostgreSQL 9.1

Doing some testing with latest and greatest PostgreSQL… Immediately ran into a data type issue with boolean columns. Appears Postgres no longer automatically casts integer zeros and ones into booleans when inserting into or updating boolean columns.

The designer choked on the problem with a fairly cryptic RPC error, but as I was testing a new database, I looked at the query log first and saw:

column “deleted” is of type boolean but expression is of type integer
You will need to rewrite or cast the expression.

A google search brought me to … g01056.php, which shows how to re-establish the auto-cast.

Specifically, as a privileged database user, execute:

update pg_cast set castcontext = ‘a’ where castsource = ‘int’::regtype and casttarget = ‘bool’::regtype;

Hope this helps someone.

Hmmm. This fix doesn’t survive DB restart.

I downgraded the PostgreSQL JDBC driver from postgresql-9.1-901.jdbc4.jar to postgresql-9.1-901.jdbc3.jar.

Problem seems to be gone.

FWIW, this is on Gentoo Linux 64bit w/ Ignition 7.4.1 & Sun JDK