329
edits
Changes
Jump to navigation
Jump to search
'''Issue 1: Faulty CREATE WHERE clause''' <code>ptoassigneend_allus</code> is the result of <code>CREATE TABLE ptoassigneend_allus ASSELECT * FROM ptoassigneendWHERE city ~* '.*\d{5}[-]\d{4}.*' OR addrline1 ~* '.*\d{5}[-]\d{4}.*' ORaddrline2 ~* '.*\d{5}[-]\d{4}.*' OR postcode ~* '.*\d{5}[-]\d{4}.*' ORcountry ~* 'UNITED STATES';</code> It is unclear why this approach is favored over <code>CREATE TABLE ptoassigneend_allus ASSELECT * FROM ptoassigneendWHERE (city ~* '.*\d{5}[-]\d{4}.*' OR addrline1 ~* '.*\d{5}[-]\d{4}.*' ORaddrline2 ~* '.*\d{5}[-]\d{4}.*' OR postcode ~* '.*\d{5}[-]\d{4}.*') ANDcountry ~* 'UNITED STATES';</code> It appears that the country field is correct consistent across all of the entries, and that the country names are taken from a small lookup table.
Therefore, my proposed modified <code>SELECT WHERE</code> is credibleThere are single-digit edge cases that in the strictest interpretation of country are correct.
→Table Origin
Note that the suffix <code>nd</code> stands for "no duplicates". Assume this method was done correctly.
'''Proof:'''
<nowiki>
patent=# select * from (select country, count(country) as c from ptoassigneend group by country oorder by c desc limit 50;
country | c
----------------------------------------+---------
</nowiki>
<nowiki>
