How to get .csv file into postGIS

Question

I am using this:

COPY data_table (col1,col2,col3) FROM 'datafile.csv' DELIMITERS ',' CSV HEADER;

to get csv data into my postGIS table. It works wonderfully, except I need to have a 'pk_id serial PRIMARY KEY' in the database table as well, which will then make the columns not match up between 'data_table' and 'datafile.csv'. And I think this will give an error when I run the COPY command.

I could add the pk_id serial PRIMARY KEY after the import, but I want to run many imports into data_table so this doesn't seem like it would work.

So my question is: what is an alternative way to get csv data in - can I still use COPY? Is there a way like in mySQL to "INSERT INTO table_name (col1, col2,col3) VALUES (val1,val2,val3) where I specify the affected database columns?

Thanks

• MrX: I think what I'm going to try (unless anyone thinks there would be a simpler solution) is to create a 'master' data table and copy everything into it from 'data_table', then truncate 'data_table' as the data in it will only be held temporarily. 'master' will have an autoincrementing id field.
• Jakub Kania: Importing data into a staging table is quite common.

Answer 1

GDAL using a .vrt file could also be a solution.

You vrt would look like:

<OGRVRTDataSource> 
<OGRVRTLayer name="feature_name"> 
 <SrcDataSource>your_csv.csv</SrcDataSource> 
 <GeometryType>wkbPoint</GeometryType> 
 <LayerSRS>EPSG:27700</LayerSRS> 
 <GeometryField encoding="PointFromColumns" x="Eastings" y="Northings"/> 
</OGRVRTLayer> 

Then simply:

ogr2ogr -progress -nln table_name_doesnt_need_to_exist -skipfailures PostgreSQL PG:"dbname='dbname' host='localhost' port='5432' user='username' password='password'" vrt_filename.vrt

For a full guide see:

Loading CSV OS CodePoint Data into PostGIS

Answer 2

I think this will give an error

No, you can definitely do this. From the docs:

If a list of columns is specified, COPY will only copy the data in the specified columns to or from the file. If there are any columns in the table that are not in the column list, COPY FROM will insert the default values for those columns.

So, even if there's a pk_id column, you can still list out your CSV's columns in the COPY command, exactly as you showed us in your question. The pk_id will default to the next value in the sequence, as expected.