Summary: in this tutorial, we will show various ways to import a CSV file into a PosgreSQL table.
We will create a new table named persons with the following columns:
-
id: the person id -
first_name: the first name of the person -
last_name:the last name of the person -
dobdate of birth of the person -
email: the email address of the person
We execute the following CREATE TABLE statement to create the persons table:
1 2 3 4 5 6 7 8 9 | 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) ) |
We also prepare a CSV data file by using Microsoft Excel. You can use any applications that available in your PC to do so.
The path of the CSV file is as follows: C:\tmp\persons.csv
Import CSV file into table using COPY statement
To import this CSV file into the persons table, you use COPY statement as follows:
1 2 | COPY persons(first_name,last_name,dob,email) FROM 'C:\tmp\persons.csv' DELIMITER ',' CSV HEADER; |
PosgreSQL gives back the following message:
Let’s check the persons table.
1 | SELECT * FROM persons; |
It works.
Let’s dive into the COPY statement in more detail.
First, you specify the table with column names after the COPY keyword. The order of the columns must the the same as in the CSV file. In case the CSV file contains all columns of the table, you don’t have to specify them explicitly, for example:
1 2 | COPY sample_table FROM 'C:\tmp\sample_data.csv' DELIMITER ',' CSV HEADER; |
Second, you put the CSV file path after the FROM keyword. Because CSV file format is used, you need to specify DELIMITER as well as CSV keywords.
Third, HEADER keyword indicates that the CSV file contains a header line with column names. When importing data, PostgreSQL ignores the first line, which is the header line of the file.
Notice that the file must be read directly by the PosgreSQL server, not by the client application. Therefore, it must be accessible to the PosgreSQL server machine. Also you can execute COPY statement successfully if you have superusers access.
Import CSV file into table using pgAdmin III
In case you don’t have superuser privilege or you need to import CSV file from a client machine into a table in PosgreSQL server, you can use pgAdmin GUI tool to do that.
Let’s remove all data of the persons table so that we can re-import data and see the effect.
1 | TRUNCATE TABLE persons; |
First, right click on the persons table, choose the import… menu item as follows:
Then, browse the the CSV file, choose format as CSV and click the Columns tab.
Next, uncheck the id column because we don’t import data into this column and click the Misc. Options
After that, check the Header checkbox because our CSV file has a header, choose comma (,) as the delimiter, and click Import button.
Finally, when the import process completes, click the Done button.
Let’s check the persons table again to see if the data is really imported.
Yes, it is.
In this tutorial, we have shown you how to import data from CSV file into a table using COPY statement as well as the pgAdmin tool.