Open In App

PostgreSQL – Import CSV File Into Table

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

In this article, we will discuss the process of importing a .csv file into a PostgreSQL table.

To do so we will require a table which can be obtained using the below command:

CREATE TABLE persons
(
  id serial NOT NULL,
  first_name character varying(50),
  last_name character varying(50),
  dob date,
  email character varying(255),
  CONSTRAINT persons_pkey PRIMARY KEY (id)
);

Now we create a .csv file in our sheet manager (eg: MS Excel or notepad) as shown below:

Our file is located as persons.csv at C:\Users\Raju
Example :
To import this CSV file into the persons table, you use COPY statement as follows:

COPY persons(first_name, last_name, dob, email) 
FROM 'C:\Users\Raju' DELIMITER ', ' CSV HEADER;

Now, let us check the person table as follows:

SELECT * FROM persons;

It will lead to the below Output:

It is important to put the CSV file path after the FROM keyword. Because CSV file format is used, you need to mention the DELIMITER as well as ‘CSV’ keywords. The HEADER keyword indicates that the CSV file comprises a header line with column names. When importing data, PostgreSQL neglects the first line as they are the header line of the file.

The file must be read directly by the PostgreSQL server and not by the client application. Therefore, it must be accessible to the PostgreSQL server machine. Also, you can execute the COPY statement successfully if you have superusers access.


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads