How to Import CSV File Data Into a Table in PostgreSQL

David Mbochi Njonge Feb 02, 2024
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
    
  1. 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
    
  2. 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)
    
David Mbochi Njonge avatar David Mbochi Njonge avatar

David is a back end developer with a major in computer science. He loves to solve problems using technology, learning new things, and making new friends. David is currently a technical writer who enjoys making hard concepts easier for other developers to understand and his work has been published on multiple sites.

LinkedIn GitHub

Related Article - Postgres Table