faa.load

LOAD CSV
FROM 'MASTER.TXT'
HAVING FIELDS (
       n_number,
       serial_number,
       mfr_mdl_code,
       eng_mfr_mdl,
       year_mfr,
       type_registrant,
       name,
       street,
       street2,
       city,
       state,
       zip_code,
       region,
       county,
       country,
       last_action_date,
       cert_issue_date,
       certification,
       type_aircraft,
       type_engine,
       status_code,
       mode_s_code,
       fract_owner,
       air_worth_date,
       other_names_1,
       other_names_2,
       other_names_3,
       other_names_4,
       other_names_5,
       expiration_date,
       unique_id,
       kit_mfr,
       kit_model,
       mode_s_code_hex,
       ignored)
INTO postgresql:///orbital?faa_registration
TARGET COLUMNS (
       n_number,
       serial_number,
       mfr_mdl_code,
       eng_mfr_mdl,
       year_mfr,
       type_registrant,
       name,
       street,
       street2,
       city,
       state,
       zip_code,
       region,
       county,
       country,
       last_action_date,
       cert_issue_date,
       certification,
       type_aircraft,
       type_engine,
       status_code,
       mode_s_code,
       fract_owner,
       air_worth_date,
       other_names_1,
       other_names_2,
       other_names_3,
       other_names_4,
       other_names_5,
       expiration_date,
       unique_id,
       kit_mfr,
       kit_model,
       mode_s_code_hex)
WITH truncate,
skip header = 1,
fields terminated by ','
AFTER LOAD DO $$ UPDATE faa_registration SET n_number = CONCAT('N', n_number); $$
;