1

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

  • 1
    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. – MrX May 27 '13 at 2:23
  • 1
    Importing data into a staging table is quite common. – Jakub Kania May 27 '13 at 20:56
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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.