🔗 Import a CSV into Postgres using Elixir

Analyzing large amounts of data has become an everyday problem. Oftentimes, the datasets are only available as CSV file, which creates the the question of how you can import them into your Postgres database? The short answer: By using the Postgres’ COPY-function. Here’s the long answer:

Let’s imagine you have an Ecto.Schema called Location with the following definition:

schema "locations" do
  field :name, :string
  field :latitude, :float
  field :longitude, :float
end

The locations table stores location data as latitude and longitude coordinates together with a name. For example, this could be a street address with a house number and its geocoded lat+long position.

Now, let’s imagine you have a CSV file called locations.csv with 100.000 rows of location data and you want to import the data into your locations table. In the following, we will use Postgres’ COPY-function for that. First, we will call it directly from a psql-session and then we will wrap it into a simple Mix.Task. Let’s go!