Import a CSV file into PostGIS

« Back to Recipes for typical tasks

---

First, make sure your data table is completely ready to go! PostGIS is very strict about integers, floats, and text fields being in the right places. Every row of your table must adhere to the table definition or the import will fail.

Note, you can do everything using SQL commands, or you can point/click your way through the pgAdmin tool. Whichever you prefer. Here's how I do it.

1. Create a new table in the SCHEMA that you want it to go in. The default is the scratch schema. You can declare other schemas using the format schemaname.tablename:

   CREATE TABLE scratch.new_taz_table (
     taz 	integer NOT NULL PRIMARY KEY,
     thing1      text,
     output1     double precision
   );

2. Import your CSV data by right-clicking the newly-created table node in pgAdmin and choosing Import.

   • Browse to your file
   • Choose format csv
   • On the Misc. Options pane, select header if your file has a header row
   • Also select , as the delimiter

3. Every table needs to have a "primary key" which must be unique. If none of your fields make sense as a primary key, add one after the CSV import is complete, using:

   ALTER TABLE schema.mytable ADD COLUMN id SERIAL PRIMARY KEY;

4. You will probably want to add an index or two, so postgres can find rows keyed to specific fields. Add indexes for any field that you think you'll be doing frequent selects or joins.

   • For regular columns, use this:

     CREATE INDEX my_col_index ON mytable (colname);

   • For geospatial queries, add a PostGIS-style "GIST" geospatial index:

     CREATE INDEX my_geo_index ON mytable USING GIST (geom_column_name);