I am writing large CSVs to Postgres tables. The columns are a mix of datatypes. Some are numeric and some of these numeric columns contain empty cells.
Here is an example row. (You'll have to click "Run code snippet" to see a decent view of the example row.)
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>cert</th><th>docket</th><th>fed_rssd</th><th>rssdhcr</th><th>name</th><th>city</th><th>stalp</th><th>zip</th><th>repdte</th><th>rundate</th><th>bkclass</th><th>address</th><th>namehcr</th><th>offdom</th><th>offfor</th><th>stmult</th><th>specgrp</th><th>subchaps</th><th>county</th><th>cbsa_metro</th><th>cbsa_metro_name</th><th>estymd</th><th>insdate</th><th>effdate</th><th>mutual</th><th>parcert</th><th>trust</th><th>regagnt</th><th>insagnt1</th><th>fdicdbs</th><th>fdicsupv</th><th>fldoff</th><th>fed</th><th>occdist</th><th>otsregnm</th><th>inst.webaddr</th><th>P3GTYPAR</th><th>P3GTY</th><th>P3GTYGNM</th><th>P9GTYPAR</th><th>P9GTY</th><th>P9GTYGNM</th><th>NAGTYPAR</th><th>NAGTY</th><th>NAGTYGNM</th></tr></thead><tbody>
<tr><td>16419</td><td>0</td><td>419255</td><td>1055780</td><td>1st Bank & Trust</td><td>Broken Bow</td><td>OK</td><td>74728</td><td>12/31/1992</td><td>8/4/2016</td><td>NM</td><td>210 North Broadway</td><td>SOUTHEASTERN BANCSHARES, INC.</td><td>3</td><td>0</td><td>0</td><td>8</td><td>0</td><td>Mccurtain</td><td> </td><td> </td><td>5/6/1946</td><td>5/6/1946</td><td>10/13/1997</td><td>Stock</td><td>0</td><td>1</td><td>FDIC</td><td>BIF</td><td>Dallas</td><td>DALLAS</td><td>Tulsa</td><td>Kansas City</td><td>Southern</td><td>West</td><td>http://www/1stbankandtrust.com</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td></td></tr>
</tbody></table>When I use this command to write to the table
\COPY all_reports_19921231_past_due_and_nonaccrual_loans_wholly_or_pa FROM 'C:\Data\04-09-2018-upload\FDIC\All_Reports_19921231\All_Reports_19921231_- Past Due and Nonaccrual Loans Wholly or Partially US Gvmt Guaranteed - Copy.csv' DELIMITER ',' CSV HEADER;
I get this error
CONTEXT: COPY all_reports_19921231_past_due_and_nonaccrual_loans_wholly_or_pa, line 4, column cbsa_metro: ""
I have tried replacing the empty cells with the word "NULL" (and "null") in Excel before trying to copy to Postgres, but I still hit the same error.
The table's schema does not say NOT NULL -- so that isn't the issue. (The column in question, for example says: "cbsa_metro numeric,")
What is the best way to replace empty cells with a null value that a numeric datatype column can handle?
COPY ... WITH NULL ''(two single quotes) - that should tell it to consider "empty" fields as NULL