Re: Casting Integer to Boolean in assignment - Mailing list pgsql-general

From Laurenz Albe
Subject Re: Casting Integer to Boolean in assignment
Date
Msg-id 5aa57328453d69fca0694c882262125a225670e8.camel@cybertec.at
Whole thread Raw
In response to Re: Casting Integer to Boolean in assignment  (Alexandre GRAIL <postgresql.general@augure.net>)
List pgsql-general
Alexandre GRAIL wrote:
> 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.)

You are aware that catalog modifications are not supported, right?

One of the reasons is that these modifications will be gone after a
major upgrade, and you'll have to remember to re-apply them.

Making type casts more liberal increases the risk of ambiguities
during type resolution, which cause error messages if PostgreSQL cannot
find a single best candidate.
So there is a price you are paying - but if it works for you, you
probably won't mind.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com



pgsql-general by date:

Previous
From: David Steele
Date:
Subject: Re: [pgbackrest] Expiring the last backup?
Next
From: Andreas Kretschmer
Date:
Subject: Re: Geographical multi-master replication