How to Import CSVs to PostgreSQL Using PgAdmin

CSV is a file format that displays tabular records in plain text. Each entry in the records is separated from another by commas. This is the origin of the name CSV (Comma Separated Values). CSVs are used to transfer data across incompatible database programs, such as between open-source databases and proprietary database formats.

PostgreSQL is a Data Warehouse that stores data from Web-based and Mobile Applications. This database system is preferred by many developers because it is compatible with both Relational Databases and Non-Relational Databases.

PgAdmin is a Graphical User Interface built for interaction with PostgreSQL databases. This tool helps in the PgAdmin Import CSV process. This tool contains icons that allow PostgreSQL users to navigate database querying, storage, and application building on PostgreSQL databases.

If you run a tech company, you are probably storing your data in a proprietary format to secure it. Nevertheless, you might want to share some of your data with your subscribers. With an Open-Source database like PostgreSQL to store your app data, you won’t be able to import your data directly from your company database. This is because PostgreSQL databases are incompatible with proprietary database formats. 

Therefore, you need a file system like CSV, which supports most database formats to perform the transfer. Once you store your data from your company database in a CSV, you can implement the method, PgAdmin Import CSV.

What is PostgreSQL?

When creating an application, you need a data warehouse to build your app and store important information from the software. If you are a new developer, you may not be able to afford a paid data warehouse. 

PostgreSQL is a database system that offers services to businesses for free. Business owners can create and modify PostgreSQL databases at no charge. PostgreSQL also allows app creators to scale their databases according to the quantity of data and number of registered users on the application. 

Why Should you Use PostgreSQL?

• Free to Use: PostgreSQL allows its users to enjoy all its services for free. Even if you want to expand your database. 
• Reliable: PostgreSQL has provided secure database management services for over 25 years. The service is also renowned for fixing bugs as soon as they are reported. So, you can rest assured that the records in your database won’t be compromised.
• Data Security: PostgreSQL protects your databases with secure locking mechanisms. One example is the table-level lock, which restricts secondary users from adding new data to the tables.

What is PgAdmin?

PgAdmin is a Graphical User Interface (GUI) that allows businesses to import data into PostgreSQL databases. With this service, you can convert CSV files into acceptable PostgreSQL database formats, and import the CSV into your PostgreSQL format. You can also use PgAdmin to transform PostgreSQL databases into CSV files and export them from the system.

Just like PostgreSQL, PgAdmin is free to use. It also supports a variety of operating systems like Mac, Windows, and Linux Operating Systems. But before installing PgAdmin to your system, you must already have a PostgreSQL account. This is because you’ll need a PostgreSQL account to use PgAdmin. Any action you take in PgAdmin will automatically reflect in your PostgreSQL database. 

What does a CSV Look Like?

A CSV file stores tabular data in simple text. Each row in a table occupies a single line in CSV, and the entries in the row are known as fields. Commas separate the field in a record from each other. These comma separators are the origin of the name ‘CSV’ (Comma Separated Values).

Now, let’s look at a CSV file in practice.

Take, for instance, a table of student records:

First Name	Last Name	Grade
James	Sullivan	54
Clara	Matthews	73
Peter	Garricks	81

The CSV file for this table will look like this:

First Name, Last Name, Grade
James, Sullivan, 54
Clara, Matthews, 73
Peter, Garricks, 81

How to Import CSV into PostgreSQL?: PgAdmin Import CSV

Before carrying out the process of PgAdmin Import CSV into PostgreSQL, you must first convert the file into a compatible format for PostgreSQL databases. There are two ways to do this. You can either:

• Create a table in PgAdmin and match the column headers in the table to the columns in your CSV.
• Write an SQL Query that references your CSV file path.

Let’s understand both methods for PgAdmin Import CSV in detail.

1) Creating a PgAdmin Table that Matches Your CSV File

To import CSV using this PgAdmin Import CSV method, you have to do the following:

• Click on the Tools tab at the top of your PgAdmin Home Page.
• Select the Query Tool in the drop-down menu that appears.
• Enter the title and columns in your CSV file as an SQL Query. In this tutorial, you’ll use a student record file as an example. 

Let’s say your CSV file with the filename ‘ students.csv’ looks like this:

First Name, Last Name, Grade
1, James, Sullivan, 54
2, Clara, Matthews, 73
3, Peter Garricks, 81

Your SQL Query for your PgAdmin table should be:

CREATE TABLE  students
(id serial,
first_name james,
last_name Sullivan,
grade 54 
);

At the end of this PgAdmin Import CSV method, the entire CSV file should now appear as a table in your PgAdmin table list. Click on the File tab at the top of the page to find the table. 

This is how you can use the PgAdmin Import CSV option to insert data with ease.

2) Writing an SQL Query that references your CSV file path

You can also carry out PgAdmin Import CSV by writing an SQL Query that references your CSV file. This method requires you to use the COPY command to import data from the CSV file into PgAdmin.

Here’s how to do it: 

• Open the Internal Query tool by clicking on the ‘Tools’ tab at the top of your screen. 
• Write your SQL Query. The SQL Query for the CSV file ‘students.csv’ would take this format:

COPY students
FROM ‘C: astudents.csv 
DELIMITER ‘,’
CSV Header;

The ‘students’ entry after copy refers to the CSV filename in the query above. The entry written after FROM is the CSV file path. 

DELIMITER refers to the comma that separates the fields in the CSV file. The CSV Header instructs the query to skip the header (the column titles) when importing the CSV file. 

• Now, click on the Query Editor tab at the top of the page. The SQL Query you just created will appear. Then, tap on Messages to view your results.
• Since the sample CSV file we used has 3 columns, you should see the message 

COPY 3

After applying your preferred method to import the CSV file, you still need to confirm that the query was successful. To do this, you need to enter the following query in the Query tool:

SELECT  *  FROM students

To verify this PgAdmin Import CSV method, click on the Query Editor tab. In the Query Editor, tap Data Output. Your query is successful if the CSV file appears in a tabular format. 

This is how you can use the PgAdmin Import CSV option to insert data by specifying a file path.

Conclusion

You have just carried out PgAdmin Import CSV methods to import CSV into the PostgreSQL database. Storing your databases on PostgreSQL is a convenient way to manage company data. In case you want to export data from a source of your choice into your desired Database/destination like PostgreSQL, then Hevo Data is the right choice for you! 

Visit our Website to Explore Hevo

Hevo Data provides its users with a simpler platform for integrating data from 100+ sources for Analysis. It is a No-code Data Pipeline that can help you combine data from multiple sources. You can use it to transfer data from multiple data sources into your Data Warehouses such as PostgreSQL, Database, or a destination of your choice. It provides you with a consistent and reliable solution to managing data in real-time, ensuring that you always have Analysis-ready data in your desired destination.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Share your experience of learning about how to PgAdmin Import CSV! Let us know in the comments section below!