How Copy Statement of PostgreSQL to import data from File ?

In previous article we have explained about the how to import the data from different file in oracle.In this article I would like to explain more about the Copy statement of PostgreSQL with multiple examples. The Copy statement of PostgreSQL is used to import the data from the file. There are basic steps to import the data from file in to PostgreSQL database. The Copy command is specifically used to import the data from multiple files.

Copy Statement of PostgreSQL Syntax and Examples :

In this section I would like to give the syntax and examples of copy statement of PostgreSQL.

Syntax :

Copy “Table_Name”, Column_1,Column_2……..Column_N)

from ‘Path_Of_File’ DELIMITER ‘Delimeter_Symbol’ Type Of File [HEADER];

The Copy Command Copies the data from the file separated by delimiter and places the data in multiple columns of the table. If the file type is csv then we require to use the HEADER keyword else HEADER keyword is not mandatory.

Prerequisite :

We require to place the CSV file or any other text file in the installed PostgreSQL directory.

For Example :

If PostgreSQL is installed in program files location then following will be the location where you can put the file ,

Program Files–> PostgreSQL –> 10 –> Data–> Data_Copy

The reason why we have copied the files inside the installation directory is because SQL server will not able to read the files from your local system. Lets assume that the Files named Customer_file1 and Customer_file2 have the customer data and which are placed in data_copy folder.

The Command Will be,

Copy Customer cust_id,cust_name from “c:\Program Files\PostgreSQL\10\Data\Data_Copy\Customer_file1.csv” Delimiter ‘,’ CSV header;

Output :

Query returned successfully in 76 secs

The above command will insert the data from Customer_file1.csv to Table named Customer.

Example 2 :

If the file is text file Customer_File2.txt. We require to copy the data from text file in the table. We do not want to use the HEADER keyword. The command will be,

Copy Customer cust_id,cust_name from “c:\Program Files\PostgreSQL\10\Data\Data_Copy\Customer_file2.txt” Delimiter ‘,’ CSV;

The above command will insert the data from text file to Customer table.

We can change the delimiter from comma to pipe or any other symbol. But we require to give the correct delimiter in the file.

Using the Copy command of PostgreSQL we can import the data from table. Hhope