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

On 2021-06-07 04:52, Andrew Dunstan wrote:
> 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:
>>>> ...  If I remove "CAST( license_status AS CHAR ) = 'A'", ...
>>> 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 caused 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

As I mentioned earlier, both the data & the table definitions come from
the FCC, the latter in the form of text files containing their formal
SQL definitions.  These often change (like two weeks ago).  There are 18
tables currently of interest to me, with between 30 & 60 fields in each
table.   Further, the entire data set is replaced every Sunday, with
daily updates during the week.  About 1/6th of the text fields are
defined as VARCHAR;  the rest are CHAR.  All of the text fields that are
used as indexes, are CHAR.

Being mindful of the fact that trailing blanks are significant in CHAR
fields, I find it easier to keep the original FCC table definitions, &
remap them to VIEWs containing the fields I am interested in.  I've been
doing this with the FCC data for over 15 years, starting with PostgreSQL
7.3.

As far as needing a consultant in DB design, the FCC is planning a new
DB architecture "soon", & they sorely need one.  When they export the
data to the public (delimited by "|"), they don't escape some characters
like "|", "\", & <cr>.  That makes it fun ...

-- Dean