title: Converting XML into SQL database where items are duplicated
body: <p>I'm trying to convert a very large XML into an SQL database (PostgreSQL). It's constructed in a funny way (see below): there are ~ 100 million records of type (say) &quot;person&quot; with various personal data, including (say) a city record which has a bunch of data on the city. There are only ~ 10 thousand unique cities (say the name is unique). The goal is to create a <em>city</em> table with these 10⁴ rows and some <code>city_id</code> as primary key; and a <em>person</em> table with 10⁸ rows and <code>city_id</code> as a foreign key.</p>
  <p>The cities are not known in advance, the <em>city</em> table has to be constructed as the XML is parsed. So the naïve algorithm is to create these two tables with automatically incrementing indices, and then at each iteration:</p>
  <ol>
  <li>parse person</li>
  <li>parse their city</li>
  <li>check if city with [city name] already in <em>city</em> table
  <ol>
  <li>if yes, select its <code>city_id</code></li>
  <li>if no, insert and return <code>city_id</code></li>
  </ol>
  </li>
  <li>insert parsed person with the retrieved <code>city_id</code> into table.</li>
  </ol>
  <p>This is impractically slow however. It seems that too much effort is spent on this third step, to check if the city with the same name field already exists in the table. The 100 million records only have to be inserted once when converting from XML, later the table will grow more slowly.</p>
  <hr />
  <p>The alternative I had in mind is this: create <em>person</em> and <em>city</em> tables with initially no constraints at all. Then at each iteration:</p>
  <ol>
  <li>parse person</li>
  <li>parse city</li>
  <li>calculate <code>city_id</code> by hashing its name field</li>
  <li>insert city into table with the hash as <code>city_id</code> (not yet marked as primary key)</li>
  <li>insert person into table with the hash as <code>city_id</code> (not yet marked as foreign key).</li>
  </ol>
  <p>We end up with 10⁸ rows in the <em>city</em> table, but we can easily (?) delete the duplicates afterwards maybe with select distinct (I'm not sure that's the best tool though, since we are guaranteed that the hash is based on the name and is a unique identifier; maybe better to partition and select first row?). Then after the city table is shrunk, mark <code>city_id</code> as primary key in <em>city</em> table and foreign key in <em>person</em> table.</p>
  <p>Is there any problem with my suggested solution? Any better approach?</p>
  <hr />
  <p>This is the XML</p>
  <pre><code>&lt;person&gt;
      &lt;name&gt;Alice&lt;/name&gt;
      &lt;birth&gt;1991&lt;/birth&gt;
      &lt;city&gt;
          &lt;name&gt;London&lt;/name&gt;
          &lt;pop&gt;9000000&lt;/pop&gt;
          &lt;area unit=&quot;sqkm&quot;&gt;1600&lt;/area&gt;
      &lt;/city&gt;
  &lt;/person&gt;
  
  &lt;person&gt;
      &lt;name&gt;Bob&lt;/name&gt;
      &lt;birth&gt;1992&lt;/birth&gt;
      &lt;city&gt;
          &lt;name&gt;London&lt;/name&gt;
          &lt;pop&gt;9000000&lt;/pop&gt;
          &lt;area unit=&quot;sqkm&quot;&gt;1600&lt;/area&gt;
      &lt;/city&gt;
  &lt;/person&gt;
  
  &lt;person&gt;
      &lt;name&gt;Charlie&lt;/name&gt;
      &lt;birth&gt;1993&lt;/birth&gt;
      &lt;city&gt;
          &lt;name&gt;Edinburgh&lt;/name&gt;
          &lt;pop&gt;500000&lt;/pop&gt;
          &lt;area unit=&quot;sqkm&quot;&gt;300&lt;/area&gt;
      &lt;/city&gt;
  &lt;/person&gt;
  </code></pre>