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
We execute the following CREATE TABLE statement to create the
CREATE TABLE persons
id serial NOT NULL,
first_name character varying(50),
last_name character varying(50),
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:
Import CSV file into table using COPY statement
To import this CSV file into the
persons table, you use
COPY statement as follows:
FROM 'C:\tmp\persons.csv' DELIMITER ',' CSV HEADER;
PosgreSQL gives back the following message:
Let’s check the
SELECT * FROM persons;
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:
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
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.
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.