DEV Community

Cover image for Three ways to import data into Postgres
Arctype Team for Arctype

Posted on • Originally published at

Three ways to import data into Postgres

What is a database without data? Postgres provides multiple options to import data from files. You can use the COPY command, restore from a backup, or load in a CSV with a SQL GUI tool. Which method is fastest and easiest will depend on your setup and how the data exists right now.

Method 1: Using the COPY command for Postgres

The COPY command can import data to Postgres if access to text, CSV, or binary format data. For example, the COPY TO command outputs the content of a table to a file. You can also use the COPY FROM command to load the data from a file to the table. The file mentioned in the COPY command must be accessible by the Postgres user and should be specified from the perspective of the Postgres server.

The command can also use a SELECT query to load data to a table. It also allows you to specify a list of columns to insert the data into instead of the whole table. On successful completion, the COPY command outputs result in the format COPY count, denoting the number of rows inserted by the command.

Both the text and the CSV file formats allow you to specify a delimiter. But if your input is CSV, it is better to use the CSV format with the DELIMITER option rather than the TEXT format since CSV format adheres with commonly accepted CSV escaping mechanism. By contrast, the TEXT format follows Postgres-specific escaping rules.

Want to see the COPY command in action? Follow along with the steps below. The first step is to use the CREATE TABLE command to create a table.

CREATE TABLE customer(
id serial,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50)
Enter fullscreen mode Exit fullscreen mode

The above command results in a customer table with four columns. Now, create a CSV file in the below format. You can do this in Excel or any other spreadsheet-type program.

Serial First Name Last Name Email
1 John Michael
2 Mary Cooper
3 Sheldon Cooper

Save it as customers.csv and exit. We can now use the COPY command to insert the data into the customer table.

COPY customer FROM '/home/data/customer.csv' DELIMITER ',' CSV HEADER;
Enter fullscreen mode Exit fullscreen mode

If you want to skip loading the serial column and want to load only first_name, last_name, and email You can use the command below instead.

COPY customer(first_name,last_name,email) FROM '/home/data/customers1.csv' DELIMITER ',' CSV HEADER;
Enter fullscreen mode Exit fullscreen mode

The COPY command has many optional parameters that you can use to customize its behavior. Some of the important ones are listed below:

  • QUOTE: Specify the character used to quote the data values.
  • NULL: Specifies the character used to represent the NULL value.
  • ESCAPE: Specifies the character used to escape a character that is being used as the QUOTE character.
  • ENCODING: Used to describe the encoding of the file. If nothing is mentioned, it defaults to client encoding.

This documentation link lets you learn more about the COPY command's parameters.

Method 2: Restoring from a backup file using psql

Another way of importing data to Postgres is to use the psql command. This method is meant to be used when the input file is a plain text file in .sql format. This command is most useful for restoring backups of data.

Let's try it out. First, we'll make a backup of the table that we just created by executing the command below:

pg_dump -d postgres -t customer > /tmp/customer.sql
Enter fullscreen mode Exit fullscreen mode

Ensure that Postgres has access to the folder you used to make the backup. Now, to restore this table, use the command below.

psql -d postgres < /tmp/customer.sql
Enter fullscreen mode Exit fullscreen mode

If the backup file format is not .sql and is another format (like .tar), you have to use the pg_restore command to restore the backup. In that case, use the command below to create the backup:

pg_dump -d postgres -t customer -F t  > /tmp/customer.tar
Enter fullscreen mode Exit fullscreen mode

To restore the backup:

pg_restore -d postgres -t customer /tmp/customer.tar
Enter fullscreen mode Exit fullscreen mode

Viola - your data is now imported! Now that we are clear about importing data to a Postgres table using shell commands, let's check out a third, easier option - using the Arctype UI to create and load a table.

Method 3: Using a Postgres client to create tables and import CSVs

Using the Arctype UI to create tables and import data takes just a few clicks. First, download Arctype and connect your database. You can follow the database connection guide for step-by-step instructions on how to connect Arctype to a Postgres database. You can also follow the documentation posted here for a quick-start tutorial.

Once you're all set up, select "Table" in the left navigation pane and click on the three dots close to the search bar.

Arctype screenshot

Select Import CSV to Table and verify the data in the CSV file. Click "Accept."

Arctype screenshot

Enter the name of the table and the schema where you want to create the table. Click "Import CSV."

Arctype screenshot

Thats it! You have now successfully imported a CSV file to Arctype.


You've now seen some of the ways you can import data into Postgres. As a robust DBMS, Postgres provides options. For example, you can use the COPY command or pg_restore to import your data without a GUI. You can also use Arctype to import a table through its user interface in just a few clicks. With all these options to choose from, you just need to decide which is the best for you. Good luck!

Top comments (2)

appurist profile image
Paul / Appurist

The Arctype download for Windows appears to have been removed, the link here and all download links just take you to the home page where there doesn't seem to be any opportunity to download a client. Is that the same thing you see? Are you aware of any other place to get a copy or perhaps this is just no longer an option? They seem to have quite a bit of documentation on how to use it, including installation instructions, but not to actually download it. All download links just go to the home page (at least on Windows).

andrewbaisden profile image
Andrew Baisden

Cool guide great info.