How to import data from a CSV file into a PostgreSQL database
I was working on my personal project last week and needed to import some data in CSV format to a PostgreSQL database. I wanted only certain fields to be imported so I had to specify this in the command. I’m new to PostgreSQL so it took me quite a bit of Googling to figure out exactly how to do this.
Here’s how I did it on Ubuntu 10.04 LTS, ‘glucosetracker’ is the name of my database‘:
psql glucosetracker \COPY glucose_glucose(owner_id, glucose_value, category_id, record_date, record_time, notes, created_on, last_updated_on) from '/home/jc/Downloads/jc_glucose_data.csv' DELIMITERS ',' CSV;<br>
Note that ‘COPY is not exactly the same as ‘\COPY’. The COPY command runs under the PostgreSQL backend user (default ‘postgres’) so this user will need read access to your CSV file. The \COPY command runs under the user executing the command. I was getting permission denied errors my first few tries because I was using ‘COPY’ instead of ‘\COPY’ since I thought they were they same thing.