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)
);
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 | |
|---|---|---|---|
| 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 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 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.
Select Import CSV to Table and verify the data in the CSV file. Click "Accept."
Enter the name of the table and the schema where you want to create the table. Click "Import CSV."
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!
Latest comments (2)
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).
Cool guide great info.