From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Alexandre GRAIL <postgresql(dot)general(at)augure(dot)net>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Casting Integer to Boolean in assignment |
Date: | 2019-01-25 05:38:40 |
Message-ID: | 5aa57328453d69fca0694c882262125a225670e8.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2019-01-25 05:45:43 | Re: Geographical multi-master replication |
Previous Message | David Steele | 2019-01-25 05:22:05 | Re: [pgbackrest] Expiring the last backup? |