persistence.xml and postgresql set 0 == false

Question

We migrate our application from oracle to postgresql. And we have got problem because many question using 0 as false. And now we have got many errors that :

operator does not exist: integer = boolean

Its possible using settings in persistence.xml and define postgresql that 0 = false ???

PS I am using glassfish v3 and toplink as JPA

It appears you are comparing a boolean with an integer. The cast is not implicit, so you'll have to cast it explicitely. — wildplasser, Jan 17 '13 at 21:23
yes oracle using 0 or 1 but postgresql true / false Its posible easy migrate this stuff ?? — Łukasz Woźniczka, Jan 17 '13 at 23:12
It depends. Is it code? Only a few queries? Many queries? You could edit and add an expicit coercion. Or change the datatype while importing and (maybe) change it later.Or use a UDF to do that (but still edit) Or: add a default coercion to the pg-source (could be impossible, and very difficult, not for the faint-at-heart; the type system is robust but easy to break, IMHO) BTW: Please add a code fragment... — wildplasser, Jan 17 '13 at 23:23

score 1 · Answer 1 · answered Apr 21 '13 at 09:40

The issue is that Oracle doesn't have a true bool type but PostgreSQL does. As a result:

SELECT true = '1';

Returns 't' for true

SELECT true = 1;

Produces the error you are experiencing. If you can change your persistence to compare with a quoted (i.e. unknown) type, that will solve the problem.

If you can't do that, you can create a custom operator:

CREATE OR REPLACE FUNCTION comp_bool(bool, int) RETURNS bool LANGUAGE SQL AS
$$
SELECT $1 = $2::bool;
$$;

CREATE OR REPLACE FUNCTION comp_bool(int, bool) RETURNS bool LANGUAGE SQL AS
$$
SELECT $1::bool = $2;
$$;

CREATE OPERATOR = (
    procedure = comp_bool,
    leftarg = bool,
    rightarg = int,
    commutator = =
);


CREATE OPERATOR = (
    procedure = comp_bool,
    leftarg = int,
    rightarg = bool,
    commutator = =
);

Then

SELECT true = 1;

works.....

persistence.xml and postgresql set 0 == false

1 Answers1