How to Import CSV File Data Into a Table in PostgreSQL

A CSV file is a text file with a .csv extension with comma-separated contents. The file can achieve different objectives, such as loading data into a database table and importing data into Google and Excel spreadsheets.

While working with spreadsheets, you can also export the data to a CSV file and use the data in other functions.

This tutorial will demonstrate how to populate a table in a PostgreSQL database using a CSV file.

Step-By-Step Guide to Import CSV File Data Into a Table in PostgreSQL

1. Use the following command to log in to the PostgreSQL server. Enter your password in the prompt and press the Enter button.

   david@david-HP-ProBook-6470b:~$ psql -U postgres
   Password for user postgres:
   

2. Create a database where we will place the data from the CSV file.

   postgres=# create database csv_db;
   

3. Connect to the database csv_db.

   postgres=# \c csv_db;
   You are now connected to database "csv_db" as user "postgres".
   

4. Create a table named product with columns id, product_name, product_type, and product_price.

   csv_db=# CREATE table product(
   csv_db(# id SERIAL UNIQUE NOT NULL,
   csv_db(# product_name varchar(50),
   csv_db(# product_type varchar(50),
   csv_db(# product_price integer,
   csv_db(# PRIMARY KEY(id));
   CREATE TABLE
   

5. Create a CSV file and create some instances of the product table. You can name the file as data.csv or any name you like.

   Iphone 7,    500,  phone
   HP probook,  8000, computer
   Canon pixma, 3000,  printer
   

6. To copy the data from the CSV file to the product table, use the copy command, accompanied by the absolute path to the CSV file and the delimiter separating the columns. Since the id is auto-generated, we can specify the product_name, product_price, and product_type as the only fields we want to be inserted into the database.

   csv_db=# \copy product(product_name, product_price, product_type) FROM '/home/david/Documents/work/upwork/jhinku-tutorials/data.csv' DELIMITER ',' CSV;
   COPY 3
   

7. Execute the following query to confirm that we have successfully inserted the data into the product table.

   csv_db=# select * from product;
   

   Output:

    id | product_name | product_type | product_price
   ----+--------------+--------------+---------------
     1 | Iphone 7     |   phone      |           500
     2 | HP probook   |  computer    |          8000
     3 | Canon pixma  |   printer    |          3000
   (3 rows)