PostgreSQL - Import CSV File Into Table
Last Updated : 20 Aug, 2024
Importing data from a CSV (Comma-Separated Values) file into a PostgreSQL database is a common task for database administrators and developers. PostgreSQL provides the COPY command, which is a powerful and efficient way to bulk load data directly from a CSV file into a table.
In this article, we will discuss the process of importing a '.csv' file into a PostgreSQL table.
Steps to Import CSV Data into PostgreSQL
Let us look at the steps of Importing CSV Data into a PostgreSQL Table.
Step 1: Creating a Target Table in PostgreSQL
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)
);
This table will store personal information such as 'first_name', 'last_name', 'dob' (date of birth), and 'email'.
Step 2: Preparing the CSV File
Now we create a '.csv' file in our sheet manager (eg: MS Excel or notepad) as shown below. The structure of your CSV file should match the columns in the 'persons' table.
Our file is located as 'persons.csv' at 'C:\Users\Raju'.
Step 3: Importing the CSV File into PostgreSQL
To import the CSV file into the 'persons' table, you can use the 'COPY' statement. This statement reads the data from the CSV file and inserts it into the specified table.
COPY persons(first_name, last_name, dob, email)
FROM 'C:\Users\Raju' DELIMITER ', ' CSV HEADER;
Step 4: Verifying the Data Import
After running the 'COPY' statement, you can verify that the data has been imported successfully by querying the 'persons' table:
SELECT * FROM persons;
It will lead to the below Output:
Important Considerations When Importing CSV Files into PostgreSQL
- 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.