title: Postgresql: Insert from huge csv file, collect the ids and respect unique constraints
body: <p>In a postgresql database: </p>
  
  <pre><code>class Persons(models.Model):
      person_name = models.CharField(max_length=10, unique=True)
  </code></pre>
  
  <p>The <code>persons.csv</code> file, contains 1 million names.</p>
  
  <pre><code>$cat persons.csv
  Name-1
  Name-2
  ...
  Name-1000000
  </code></pre>
  
  <p>I want to:</p>
  
  <ol>
  <li>Create the names that do not already exist</li>
  <li>Query the database and fetch the id for each name contained in the csv file.</li>
  </ol>
  
  <p>My approach:</p>
  
  <ol>
  <li><p>Use the <a href="https://www.postgresql.org/docs/10/static/sql-copy.html" rel="nofollow noreferrer"><code>COPY</code></a> command or the <a href="https://github.com/california-civic-data-coalition/django-postgres-copy" rel="nofollow noreferrer">django-postgres-copy</a> application that implements it. </p>
  
  <p>Also take advantage of the new Postgresql-9.5+ <a href="https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#INSERT_..._ON_CONFLICT_DO_NOTHING.2FUPDATE_.28.22UPSERT.22.29" rel="nofollow noreferrer">upsert feature</a>.</p></li>
  <li><p>Now, all the names in the csv file, are also in the database. </p>
  
  <p>I need to get their ids -from the database- either in memory or in another csv file with an efficient way:</p>
  
  <ul>
  <li><p>Use <a href="https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.Q" rel="nofollow noreferrer"><code>Q</code></a> objects</p>
  
  <pre><code>list_of_million_q = &lt;iterate csv and append Qs&gt;
  million_names = Names.objects.filter(list_of_million_q)
  </code></pre>
  
  <p><strong>or</strong></p></li>
  <li><p>Use <a href="https://docs.djangoproject.com/en/dev/ref/models/querysets/#in" rel="nofollow noreferrer"><code>__in</code></a> to filter based on a list of names:</p>
  
  <pre><code>list_of_million_names = &lt;iterate csv and append strings&gt;
  million_names = Names.objects.filter(
      person_name__in=[list_of_million_names]
  )
  </code></pre>
  
  <p><strong>or</strong></p>
  
  <p>?</p></li>
  </ul></li>
  </ol>
  
  <p>I do not feel that any of the above approaches for fetching the ids is efficient. </p>
  
  <h2><strong>Update</strong></h2>
  
  <p>There is a third option, along the lines of <a href="https://stackoverflow.com/a/42217872/2996101">this post</a> that should be a great solution which combines all the above.</p>