Three ways to import data into Postgres
Tags: programming, postgres, tutorial, database
Published: 2022-04-01 16:56:47

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](https://arctype.com). 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](https://www.postgresql.org/docs/13/sql-copy.html) 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](https://www.geeksforgeeks.org/postgresql-text-data-type/) 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)
);
```

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 |john@gmail.com|
|2 |Mary |Cooper |mcooper@gmail.com |
|3 |Sheldon |Cooper |scooper@gmail.com|

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;
```

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;
```

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](https://www.postgresql.org/docs/13/sql-copy.html) 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
```

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
```

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
```

To restore the backup:

```
pg_restore -d postgres -t customer /tmp/customer.tar
```

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](http://arctype.com) and connect your database. You can follow the [database connection guide](https://arctype.com/postgres/connect/client-postgres) for step-by-step instructions on how to connect Arctype to a Postgres database. You can also follow the documentation posted [here](https://docs.arctype.com/getting-started/connecting-to-a-database) 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](https://lh6.googleusercontent.com/xQqfVgrNhX_O3ONQa168MnBqjYokFWdRhJf5co5e8UJqoLks7eVkbo6zVUmakUjBYvzwas9-5BGBEpyptU_nfy-Qsge6EABHbNQDdOxwRLP7uBPiQ9oWVSjqi5P_PYZdkDDGJRCE)

Select [Import CSV](https://docs.arctype.com/managing-tables/importing-a-csv) to Table and verify the data in the CSV file. Click "Accept."

![Arctype screenshot](https://lh5.googleusercontent.com/A9e_F3Rh8S7-2lvrfXZeA0GXbdfrAScfaqqdQftAYYGuaFky50hWAXDT_nnWD8lo8VAGnN9ACSOqmqEujLC9E3F1fTFAAWLY5D4SeBNiDXuEi6pmLml9h_Qpf9oPBIBe9uXIMOeA)

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

![Arctype screenshot](https://lh3.googleusercontent.com/a21Y05hxzYEfnNOnm7Eqj_b_LiUH6UqOZ7-9yx33SxG2UeVBz_cZEHiGehTK-GcYVt8xsHJEatT584EjiMhFRogFXE9k0MdyLHy8iSJN3zroe0D6fmeNdIHAAvbh0UUNbBHROXbx)

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

## Conclusion

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!