address_seen

Stores every address we've ever encountered from any source in the database.

Primary Key:

• address_id

Checks:

• address_seen_address1_upper_check - (address1 = upper(address1))
• address_seen_address2_upper_check - (address2 = upper(address2))
• address_seen_address_id_check - ((canonical_address_id IS NULL) = (canonical_primary_address_only_id IS NULL))
• address_seen_city_upper_check - (city = upper(city))
• address_seen_country_code_check - (country_code <> ''::text)
• address_seen_country_subdivision_upper_check - (country_subdivision = upper(country_subdivision))
• address_seen_postal_code_upper_check - (postal_code = upper(postal_code))
• address_seen_state_or_province_upper_check - (state_or_province = upper(state_or_province))

Foreign Keys:

• canonical_address_id => address_canonical.canonical_address_id
• canonical_primary_address_only_id => address_canonical.canonical_address_id
• country_code => npi_country.code

Indexes:

• address_seen_address1_address2_city_state_or_province_posta_idx (Unique) - address1, address2, city, state_or_province, postal_code, country_code, country_subdivision
• address_seen_address1_idx - address1
• address_seen_canonical_address_id_idx - canonical_address_id
• address_seen_canonical_primary_address_only_id_idx - canonical_primary_address_only_id

Column	Data Type	Nullable	Indexed	Description
address_id	bigint	No	Yes	-
canonical_address_id	bigint	Yes	Yes	-
canonical_primary_address_only_id	bigint	Yes	Yes	-
is_invalid_address	boolean	Yes	No	-
address1	text	No	Yes	-
address2	text	No	Yes	-
city	text	No	Yes	-
state_or_province	text	No	Yes	-
postal_code	text	No	Yes	-
country_code	text	No	Yes	-
country_subdivision	text	No	Yes	-
last_retrieved_date	date	Yes	No	-