 
 \copy aged_creditor_upsert (demarcation_code, period_code, item_code, g1_amount, l1_amount, l120_amount, l150_amount, l180_amount, l30_amount, l60_amount, l90_amount, total_amount) FROM '/home/jdb/proj/code4sa/municipal_finance/datasets/2017q1/cred_2017q1_acrmun.csv' DELIMITER ',' CSV HEADER;
 
-\echo Drop not null constraints...
-
-alter table aged_creditor_facts
-      alter column financial_year drop not null,
-      alter column amount_type_code drop not null,
-      alter column period_length drop not null,
-      alter column financial_period drop not null;
-
 \echo Delete demarcation_code-period_code pairs that are in the update
 
 DELETE FROM aged_creditor_facts f WHERE EXISTS (

BEGIN;

\echo Create import table...

    l30_amount,
    l60_amount,
    l90_amount,
    total_amount,
    financial_year,
    amount_type_code,
    period_length,
    financial_period
)
SELECT demarcation_code,
       period_code,
       lpad(item_code, 4, '0'),
       g1_amount,
       l1_amount,
       l120_amount,
       l150_amount,
       l180_amount,
       l30_amount,
       l60_amount,
       l90_amount,
       total_amount,
       cast(left(period_code, 4) as int),
       case when substr(period_code, 5) in ('IBY1', 'IBY2', 'ADJB', 'ORGB', 'AUDA', 'PAUD')
               then substr(period_code, 5)
           when period_code ~ '\d{4}M\d{2}'
               then 'ACT'
       end,
       case when period_code ~ '\d{4}M\d{2}'
                then 'month'
            when period_code ~ '\d{4}(IBY1|IBY2|ADJB|ORGB|AUDA|PAUD)'
                then 'year'
       end,
       case when period_code ~ '\d{4}M\d{2}'
                then cast(right(period_code, 2) as int)
            when period_code ~ '\d{4}(IBY1|IBY2|ADJB|ORGB|AUDA|PAUD)'
                then cast(left(period_code, 4) as int)
       end
FROM aged_creditor_upsert i;

COMMIT;
