Re: AWS forcing PG upgrade from v9.6 a disaster

On 6/6/21 7:49 PM, Dean Gibson (DB Administrator) wrote:
> On 2021-05-29 13:35, Andrew Dunstan wrote:
>> On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
>>> Meanwhile, I've been doing some checking.  If I remove "CAST(
>>> license_status AS CHAR ) = 'A'", the problem disappears.  Changing the
>>> JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the
>>> problem, but there is an extra row where license_status is NULL, due
>>> to the RIGHT JOIN.  Currently trying to figure that out (why did the
>>> CAST ... match 'A', if it is null?)...
>> Why are you using this expression? It's something you almost never want
>> to do in my experience. Why not use the substr() function to get the
>> first character?
>>
>
> Although it doesn't matter in this case, I do it because in general,
> it changes the type of the value from CHAR to bptext or whatever it
> is, & that has causes comparison issues in the past.  It's just a
> matter of habit for me when working with CHAR() types.
>
> But this case, where it doesn't matter, I'd use LEFT().

That raises the issue of why you're using CHAR(n) fields. Just about
every consultant I know advises simply avoiding them. :-)

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com