NBEC->NBUILD import SQL

gistfile1.sql

SELECT COUNT(*) FROM staging_axjd;

SELECT min(id), max(id) FROM staging_axjd;

DROP TABLE IF EXISTS registered_addresses_working;

CREATE TEMPORARY TABLE registered_addresses_working ("row_id" serial, "id" int, "inserted" boolean, "updated_at" timestamp, "zip" TEXT, "lng" numeric, "lat" numeric, "geocode_accuracy" int, "fips" TEXT, "address1" TEXT, "address2" TEXT, "city" TEXT, "county" TEXT, "state" TEXT, "country_code" TEXT, "geocode_city" TEXT, "geocode_address1" TEXT, "geocode_state" TEXT, "geocode_zip" TEXT, "geocode_country_code" TEXT, "geocode_error_count" int, "street_name" TEXT, "street_type" TEXT, "unit_number" TEXT, "identity_hash" TEXT, "street_number" TEXT, "street_prefix" TEXT, "street_suffix" TEXT, "unit_number_integer" int, "street_number_integer" int);

INSERT INTO registered_addresses_working ("inserted", "updated_at", "zip", "lng", "lat", "geocode_accuracy", "fips", "address1", "address2", "city", "county", "state", "country_code", "geocode_city", "geocode_address1", "geocode_state", "geocode_zip", "geocode_country_code", "geocode_error_count", "street_name", "street_type", "unit_number", "identity_hash", "street_number", "street_prefix", "street_suffix", "unit_number_integer", "street_number_integer") SELECT NULL, now(), "registered_zip"::TEXT, "registered_lng"::numeric, "registered_lat"::numeric, "registered_geocode_accuracy"::int, "registered_fips"::TEXT, "registered_address1"::TEXT, "registered_address2"::TEXT, "registered_city"::TEXT, "registered_county"::TEXT, "registered_state"::TEXT, "registered_country_code"::TEXT, "registered_geocode_city"::TEXT, "registered_geocode_address1"::TEXT, "registered_geocode_state"::TEXT, "registered_geocode_zip"::TEXT, "registered_geocode_country_code"::TEXT, "registered_geocode_error_count"::int, "registered_street_name"::TEXT, "registered_street_type"::TEXT, "registered_unit_number"::TEXT, "registered_identity_hash"::TEXT, "registered_street_number"::TEXT, "registered_street_prefix"::TEXT, "registered_street_suffix"::TEXT, "registered_unit_number_integer"::int, "registered_street_number_integer"::int FROM staging_axjd; 

CREATE INDEX ON registered_addresses_working(row_id);

CREATE INDEX ON registered_addresses_working(id);

DROP TABLE IF EXISTS mailing_addresses_working;

CREATE TEMPORARY TABLE mailing_addresses_working ("row_id" serial, "id" int, "inserted" boolean, "updated_at" timestamp, "zip" TEXT, "lng" numeric, "lat" numeric, "geocode_accuracy" int, "fips" TEXT, "address1" TEXT, "address2" TEXT, "city" TEXT, "county" TEXT, "state" TEXT, "country_code" TEXT, "geocode_city" TEXT, "geocode_address1" TEXT, "geocode_state" TEXT, "geocode_zip" TEXT, "geocode_country_code" TEXT, "geocode_error_count" int, "street_name" TEXT, "street_type" TEXT, "unit_number" TEXT, "identity_hash" TEXT, "street_number" TEXT, "street_prefix" TEXT, "street_suffix" TEXT, "unit_number_integer" int, "street_number_integer" int);

INSERT INTO mailing_addresses_working ("inserted", "updated_at", "zip", "lng", "lat", "geocode_accuracy", "fips", "address1", "address2", "city", "county", "state", "country_code", "geocode_city", "geocode_address1", "geocode_state", "geocode_zip", "geocode_country_code", "geocode_error_count", "street_name", "street_type", "unit_number", "identity_hash", "street_number", "street_prefix", "street_suffix", "unit_number_integer", "street_number_integer") SELECT NULL, now(), "mailing_zip"::TEXT, "mailing_lng"::numeric, "mailing_lat"::numeric, "mailing_geocode_accuracy"::int, "mailing_fips"::TEXT, "mailing_address1"::TEXT, "mailing_address2"::TEXT, "mailing_city"::TEXT, "mailing_county"::TEXT, "mailing_state"::TEXT, "mailing_country_code"::TEXT, "mailing_geocode_city"::TEXT, "mailing_geocode_address1"::TEXT, "mailing_geocode_state"::TEXT, "mailing_geocode_zip"::TEXT, "mailing_geocode_country_code"::TEXT, "mailing_geocode_error_count"::int, "mailing_street_name"::TEXT, "mailing_street_type"::TEXT, "mailing_unit_number"::TEXT, "mailing_identity_hash"::TEXT, "mailing_street_number"::TEXT, "mailing_street_prefix"::TEXT, "mailing_street_suffix"::TEXT, "mailing_unit_number_integer"::int, "mailing_street_number_integer"::int FROM staging_axjd; 

CREATE INDEX ON mailing_addresses_working(row_id);

CREATE INDEX ON mailing_addresses_working(id);

DROP TABLE IF EXISTS home_addresses_working;

CREATE TEMPORARY TABLE home_addresses_working ("row_id" serial, "id" int, "inserted" boolean, "updated_at" timestamp, "zip" TEXT, "lng" numeric, "lat" numeric, "geocode_accuracy" int, "fips" TEXT, "address1" TEXT, "address2" TEXT, "city" TEXT, "county" TEXT, "state" TEXT, "country_code" TEXT, "geocode_city" TEXT, "geocode_address1" TEXT, "geocode_state" TEXT, "geocode_zip" TEXT, "geocode_country_code" TEXT, "geocode_error_count" int, "street_name" TEXT, "street_type" TEXT, "unit_number" TEXT, "identity_hash" TEXT, "street_number" TEXT, "street_prefix" TEXT, "street_suffix" TEXT, "unit_number_integer" int, "street_number_integer" int);

INSERT INTO home_addresses_working ("inserted", "updated_at", "zip", "lng", "lat", "geocode_accuracy", "fips", "address1", "address2", "city", "county", "state", "country_code", "geocode_city", "geocode_address1", "geocode_state", "geocode_zip", "geocode_country_code", "geocode_error_count", "street_name", "street_type", "unit_number", "identity_hash", "street_number", "street_prefix", "street_suffix", "unit_number_integer", "street_number_integer") SELECT NULL, now(), "home_zip"::TEXT, "home_lng"::numeric, "home_lat"::numeric, "home_geocode_accuracy"::int, "home_fips"::TEXT, "home_address1"::TEXT, "home_address2"::TEXT, "home_city"::TEXT, "home_county"::TEXT, "home_state"::TEXT, "home_country_code"::TEXT, "home_geocode_city"::TEXT, "home_geocode_address1"::TEXT, "home_geocode_state"::TEXT, "home_geocode_zip"::TEXT, "home_geocode_country_code"::TEXT, "home_geocode_error_count"::int, "home_street_name"::TEXT, "home_street_type"::TEXT, "home_unit_number"::TEXT, "home_identity_hash"::TEXT, "home_street_number"::TEXT, "home_street_prefix"::TEXT, "home_street_suffix"::TEXT, "home_unit_number_integer"::int, "home_street_number_integer"::int FROM staging_axjd; 

CREATE INDEX ON home_addresses_working(row_id);

CREATE INDEX ON home_addresses_working(id);

DROP TABLE IF EXISTS billing_addresses_working;

CREATE TEMPORARY TABLE billing_addresses_working ("row_id" serial, "id" int, "inserted" boolean, "updated_at" timestamp, "zip" TEXT, "lng" numeric, "lat" numeric, "geocode_accuracy" int, "fips" TEXT, "address1" TEXT, "address2" TEXT, "city" TEXT, "county" TEXT, "state" TEXT, "country_code" TEXT, "geocode_city" TEXT, "geocode_address1" TEXT, "geocode_state" TEXT, "geocode_zip" TEXT, "geocode_country_code" TEXT, "geocode_error_count" int, "street_name" TEXT, "street_type" TEXT, "unit_number" TEXT, "identity_hash" TEXT, "street_number" TEXT, "street_prefix" TEXT, "street_suffix" TEXT, "unit_number_integer" int, "street_number_integer" int);

INSERT INTO billing_addresses_working ("inserted", "updated_at", "zip", "lng", "lat", "geocode_accuracy", "fips", "address1", "address2", "city", "county", "state", "country_code", "geocode_city", "geocode_address1", "geocode_state", "geocode_zip", "geocode_country_code", "geocode_error_count", "street_name", "street_type", "unit_number", "identity_hash", "street_number", "street_prefix", "street_suffix", "unit_number_integer", "street_number_integer") SELECT NULL, now(), "billing_zip"::TEXT, "billing_lng"::numeric, "billing_lat"::numeric, "billing_geocode_accuracy"::int, "billing_fips"::TEXT, "billing_address1"::TEXT, "billing_address2"::TEXT, "billing_city"::TEXT, "billing_county"::TEXT, "billing_state"::TEXT, "billing_country_code"::TEXT, "billing_geocode_city"::TEXT, "billing_geocode_address1"::TEXT, "billing_geocode_state"::TEXT, "billing_geocode_zip"::TEXT, "billing_geocode_country_code"::TEXT, "billing_geocode_error_count"::int, "billing_street_name"::TEXT, "billing_street_type"::TEXT, "billing_unit_number"::TEXT, "billing_identity_hash"::TEXT, "billing_street_number"::TEXT, "billing_street_prefix"::TEXT, "billing_street_suffix"::TEXT, "billing_unit_number_integer"::int, "billing_street_number_integer"::int FROM staging_axjd; 

CREATE INDEX ON billing_addresses_working(row_id);

CREATE INDEX ON billing_addresses_working(id);

DROP TABLE IF EXISTS signups_working;

CREATE TEMPORARY TABLE signups_working ("row_id" serial, "id" int, "is_new" boolean, "registered_address_id" int, "mailing_address_id" int, "address_id" int, "billing_address_id" int, "primary_address_id" int, "nbec_guid" text, "state_file_id" TEXT, "county_file_id" TEXT, "state" TEXT, "ngp_id" TEXT, "van_id" TEXT, "civicrm_id" int, "external_id" TEXT, "dw_id" int, "twitter_login" TEXT, "twitter_id" TEXT, "facebook_uid" bigint, "meetup_id" int, "salesforce_id" TEXT, "first_name" TEXT, "last_name" TEXT, "middle_name" TEXT, "full_name" TEXT, "prefix" TEXT, "suffix" TEXT, "born_at" date, "employer" TEXT, "occupation" TEXT, "is_volunteer" boolean, "sex" TEXT, "phone" TEXT, "phone_number" TEXT, "work_phone" TEXT, "work_phone_number" TEXT, "mobile" TEXT, "mobile_number" TEXT, "party" TEXT, "inferred_party" TEXT, "party_member" boolean, "email1" TEXT, "email2" TEXT, "email3" TEXT, "email4" TEXT);

INSERT INTO signups_working ("is_new", "primary_address_id", "nbec_guid", "state_file_id", "county_file_id", "state", "ngp_id", "van_id", "civicrm_id", "external_id", "dw_id", "twitter_login", "twitter_id", "facebook_uid", "meetup_id", "salesforce_id", "first_name", "last_name", "middle_name", "full_name", "prefix", "suffix", "born_at", "employer", "occupation", "is_volunteer", "sex", "phone", "phone_number", "work_phone", "work_phone_number", "mobile", "mobile_number", "party", "inferred_party", "party_member", "email1", "email2", "email3", "email4") SELECT false, NULL, "nbec_guid"::text, "state_file_id", "county_file_id", "registered_state"::TEXT, "ngp_id"::TEXT, "van_id"::TEXT, "civicrm_id"::int, "external_id"::TEXT, "dw_id"::int, "twitter_login"::TEXT, "twitter_id"::TEXT, "facebook_uid"::bigint, "meetup_id"::int, "salesforce_id"::TEXT, "first_name"::TEXT, "last_name"::TEXT, "middle_name"::TEXT, "full_name"::TEXT, "prefix"::TEXT, "suffix"::TEXT, "born_at"::date, "employer"::TEXT, "occupation"::TEXT, "is_volunteer"::boolean, "sex"::TEXT, "phone"::TEXT, "phone_number"::TEXT, "work_phone"::TEXT, "work_phone_number"::TEXT, "mobile"::TEXT, "mobile_number"::TEXT, "party"::TEXT, "inferred_party"::TEXT, "party_member"::boolean, "email1"::TEXT, "email2"::TEXT, "email3"::TEXT, "email4"::TEXT FROM staging_axjd; 

CREATE INDEX ON signups_working(row_id);

CREATE INDEX ON signups_working(id);

---------[START]  addresses: populating primary keys ---------
WITH rows AS  (INSERT INTO addresses (import_id)  SELECT row_id FROM registered_addresses_working  WHERE (trim(coalesce(country_code, state, zip, city, street_name, '')) <> '') RETURNING id, import_id as row_id)
UPDATE registered_addresses_working SET id = rows.id, inserted = true FROM rows WHERE rows.row_id = registered_addresses_working.row_id;
UPDATE addresses SET import_id = NULL FROM registered_addresses_working WHERE registered_addresses_working.id = addresses.id AND registered_addresses_working.inserted = true;
---------[END]  addresses: populating primary keys ---------




---------[START]  addresses: populating primary keys ---------
WITH rows AS  (INSERT INTO addresses (import_id)  SELECT row_id FROM mailing_addresses_working  WHERE (trim(coalesce(country_code, state, zip, city, street_name, '')) <> '') RETURNING id, import_id as row_id)
UPDATE mailing_addresses_working SET id = rows.id, inserted = true FROM rows WHERE rows.row_id = mailing_addresses_working.row_id;
UPDATE addresses SET import_id = NULL FROM mailing_addresses_working WHERE mailing_addresses_working.id = addresses.id AND mailing_addresses_working.inserted = true;
---------[END]  addresses: populating primary keys ---------




---------[START]  addresses: populating primary keys ---------
WITH rows AS  (INSERT INTO addresses (import_id)  SELECT row_id FROM home_addresses_working  WHERE (trim(coalesce(country_code, state, zip, city, street_name, '')) <> '') RETURNING id, import_id as row_id)
UPDATE home_addresses_working SET id = rows.id, inserted = true FROM rows WHERE rows.row_id = home_addresses_working.row_id;
UPDATE addresses SET import_id = NULL FROM home_addresses_working WHERE home_addresses_working.id = addresses.id AND home_addresses_working.inserted = true;
---------[END]  addresses: populating primary keys ---------




---------[START]  addresses: populating primary keys ---------
WITH rows AS  (INSERT INTO addresses (import_id)  SELECT row_id FROM billing_addresses_working  WHERE (trim(coalesce(country_code, state, zip, city, street_name, '')) <> '') RETURNING id, import_id as row_id)
UPDATE billing_addresses_working SET id = rows.id, inserted = true FROM rows WHERE rows.row_id = billing_addresses_working.row_id;
UPDATE addresses SET import_id = NULL FROM billing_addresses_working WHERE billing_addresses_working.id = addresses.id AND billing_addresses_working.inserted = true;
---------[END]  addresses: populating primary keys ---------




---------[START]  signups: populating primary keys ---------
-- match by nbec_guid
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (t.nbec_guid = w.nbec_guid AND t.nbec_guid IS NOT NULL);
-- match by state_file_id
UPDATE signups_working w SET id = t.id FROM signups t LEFT OUTER JOIN addresses a
ON a.id = t.registered_address_id
WHERE w.id IS NULL AND t.nbec_guid IS NULL AND t.state_file_id IS NOT NULL AND a.state IS NOT NULL AND upper(a.state) = upper(w.state) AND t.state_file_id = w.state_file_id;
-- match by various ids
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.ngp_id = w.ngp_id AND trim(coalesce(t.ngp_id, '')) <> '');
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.van_id = w.van_id AND trim(coalesce(t.van_id, '')) <> '');
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.civicrm_id = w.civicrm_id AND t.civicrm_id IS NOT NULL);
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.external_id = w.external_id AND trim(coalesce(t.external_id, '')) <> '');
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.dw_id = w.dw_id AND t.dw_id IS NOT NULL);
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.twitter_id = w.twitter_id AND trim(coalesce(t.twitter_id, '')) <> '');
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.facebook_uid = w.facebook_uid AND t.facebook_uid IS NOT NULL);
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.meetup_id = w.meetup_id AND t.meetup_id IS NOT NULL);
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.salesforce_id = w.salesforce_id AND trim(coalesce(t.salesforce_id, '')) <> '');
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.twitter_login = w.twitter_login AND trim(coalesce(t.twitter_login, '')) <> '');
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND t.county_file_id = w.county_file_id AND trim(coalesce(t.county_file_id, '')) <> '');
-- match by email
UPDATE signups_working w SET id = t.id FROM signups t  WHERE (w.id IS NULL AND ARRAY[t.email1, t.email2, t.email3, t.email4]::text[] && ARRAY[w.email1, w.email2, w.email3, w.email4]::text[]);
-- match by phone, first_name, last_name (only if all three have non blank values)
UPDATE signups_working SET id = signups.id FROM signups
WHERE signups_working.id IS NULL AND signups.nbec_guid IS NULL AND coalesce(signups.phone, '') <> '' AND coalesce(signups.first_name, '') <> '' AND coalesce(signups.last_name, '') <> '' AND signups_working.phone = signups.phone AND lower(signups_working.first_name) = lower(signups.first_name) AND lower(signups_working.last_name) = lower(signups.last_name);
-- using nbec_guid as temorary storage while inserting new signups, so inserted rows could be matched back by row_id
WITH rows AS (INSERT INTO signups (nbec_guid) SELECT row_id FROM signups_working WHERE signups_working.id IS NULL RETURNING id, nbec_guid) UPDATE signups_working SET id = rows.id, is_new = true FROM rows where rows.nbec_guid::text = signups_working.row_id::text;
-- cleaning up temporary nbec_guid
UPDATE signups t SET nbec_guid = null FROM signups_working w WHERE w.is_new = true AND t.id = w.id;
---------[END]  signups: populating primary keys ---------




---------[START]  signups: foreign_key is ready [signups_working/registered_address_id -> registered_addresses_working/id] ---------
WITH changed_primary_addresses AS
(SELECT registered_addresses_working.id address_id, signups.id signup_id FROM
signups JOIN signups_working
ON signups_working.id = signups.id AND signups.primary_address_id = signups.registered_address_id -- selecting what address is primary one
JOIN registered_addresses_working
ON registered_addresses_working.row_id = signups_working.row_id)
UPDATE signups_working SET primary_address_id = changed_primary_addresses.address_id FROM changed_primary_addresses WHERE signups_working.id = changed_primary_addresses.signup_id;
UPDATE signups_working SET registered_address_id = registered_addresses_working.id FROM registered_addresses_working WHERE signups_working.row_id = registered_addresses_working.row_id;---------[END]  signups: foreign_key is ready [signups_working/registered_address_id -> registered_addresses_working/id] ---------




---------[START]  signups: foreign_key is ready [signups_working/mailing_address_id -> mailing_addresses_working/id] ---------
WITH changed_primary_addresses AS
(SELECT mailing_addresses_working.id address_id, signups.id signup_id FROM
signups JOIN signups_working
ON signups_working.id = signups.id AND signups.primary_address_id = signups.mailing_address_id -- selecting what address is primary one
JOIN mailing_addresses_working
ON mailing_addresses_working.row_id = signups_working.row_id)
UPDATE signups_working SET primary_address_id = changed_primary_addresses.address_id FROM changed_primary_addresses WHERE signups_working.id = changed_primary_addresses.signup_id;
UPDATE signups_working SET mailing_address_id = mailing_addresses_working.id FROM mailing_addresses_working WHERE signups_working.row_id = mailing_addresses_working.row_id;---------[END]  signups: foreign_key is ready [signups_working/mailing_address_id -> mailing_addresses_working/id] ---------




---------[START]  signups: foreign_key is ready [signups_working/address_id -> home_addresses_working/id] ---------
WITH changed_primary_addresses AS
(SELECT home_addresses_working.id address_id, signups.id signup_id FROM
signups JOIN signups_working
ON signups_working.id = signups.id AND signups.primary_address_id = signups.address_id -- selecting what address is primary one
JOIN home_addresses_working
ON home_addresses_working.row_id = signups_working.row_id)
UPDATE signups_working SET primary_address_id = changed_primary_addresses.address_id FROM changed_primary_addresses WHERE signups_working.id = changed_primary_addresses.signup_id;
UPDATE signups_working SET address_id = home_addresses_working.id FROM home_addresses_working WHERE signups_working.row_id = home_addresses_working.row_id;---------[END]  signups: foreign_key is ready [signups_working/address_id -> home_addresses_working/id] ---------




---------[START]  signups: foreign_key is ready [signups_working/billing_address_id -> billing_addresses_working/id] ---------
WITH changed_primary_addresses AS
(SELECT billing_addresses_working.id address_id, signups.id signup_id FROM
signups JOIN signups_working
ON signups_working.id = signups.id AND signups.primary_address_id = signups.billing_address_id -- selecting what address is primary one
JOIN billing_addresses_working
ON billing_addresses_working.row_id = signups_working.row_id)
UPDATE signups_working SET primary_address_id = changed_primary_addresses.address_id FROM changed_primary_addresses WHERE signups_working.id = changed_primary_addresses.signup_id;
UPDATE signups_working SET billing_address_id = billing_addresses_working.id FROM billing_addresses_working WHERE signups_working.row_id = billing_addresses_working.row_id;---------[END]  signups: foreign_key is ready [signups_working/billing_address_id -> billing_addresses_working/id] ---------




---------[START]  addresses: finalize ---------
UPDATE addresses SET ("id", "updated_at", "zip", "lng", "lat", "geocode_accuracy", "fips", "address1", "address2", "city", "county", "state", "country_code", "geocode_city", "geocode_address1", "geocode_state", "geocode_zip", "geocode_country_code", "geocode_error_count", "street_name", "street_type", "unit_number", "identity_hash", "street_number", "street_prefix", "street_suffix", "unit_number_integer", "street_number_integer", "created_at", "import_id") = (registered_addresses_working.id, registered_addresses_working.updated_at, registered_addresses_working.zip, registered_addresses_working.lng, registered_addresses_working.lat, registered_addresses_working.geocode_accuracy, registered_addresses_working.fips, registered_addresses_working.address1, registered_addresses_working.address2, registered_addresses_working.city, registered_addresses_working.county, registered_addresses_working.state, registered_addresses_working.country_code, registered_addresses_working.geocode_city, registered_addresses_working.geocode_address1, registered_addresses_working.geocode_state, registered_addresses_working.geocode_zip, registered_addresses_working.geocode_country_code, registered_addresses_working.geocode_error_count, registered_addresses_working.street_name, registered_addresses_working.street_type, registered_addresses_working.unit_number, registered_addresses_working.identity_hash, registered_addresses_working.street_number, registered_addresses_working.street_prefix, registered_addresses_working.street_suffix, registered_addresses_working.unit_number_integer, registered_addresses_working.street_number_integer, coalesce(addresses.created_at, now()), 1) FROM registered_addresses_working WHERE registered_addresses_working.id = addresses.id;
---------[END]  addresses: finalize ---------




---------[START]  addresses: finalize ---------
UPDATE addresses SET ("id", "updated_at", "zip", "lng", "lat", "geocode_accuracy", "fips", "address1", "address2", "city", "county", "state", "country_code", "geocode_city", "geocode_address1", "geocode_state", "geocode_zip", "geocode_country_code", "geocode_error_count", "street_name", "street_type", "unit_number", "identity_hash", "street_number", "street_prefix", "street_suffix", "unit_number_integer", "street_number_integer", "created_at", "import_id") = (mailing_addresses_working.id, mailing_addresses_working.updated_at, mailing_addresses_working.zip, mailing_addresses_working.lng, mailing_addresses_working.lat, mailing_addresses_working.geocode_accuracy, mailing_addresses_working.fips, mailing_addresses_working.address1, mailing_addresses_working.address2, mailing_addresses_working.city, mailing_addresses_working.county, mailing_addresses_working.state, mailing_addresses_working.country_code, mailing_addresses_working.geocode_city, mailing_addresses_working.geocode_address1, mailing_addresses_working.geocode_state, mailing_addresses_working.geocode_zip, mailing_addresses_working.geocode_country_code, mailing_addresses_working.geocode_error_count, mailing_addresses_working.street_name, mailing_addresses_working.street_type, mailing_addresses_working.unit_number, mailing_addresses_working.identity_hash, mailing_addresses_working.street_number, mailing_addresses_working.street_prefix, mailing_addresses_working.street_suffix, mailing_addresses_working.unit_number_integer, mailing_addresses_working.street_number_integer, coalesce(addresses.created_at, now()), 1) FROM mailing_addresses_working WHERE mailing_addresses_working.id = addresses.id;
---------[END]  addresses: finalize ---------




---------[START]  addresses: finalize ---------
UPDATE addresses SET ("id", "updated_at", "zip", "lng", "lat", "geocode_accuracy", "fips", "address1", "address2", "city", "county", "state", "country_code", "geocode_city", "geocode_address1", "geocode_state", "geocode_zip", "geocode_country_code", "geocode_error_count", "street_name", "street_type", "unit_number", "identity_hash", "street_number", "street_prefix", "street_suffix", "unit_number_integer", "street_number_integer", "created_at", "import_id") = (home_addresses_working.id, home_addresses_working.updated_at, home_addresses_working.zip, home_addresses_working.lng, home_addresses_working.lat, home_addresses_working.geocode_accuracy, home_addresses_working.fips, home_addresses_working.address1, home_addresses_working.address2, home_addresses_working.city, home_addresses_working.county, home_addresses_working.state, home_addresses_working.country_code, home_addresses_working.geocode_city, home_addresses_working.geocode_address1, home_addresses_working.geocode_state, home_addresses_working.geocode_zip, home_addresses_working.geocode_country_code, home_addresses_working.geocode_error_count, home_addresses_working.street_name, home_addresses_working.street_type, home_addresses_working.unit_number, home_addresses_working.identity_hash, home_addresses_working.street_number, home_addresses_working.street_prefix, home_addresses_working.street_suffix, home_addresses_working.unit_number_integer, home_addresses_working.street_number_integer, coalesce(addresses.created_at, now()), 1) FROM home_addresses_working WHERE home_addresses_working.id = addresses.id;
---------[END]  addresses: finalize ---------