How To Import CSV File in PostgreSQL

Last updated on May 17th, 2020 at 06:13 am

Often, you need to import CSV file into PostgreSQL table for querying and analysis. Here’s how to import CSV file in PostgreSQL, along with its header. We will use copy command to import CSV into PostgreSQL.

Table of Contents

How To Import CSV File in PostgreSQL

Here are the steps to import CSV file in PostgreSQL. After importing CSV file with header into PostgreSQL, you may want to use a postgresql reporting tool to query your PostgreSQL table and ensure everything is working well.

1. Create Table

First, we will create PostgreSQL table to import CSV. Let’s say you want to import CSV file which has 2 fields – order_date, sale

We will create a sales(order_date, sale) table using psql.

postgres=# create table sales(order_date date, sale int);

Bonus Read: How to Calculate Month over Month Growth in PostgreSQL

2. Import CSV file in PostgreSQL

Next, we import csv file into PostgreSQL using copy command.

Import CSV File Without Header in PostgreSQL

We have the following sales.csv file. Please use single/double quotes for date values in your CSV file, else you will get error.

'2020-01-01',15
'2020-01-02',25
'2020-01-03',34
'2020-01-04',22
'2020-01-05',30

postgres=# COPY sales FROM 'c:/files/sales.csv' WITH (FORMAT csv);

postgres=# select * from sales;
 order_date | sale
------------+------
 2020-01-01 |   15
 2020-01-02 |   25
 2020-01-03 |   34
 2020-01-04 |   22
 2020-01-05 |   30

Bonus Read: How to Create Pivot Table in PostgreSQL

Import CSV File With Header in PostgreSQL

In this case, we have the following sales.csv file which includes header. Please use single/double quotes for date values in your CSV file, else you will get error.

order_date,sale
'2020-01-01',15
'2020-01-02',25
'2020-01-03',34
'2020-01-04',22
'2020-01-05',30

postgres=# \COPY sales FROM 'c:/files/sales.txt' DELIMITER ',' CSV HEADER;

postgres=# select * from sales;
 order_date | sale
------------+------
 2020-01-01 |   15
 2020-01-02 |   25
 2020-01-03 |   34
 2020-01-04 |   22
 2020-01-05 |   30

Bonus Read : How to Calculate Retention Rate in SQL

You can use the same queries with CSV or TXT files. Please note, you may need superuser privileges to import CSV with header. That is why we use \COPY instead of only COPY, above. After you import CSV file, you can use a postgresql reporting tool like Ubiq to plot them in line/column charts as shown below.