Importing data from a CSV file into a Postgres table


Simple CSV file import

You have a CSV file called “data.csv”. It has a header line, and is delimited using “;”. You want to import it into Postgres and a table called “your_table”:

Create the database table. Set column-types so the string fields in the CSV file, can be cast to values in columns.

CREATE TABLE your_table
(
  -- Your columns
);

Execute COPY command:

COPY your_table FROM '/path/to/csv/file/data.csv' WITH DELIMITER ';' CSV HEADER;

If the data is geospatial

Another option is using ogr2ogr, which has a CSV driver. The COPY command could be faster.

If you have point data, check out this howto: Loading Point Data from a CSV File into PostGIS

, ,

5 responses to “Importing data from a CSV file into a Postgres table”

  1. I am using pgAdmin III. All I want to do is import a small table from Excel into my PostgreSQL database. I saved the table as a CSV. How do I import this? I don’t even see where to type the code you posted above. I am very new to this. Why is such a basic thing so seemingly hard to execute?

    • Hi David

      You execute it like a query. You can use PgAdmin III for that. Connect to a database that contains a table that matches your CSV file, and press the “SQL” button that gives you the SQL query editor and write the query there. Hope this helps.

      Kostas

      • It doesn’t work in pgadmin at all. you can write stored procedure to do it and then select procedure() if you want to use pgadmin. Another way is to use psql – then you can write those queries directly.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.