Repository: nycdb/nycdb (language: python)

Issue:
hpd_ll44 "-" not parsed as null for integer
```
  File "/app/load_dataset.py", line 424, in <module>
    main()
  File "/app/load_dataset.py", line 420, in main
    load_dataset(dataset)
  File "/app/load_dataset.py", line 357, in load_dataset
    ds.db_import()
  File "/usr/local/lib/python3.10/site-packages/nycdb/dataset.py", line 89, in db_import
    self.sql_files()
  File "/usr/local/lib/python3.10/site-packages/nycdb/dataset.py", line 157, in sql_files
    self.db.execute_sql_file(f)
  File "/usr/local/lib/python3.10/site-packages/nycdb/database.py", line 60, in execute_sql_file
    self.sql(f.read())
  File "/usr/local/lib/python3.10/site-packages/nycdb/database.py", line 33, in sql
    curs.execute(SQL)
  File "/usr/local/lib/python3.10/site-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
psycopg.errors.InvalidTextRepresentation: invalid input syntax for type integer: "-"
```

Not sure which table this is happening on. Also a quick look on open data and I'm not seeing any `-` except in the text field `Borrowerlegalentityname`

- https://data.cityofnewyork.us/Housing-Development/Local-Law-44-Building/hu6m-9cfi/data_preview
- https://data.cityofnewyork.us/Housing-Development/Local-Law-44-Projects/ucdy-byxd/data_preview
- https://data.cityofnewyork.us/Housing-Development/Local-Law-44-Tax-Incentive/72vt-ykjc/data_preview


Discussion / hints:
Now that I have the whole traceback it looks like it's actually coming from the SQL, this line:
```UPDATE hpd_ll44_buildings SET boroid = nullif(substr(bin, 1, 1), '')::int;```

Looks like there are a bunch of records (new construction, so probably no maybe no bin assigned yet?) where `bin` is `-`.

Fix patch:
diff --git a/src/nycdb/sql/hpd_ll44.sql b/src/nycdb/sql/hpd_ll44.sql
index 8593dea..df25944 100644
--- a/src/nycdb/sql/hpd_ll44.sql
+++ b/src/nycdb/sql/hpd_ll44.sql
@@ -1,5 +1,5 @@
 ALTER TABLE hpd_ll44_buildings ADD COLUMN boroid int;
-UPDATE hpd_ll44_buildings SET boroid = nullif(substr(bin, 1, 1), '')::int;
+UPDATE hpd_ll44_buildings SET boroid = nullif(nullif(substr(bin, 1, 1), ''), '-')::int;
 
 CREATE INDEX hpd_ll44_buildings_bbl_idx on hpd_ll44_buildings (bbl);
 CREATE INDEX hpd_ll44_buildings_projectid_idx on hpd_ll44_buildings (projectid);


Test patch:
diff --git a/src/tests/integration/data/hpd_ll44_buildings.csv b/src/tests/integration/data/hpd_ll44_buildings.csv
index 946eb61..81a6eea 100644
--- a/src/tests/integration/data/hpd_ll44_buildings.csv
+++ b/src/tests/integration/data/hpd_ll44_buildings.csv
@@ -1,6 +1,6 @@
 ProjectID,BuildingID,ReportingConstructionType,Borough,Block,Lot,BIN,HouseNumber,StreetName,CountedRentalUnits,CountedHomeownershipUnits,AllCountedUnits,TotalBuildingUnits,BaseSquareFootage,Stories,BBL,Community Board,Council District,Census Tract,NTA,Latitude,Longitude,Postcode
 44218,987329,New Construction,,1790,1,1054691,2319,3 AVENUE,297,0,297,404,35436,19,1017900046,11,8,242,MN34,40.804265,-73.93539,10035
-44223,927737,New Construction,,1556,3,3000000,335,RALPH AVENUE,0,13,13,13,1989,7,3015560003,3,41,301,BK79,40.677644,-73.921745,11233
+44223,927737,New Construction,,1556,3,-,335,RALPH AVENUE,0,13,13,13,1989,7,3015560003,3,41,301,BK79,40.677644,-73.921745,11233
 44223,969695,New Construction,,1709,10,3000000,35,ROCHESTER AVENUE,0,8,8,8,1720,4,3017090010,3,36,299,BK61,40.677925,-73.927466,11233
 44223,975702,New Construction,,1709,27,3000000,18-22,SUYDAM PLACE,0,15,15,15,2778,4,3017090027,3,36,299,BK61,40.677823,-73.926554,11233
 44223,977564,New Construction,,1556,8,,329/331,RALPH AVENUE,0,10,10,10,2925,5,,,,,,,,
diff --git a/src/tests/integration/test_nycdb.py b/src/tests/integration/test_nycdb.py
index 819ebd0..7568787 100644
--- a/src/tests/integration/test_nycdb.py
+++ b/src/tests/integration/test_nycdb.py
@@ -874,6 +874,13 @@ def test_hpd_ll44(conn):
         assert rec['bbl'] == '1017900046'
         assert rec['boroid'] == 1
 
+    with conn.cursor(row_factory=dict_row) as curs:
+        curs.execute("select * from hpd_ll44_buildings WHERE buildingid = 927737")
+        rec = curs.fetchone()
+        assert rec is not None
+        assert rec['bin'] == "-      " # space because char(7)
+        assert rec['boroid'] == None
+    
     assert row_count(conn, 'hpd_ll44_projects') > 0
     assert has_one_row(conn, "select 1 where to_regclass('public.hpd_ll44_projects_projectid_idx') is NOT NULL")
     with conn.cursor(row_factory=dict_row) as curs: