title: Efficient Bulk INSERT/COPY into table from HashMap
body: <p><strong>Task:</strong></p>
  
  <p>Given this HashMap structure: <code>Map&lt;String, Map&lt;String, String&gt;&gt; mainMap = new HashMap&lt;&gt;()</code></p>
  
  <p>I want to <code>INSERT</code> or <code>COPY</code> each <em>value</em> of the <em>inner</em> <code>Map</code> into its own cell in my database.</p>
  
  <ul>
  <li>The <code>size()</code> of <code>mainMap</code> if 50,000. </li>
  <li>The <code>size()</code> of the inner <code>Map</code> is 50.</li>
  <li>The table to be inserted into has 50 columns. </li>
  <li>Each column's header is the key for the inner <code>Map</code>. </li>
  </ul>
  
  <p><strong>EDIT:</strong> Originally the user uploads a large spreadsheet with 35 of the 50 columns. I then "cleanse" that data with various formatting, and I add my own 15 new  pairs into the innerMap for each <code>mainMap</code> entry. I can't directly <code>COPY</code> from the user's source file to my database without cleansing/formatting/adding.</p>
  
  <p>Once I'm done iterating the spreadsheet and building <code>mainMap</code>, that's when I need to insert into my database table efficiently.</p>
  
  <p><strong>Research:</strong></p>
  
  <p>I've <a href="https://stackoverflow.com/questions/758945/whats-the-fastest-way-to-do-a-bulk-insert-into-postgres">read</a> that <code>COPY</code> is the best approach to initially bulk populate a table, however I'm stuck on whether my requirements warrant that command. </p>
  
  <p><a href="https://stackoverflow.com/a/6583841/4594511">This post</a> states that Postgres has a Prepared Statement parameter limit of 34464 for a query. </p>
  
  <p>I'm assuming I need 50 x 50,000 = 2,500,000 parameters in total. 
  This equals out to ~ 73 individual queries! </p>
  
  <p><strong>Question:</strong></p>
  
  <ul>
  <li>Is <code>COPY</code> the proper approach here instead of all these parameters?</li>
  <li>If so, do I convert the <code>HashMap</code> values into a <code>.sql</code> file, save it on disk on my web app server, and then reference that in my <code>COPY</code> command, and then delete the temp file? Or can I directly pass a concatenated <code>String</code> into it, without risking SQL injection?</li>
  </ul>
  
  <p>This command will be happening often, hence the need to be optimized.</p>
  
  <p>I can't find any examples of converting Java objects into compatible Postgres text file formats, so any feedback helps.</p>
  
  <p>How would you approach this problem?</p>
  
  <p><strong>Additional Info:</strong></p>
  
  <p>My table is pre-existing and can't be deleted since it's the back-end for my webapp and multiple users are connected at any given time.</p>
  
  <p>I understand temporarily removing indexes prior to using <code>COPY</code> can increase performance, but I'm only requiring max 50,000 rows to be inserted or copied at a time, not millions. </p>
  
  <p>StackExchange told me to ask here.</p>