From 5306429d51e7a203ea18eef38e9e7b3066997db3 Mon Sep 17 00:00:00 2001
From: Stevan Earl <stevan.earl@asu.edu>
Date: Wed, 19 Aug 2026 10:23:03 -0700
Subject: [PATCH] Fix Problem #113 no-focal biography conversion

---
 conversion/clean.sql | 65 ++++++++++++++++----------------------------
 1 file changed, 24 insertions(+), 41 deletions(-)

diff --git a/conversion/clean.sql b/conversion/clean.sql
index f233d56..d79f57e 100644
--- a/conversion/clean.sql
+++ b/conversion/clean.sql
@@ -140,47 +140,30 @@ UPDATE biography
  SET b_sex = 'M'
  WHERE b_animid = 'UNK';
 
--- Provide the code used when it does not make sense to record an individual.
-INSERT INTO biography (
-    b_animid
-  , b_animid_num
-  , b_animname
-  , b_birthgroup
-  , b_bgcertainty
-  , b_sex
-  , b_momid
-  , b_dadid
-  , b_dadid_publication_info
-  , b_firstborn
-  , b_birthdate
-  , b_bdmin
-  , b_bdmax
-  , b_bddist
-  , b_entrydate
-  , b_entrytype
-  , b_departdate
-  , b_departdateerror
-  , b_departtype)
-  VALUES (
-    'NONE'
-  , NULL
-  , 'NO FOCAL INDIVIDUAL'
-  , NULL
-  , 'U'
-  , 'U'
-  , NULL
-  , NULL
-  , NULL
-  , 'U'
-  , '1960-07-14'
-  , '1960-07-14'
-  , '1960-07-14'
-  , 'U'
-  , '1960-07-14'
-  , 'X'
-  , '2025-01-01'
-  , 0
-  , 'X');
+-- Problem #113
+-- Normalize the no-focal record supplied by the Access database.  Access is
+-- authoritative for the existence of this row, while the clean schema gives
+-- its placeholder fields values accepted by the PostgreSQL schema.
+UPDATE clean.biography
+  SET b_animid_num = NULL
+    , b_animname = 'NO FOCAL INDIVIDUAL'
+    , b_birthgroup = NULL
+    , b_bgcertainty = 'U'
+    , b_sex = 'U'
+    , b_momid = NULL
+    , b_dadid = NULL
+    , b_dadid_publication_info = NULL
+    , b_firstborn = 'U'
+    , b_birthdate = '1960-07-14'
+    , b_bdmin = '1960-07-14'
+    , b_bdmax = '1960-07-14'
+    , b_bddist = 'U'
+    , b_entrydate = '1960-07-14'
+    , b_entrytype = 'X'
+    , b_departdate = '2025-01-01'
+    , b_departdateerror = 0
+    , b_departtype = 'X'
+  WHERE b_animid = 'NONE';
 
 --
 -- Clean other biography rows
-- 
2.34.1