Three Ways to Import Data With Postgres
Learn the three best methods for importing data into your database using Postgres with step-by-step instructions.
Join the DZone community and get the full member experience.Join For Free
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. The fastest and easiest method 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 to the 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.
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.
Save it as
customers.csv and exit. We can now use the COPY command to insert the data into the
If you want to skip loading the serial column and want to load only
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:
Ensure that Postgres has access to the folder you used to make the backup. Now, to restore this table, use the command below.
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:
To restore the backup:
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 the third 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."
That's 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!
Published at DZone with permission of Igor Bobriakov. See the original article here.
Opinions expressed by DZone contributors are their own.