PostgreSQL – Import CSV File Into Table

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.