Is it possible to load the data from your local system? Yes, Postgres provides assistance in this regard as well. The COPY command in Postgres allows you to import data from a local instance to a Postgres table. This article provides a step-by-step guide to copy data from a local system to a Postgres table.
How Postgres COPY statement works
The COPY statement is divided into further two categories:
COPY TO: This will copy the data of the table to a file.
COPY FROM: Used to copy data of a file into the table.
As we are considering here the copy from the local system to a Postgres table, thus in our case the COPY FROM statement would work. The upcoming section provides the usage of the COPY statement to import data from a file into a Postgres table.
How to copy data from a local system to a Postgres table
This section provides a step-by-step procedure to copy data from a local system into a Postgres table. To do so, we are creating a .CSV file as they are easy to import. There are two factors in a CSV file that must be considered to copy the data to a Postgres table. The factors are Header and Delimiter:
Header: This represents the head of each column
Delimiter: Character used to separate two entries and comma(,) is used in this regard. However, semicolon and tab may also be used to separate the database columns.
Step 1: Create a CSV file
Firstly, create a CSV file; we will be using CSV file named “staff.csv” and the snapshot of the data stored in our staff.csv file is displayed below:
Note: Any text file saved in .csv format will act as a CSV file.
It is observed that there are three columns with Header named as “id”, “name”, and “designation”. Based on the data observed from the file, a Postgres table is created. It seems that the id would be referred to as a primary key whereas the name and designation are in VARCHAR category.
Step 2: Create a Postgres table
Once you have created(or examined) the CSV file, you are good to go for creating a Postgres table. As we have to map the data of the CSV file, the table must contain the same columns. The following command creates a table named “employee” and three columns are created inside that table. Moreover, it is recommended to use the same data type for the table columns as you have observed from the file.
For verification, get the content of the employee table by issuing the command written below and the output shows that the table is empty.
Step 3: COPY from file to Postgres table
Once you have created a CSV file and Postgres table accordingly. To import a staff.csv file, the COPY command is executed in the following manner:
The output of the command written above would be the number of records copied from the file to the Postgres table. For further verification, you can get the content of the Postgres table:
And here you go with copying the data from the local system to the Postgres table.
Conclusion
Postgres is a widely used database management system for enterprises. It does support extensive querying mechanisms to perform database operations. This article provides the procedural guide to copy files from a local system to a Postgres table. The header used in the file must be the column names of the table. You have learned to create a CSV file and the copying phenomena as well. Lastly, we suggest that you create a Postgres table according to the .csv file in order to ensure that you can copy all the data without any ambiguity.
