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
