Import CSV File into PostgreSQL Table (Easy Methods)

By /
Featured

PostgreSQL is an open-source relational database system, which is an ideal platform for developers to perform data manipulation and analysis. One of the primary jobs while writing queries and extracting insights from datasets is to import CSV to add data to PostgreSQL tables. In this tutorial, you will learn how to import a CSV file in PostgreSQL database using multiple methods.

How to Import CSV to PostgreSQL?

To move further in this tutorial, you’ll need:

  • A CSV file that contains data that needs to be imported into PostgreSQL.
  • A table in PostgreSQL with a defined structure that will store the CSV file data.

In this tutorial, we are going to use the below CSV file, named Sample.csv:

Sample.csv

To create a table on PostgreSQL named Sale, we can use the below query:

CREATE TABLE Sale(
Series_reference VARCHAR(50),
Period VARCHAR(10),
Data_value NUMERIC
);Code language: SQL (Structured Query Language) (sql)

Now that we have created the table and we have the CSV file, we can now easily import CSV to PostgreSQL via below methods.

Importing CSV File Using psql Command Line

Ensure that you have PostgreSQL superuser access, to successfully use the COPY command for implementing the PostgreSQL import task.

Here are the steps you need to follow and then you will be able to import any CSV file to PostgreSQL.

Step 1: Select a CSV File

Choose the CSV file that you want to import and copy its location. For this, let’s take the file location of the Sales table.

C:\Users\aadis\Downloads\business-financial-data-december-2023-quarter
Location
Location

Step 2: Open psql Command Line

You can open the psql command line from the start bar.

Opening The SQL Shellpsql
Opening psql Command Line

You will be able to connect to PostgreSQL as long as you are entering the correct password.

Step 3: Create a Table

Now we will create the table named Sale, which we have defined earlier.

CREATE TABLE
CREATE TABLE

Step 4: Load the CSV File into a Table

You can use the below query to import the CSV File:

\COPY Sale FROM 'C:\Users\aadis\Downloads\business-financial-data-december-2023-quarter\Sample.csv' WITH CSV HEADER;Code language: JavaScript (javascript)

Step 5: Verify Data Import

You can verify that the data has been imported by running the following query:

SELECT * FROM Sale;Code language: SQL (Structured Query Language) (sql)
Sale
Sale

And this is how you upload a CSV file in PostgreSQL using the COPY command.

Note: If you are uploading a huge file, it can take a lot of time to convert from CSV to Tabular Format in PostgreSQL.

Importing CSV File Using pgAdmin

pgAdmin is an open-source tool for PostgreSQL database management. Importing a CSV file using pgAdmin is a self-explanatory way of importing a CSV file.

Here are the steps you need to follow and then you will be able to import any CSV file to PostgreSQL.

Step 1: Launch pgAdmin and Set the Connection

pgAdmin
Select Existing Connection or Create a new server

Step 2: Select a Database You Want to Work On

Database
Select a Database to work on

In the above example, we have used the Postgres database, you can either choose an existing database or create a new one. For creating a new database here is the query:

CREATE DATABASE databasename;Code language: SQL (Structured Query Language) (sql)

Step 3: Create a Table

Right-click on the Tables option present inside the Schema section and click on the Create option and hover over it to click on the Table option.

Table pgAdmin
Table pgAdmin

Step 4: Entering the Details

Wizard
Enter the table-specific details: Table name, Column Names etc

Once filled in the data, click on the Save button.

Step 5: Right Click Over the Table and Select Import/Export Data

Import
Click on Import/Export Data

By clicking on Import/Export Data, a pop-up window will appear.

Step 6: Importing the Table Using Import Wizard

Import Data
Enter the CSV Path

Enter the CSV path name and file format as CSV

In the options button toggle on the header button and specify “,” as the delimiter for the CSV file.

Delimeter
Click on the OK button

You will see a pop-up window on your screen like this:

Done
Now click on the View Processes

Finally, you will notice the Status as Finished which means your data is successfully imported.

Data Imported
Data Imported

Summary

In this tutorial, you have learned how you can import a CSV file in the PostgreSQL database using pgAdmin and psql command line. You can also read about how to Import CSV files in MySQL Database and how to export MySQL tables to a CSV File. We hope you enjoyed it.

Reference

https://stackoverflow.com/questions/60999939/how-can-i-import-my-csv-file-in-to-postgres