PostgreSQL copy command generate primary key id

postgresql

I have a CSV file with two columns: city and zipcode. I want to be able to copy this file into a PostgreSQL table using the copy command and at the same time auto generate the id value.

The table has the following columns: id, city, and zipcode.

My CSV file has only: city and zipcode.

Best Answer

The COPY command should do that all by itself if your table uses a serial column for the id:

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 you should be able to say:

copy table_name(city, zipcode) from ...

and the id will be generated as usual. If you don't have a serial column for id (or a manually attached sequence), then you could hook up a sequence by hand, do your COPY, and then detach the sequence.

Related Topic