One of the primary jobs of data practitioners and database administrators is PostgreSQL import CSV to add data to PostgreSQL tables. PostgreSQL is an open-source relational database that is an ideal pltform for developers to build applications.
This article delves into four distinct methods for CSV file import into PostgreSQL, a pivotal process for improving the data readability and enhancing the data analysis process.
The first method involves using Hevo Data’s automated data pipeline tool to replicate your data into your PostgreSQL destination seamlessly. The other three methods cover importing CSV into PostgreSQL using the COPY command, \copy command, and pgAdmin to import CSV files into PostgreSQL.
How to Import CSV to PostgreSQL?
Before you move forward with performing the PostgreSQL import CSV job, you need to ensure the following 2 aspects:
- A CSV file containing data that needs to be imported into PostgreSQL.
- A table in PostgreSQL with a well-defined structure to store the CSV file data.
In this article, the following CSV file is considered to contain the data given below:
Employee ID,First Name,Last Name,Date of Birth,City
1,Max,Smith,2002-02-03,Sydney
2,Karl,Summers,2004-04-10,Brisbane
3,Sam,Wilde,2005-02-06,Perth
You can create a table “employees” in PostgreSQL by executing the following command:
CREATE TABLE employees(
emp_id SERIAL,
first_name VARCHAR(50),
last_name VARCHAR(50),
dob DATE,
city VARCHAR(40)
PRIMARY KEY(emp_id)
);
After creating the sample CSV file and table, you can now easily import CSV to PostgreSQL via any of the following methods:
Method 1: Perform PostgreSQL Import CSV Job using Hevo
Hevo Data is a no-code data pipeline solution that can help you move data from 150+ data sources like FTP/SFTP & Google Sheets to your desired destination such as PostgreSQL, Data Warehouses, or BI tools in a completely hassle-free & automated manner. Using Hevo you can easily upload files in formats such as CSV, JSON, and XML to your PostgreSQL Database.
Get Started with Hevo for Free
To effortlessly perform the PostgreSQL import CSV job using Hevo, follow the simple steps given below:
- Step 1: Connect Hevo Data with your system by setting FTP/SFTP as a source. You can provide a unique name to your Pipeline along with information such as Port Number, Username, Password, etc. Hevo currently supports CSV, JSON, and XML formats. You can specify the file format as CSV and the Delimiter as “,”.
- Step 2: Complete the Postgres import CSV job by providing your PostgreSQL database credentials such as your authorized Username and Password, along with information about your Host IP Address and Port Number value. You will also need to provide a name for your database and a unique name for this destination.
Method 2: Perform PostgreSQL Import CSV Job using the COPY Command
To successfully use the COPY command for executing the PostgreSQL import CSV task, ensure that you have PostgreSQL Superuser Access.
- Step 1: Run the following command to perform the PostgreSQL import CSV job:
COPY employees(emp_id,first_name,last_name,dob,city)
FROM ‘C:newdbemployees.csv’
DELIMITER ‘,’
CSV HEADER;
Output:
COPY 3
On successful execution of PostgreSQL import CSV job, the Output “COPY 3” is displayed meaning that 3 records have been added to your PostgreSQL table.
The above COPY command has the following essential aspects:
- employees(emp_id, first_name, last_name, dob, city): “employees” is the name of the table where you want to import the data. Specifying the column names is optional if the order of the columns is maintained.
- ‘C:newdbemployees.csv’: This is the location of the CSV file stored on your system. You can change it according to your computer.
- DELIMITER: This is the character that determines how the values in the rows of a CSV file are separated. In the above example, the Delimiter is a comma i.e. “,”. At times, values are separated by characters like ‘|’ or tabs (t). In the case of a tab(t) delimiter, you can use the “DELIMITER E’t’ ” where ‘E’ allows for the tab character to be recognized.
- CSV: This option specifies that data is imported from a CSV file.
- HEADER: This option is used to let PostgreSQL know that the CSV file contains headers i.e the column names “First Name, Last Name, City”. Now, the CSV file data is imported from the second row onwards.
- Step 2: You can print out the contents of the table to check if the data is entered correctly.
SELECT * FROM employees;
Output:
emp_id first_name last_name dob city
1 Max Smith 2002-02-03 Sydney
2 Karl Summers 2004-04-10 Brisbane
3 Sam Wilde 2005-02-06 Perth
Also Read: How to Export a PostgreSQL Table to a CSV File
Method 3: Perform PostgreSQL Import CSV Job using pgAdmin
pgAdmin is an open-source tool for effortlessly managing your PostgreSQL Database. You can easily download it from the official pgAdmin website. You can also perform the PostgreSQL import CSV task via pgAdmin by following the simple steps given below:
- Step 1: You can directly create a table from the pgAdmin GUI(Graphical User Interface). Open pgAdmin and right-click on the Tables option present in the Schema section on the left side menu.
- Step 2: Hover over the Create option and click on the “Table…” option to open a wizard for creating a new table.
- Step 3: You can now enter the table-specific details such as Table Name, Column Names, etc. Once done, you can click on the Save button to create a new table.
- Step 4: Now for performing the Postgres import CSV job, go to the “Schemas” section on the left side menu and click on the Tables option.
- Step 5: Navigate to the “employees” table and right-click on it. Click on the Import/Export option to open the Wizard.
- Step 6: Toggle On the Import/Export flag button to import the data. Specify the filename as “employees” and the file format as CSV. You can toggle on the header button and specify “,” as the delimiter for your CSV file. Switch to the Columns Tab from the Options Tab to select columns and the order in which they need to be imported.
- Step 7: Click on the OK button. A window will pop up on your screen showing the successful execution of the PostgreSQL import CSV job using pgAdmin.
For a detailed step-by-step walkthrough of this process, please refer to our helpful guide on pgAdmin import CSV.
Method 4: Perform PostgreSQL Import CSV using \copy Command (psql Meta-Command)
The \copy command can be used for PostgreSQL import to CSV when you are interactively working with ‘psql’ and want to import data directly from your local machine or a specified file path.
- Step 1: Use ‘psql’ to Connect to your Database
psql postgres://<username>:<password>@<host>:<port>/<database>
# for example
psql postgres://postgres:postgres@localhost:5432/postgres
- Step 2: Import the Data from the CSV file
Once connected to psql, you can use the \copy command to import the data. Make sure to specify the correct path to your CSV file.
\COPY employee_table FROM 'employees.csv' WITH CSV HEADER;
- Step 3: Verify Data Import
After running the \COPY
command, you can verify that the data has been imported by querying the table:
SELECT * FROM employee_table;
You can exit the ‘psql’ by using the ‘\q’ command.
Limitations of Manually performing PostgreSQL Import CSV Job
Though the above 3 methods allow you to manually execute the PostgreSQL import CSV task, there are some challenges that you might face along the way:
- There is no scope for Data Standardization (Data Transformation & Cleaning) while using these manual methods.
- To bring data in real-time, you would need to write custom code to import data from your CSV files as soon as new data arrives.
- To achieve a reliable zero-loss data transfer, you would need to invest a section of your engineering bandwidth that continuously manages & maintain the data flow. Also, in case you are integrating data from other sources, constant effort is required to monitor the ever-changing data connectors.
Thus, an automated no-code data pipeline tool such as Hevo Data can be the ideal choice for PostgreSQL import CSV to transfer the data seamlessly and quickly.
Conclusion
In this article, you have learned how to effectively perform the PostgreSQL import CSV task via 4 different methods. If you require analysis-ready high-quality Data in Real-time from several sources, then a Cloud-based ETL tool like Hevo Data is the Right Choice for you!
However, if you rarely import data into PostgreSQL and don’t need to perform data transformations and cleaning operations, then the other three manual data integration methods will work.
Visit our Website to Explore Hevo
Hevo also supports PostgreSQL as a source for loading data to a destination of your choice. Hevo’s fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the Hevo Pricing details to understand which plan fulfills all your business needs.
Tell us about your experience of performing the PostgreSQL import CSV task! Share your thoughts with us in the comments section below.
Sanchit Agarwal is a data analyst at heart with a passion for data, software architecture, and writing technical content. He has experience writing more than 200 articles on data integration and infrastructure.