Re: Casting Integer to Boolean in assignment

On 24/01/2019 12:45, Geoff Winkless wrote:
> My own opinion is that non-0 should implicitly cast as true and 0
> should cast as false. I just run
>
> UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
>   SELECT c.oid
>   FROM pg_cast c
>   inner join pg_type src ON src.oid = c.castsource
>   inner join pg_type tgt ON tgt.oid = c.casttarget
>   WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%')
>   OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE '%int%')
>   OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE 'bit%')
>   OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE 'bool%')
>   OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE '%int')
> );

Thanks Geoff for this solution, I was thinking it cannot be changed ! I 
end up doing this :

UPDATE pg_cast SET castcontext = 'a' WHERE oid IN (
  SELECT c.oid
  FROM pg_cast c
  inner join pg_type src ON src.oid = c.castsource
  inner join pg_type tgt ON tgt.oid = c.casttarget
  WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%')
);

Only to have 0/1 => bool working in assignment. It saved me from 
injecting ALTER TYPE before and after any INSERT/UPDATE.

In my case I don't control the query which is auto generated. (And the 
framework assumes 1 and 0 are safe boolean values without cast or quote. 
Changing that is not possible.)