     l30_amount,
     l60_amount,
     l90_amount,
-    total_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
+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;
 
-\echo Decode period_code...
-
-update aged_creditor_facts set financial_year = cast(left(period_code, 4) as int) where financial_year is null;
-update aged_creditor_facts set amount_type_code = substr(period_code, 5) where substr(period_code, 5) in ('IBY1', 'IBY2', 'ADJB', 'ORGB', 'AUDA', 'PAUD') and amount_type_code is null;
-update aged_creditor_facts set amount_type_code = 'ACT' where substr(period_code, 5) not in ('IBY1', 'IBY2', 'ADJB', 'ORGB', 'AUDA', 'PAUD') and amount_type_code is null;
-update aged_creditor_facts set period_length = 'year' where substr(period_code, 5, 3) not similar to 'M\d{2}' and period_length is null;
-update aged_creditor_facts set period_length = 'month' where substr(period_code, 5, 3) similar to 'M\d{2}' and period_length is null;
-update aged_creditor_facts set financial_period = cast(right(period_code, 2) as int) where period_length = 'month' and financial_period is null;
-update aged_creditor_facts set financial_period = cast(left(period_code, 4) as int) where period_length = 'year' and financial_period is null;
-
-\echo Add back not null constraints...
-
-alter table aged_creditor_facts
-      alter column financial_year set not null,
-      alter column amount_type_code set not null,
-      alter column period_length set not null,
-      alter column financial_period set not null;
-
 COMMIT;

BEGIN;

\echo Create import table...


\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 Delete demarcation_code-period_code pairs that are in the update

DELETE FROM aged_creditor_facts f WHERE EXISTS (
