How To Import CSV File Data Into A PostgreSQL Table

CSV stands for “comma separated value” and CSV files are commonly used to mitigate compatibility issues when sharing files across different applications and platforms. CSV files are plain text files that can be opened in almost any spreadsheet application or database management system, including PostgreSQL.

PostgreSQL is among the most popular database management systems on the market as it is simple to set up and use. For programmers learning PostgreSQL, there are plenty of resources and tutorials online to help you out as you get started. If you work in tech, you'll probably end up working with PostgreSQL at some point or another and, inevitably, you'll need to learn to import CSV file data into a PostgreSQL table.

While this may sound like a complicated process, it's actually a very straightforward task that typically only takes a few minutes. We'll provide a brief tutorial below.

Create Your Table

Before you can import CSV file data, you need to create your PostgreSQL table. For this example, we'll keep things simple. Let's say you're creating a database of patient information for a hospital that lists names, ages, and addresses.

For this example, you would write:

CREATE TABLE patients (

id SERIAL

first_name VARCHAR (50)

last_name VARCHAR (50)

age INT

address VARCHAR (50)

(If you want a more in-depth discussion of creating SQL tables and what the above syntax means, we have touched on the basics here.)

Again, CSV stands for “comma separated values,” meaning information is typically separated using a comma. To prepare a CSV data file to fit into the above format, you would write something like:

First Name, Last Name, Age, Address

Tom, Smith, 34, 123 Rochester Ave

When opened, this CSV would show up as:

From here, you can either import your CSV file data either using a COPY statement or pgAdmin.

Using A COPY Statement

As is, the path to your current CSV file is “C:sampledbpatients.csv”. To import this into your patients file in PostgreSQL, you would write the a COPY statement using the following syntax:

COPY patients (first_name, last_name, age, address)

FROM C:sampledbpatients.csv

Delimiter ‘,'

CSV Header;

What does this mean, exactly? Let's break it down:

• COPY: This is the command that tells you where to copy to or from, essentially giving the address of the CSV file in question
• FROM: This specifies what will be imported from said file
• DELIMITER: The delimiter refers to the character used to separate different values. In almost all cases, the delimiter will be a comma when working with CSV files
• HEADER: This signifies that you have a header in your CSV file, which should be ignored while importing the first row.

One quick note – sometimes, your file will not include a header. In this case, simply do not include HEADER in your syntax.

If you receive the PostgreSQL message “COPY 2” it means the rows have been successfully copied.

Using pgAdmin

Sometimes, you may be unable to import a CSV file using a copy statement. If the file is saved on your computer, you may need to use pgAdmin instead. This is a fairly straightforward process that should only take a few minutes.

PgAdmin is a free, open-source graphical PostgreSQL database management program that can be used on Linux, macOS, Windows, and Unix. With pgAdmin, you can do everything from issuing and analyzing queries to administering databases to creating database objects. Using pgAdmin, you can easily import a CSV file. In fact, it's actually easier than using a COPY statement, as you can skip the query altogether and just use the import/export menu.

On the pgAdmin dashboard, right click the table in question. In our example that would be the “patients” table. From there, select “Import/Export.” A window will then appear, and you can set the slider to “Import.”

Select your source file from your computer and set the format to CSV. If your file has a header, set the header to “Yes.” Then, set your delimiter, which will typically be a comma. From there, simply click “OK” and the data should be imported.

The Bottom Line

While the above is only a brief tutorial, it should give you foundational knowledge of how to import CSV files when working with PostgreSQL. There are always some exceptions to the above rules, which may require more specific syntax or commands.

The more you use CSV files and PostgreSQL, the more familiar you will get with more advanced techniques for importing files and more. As PostgreSQL is a popular database management system, there are ample online resources to help get you through the initial learning curve.

Looking for solutions for your company? Xano is the fastest No Code Backend development platform on the market. We give you a scalable server, a flexible database, and a No code API builder that can transform, filter, and integrate with data from anywhere. Sign up here to get started.

The post How To Import CSV File Data Into A PostgreSQL Table appeared first on Xano.