How to increment primary key during postgres COPY batch insert?

Question

I have a table with serial id constraint:

id serial NOT NULL,
CONSTRAINT pricing_cache_pkey PRIMARY KEY (id)

Now I want to use the postgres COPY command to batch insert csv data into the table.

COPY tablename FROM STDIN WITH CSV;

Problem: the csv file if course does not have an ID column. How can I batch insert the rows and automatically increment the IDs in the database?

Answer 1

The copy command allows you to specify which columns to populate. If you omit the id column, it will be populated with the values from the sequence:

copy pricing_cache (column_1, column_2) from stdin with csv
col_1_value,col_2_value
col_1_value,col_2_value
\.

You haven't shown us your complete table definition. The above statements assumes that there are two columns named column_1 and column_2 in your table (in addition to the id column) and that the input data contains values for those two columns separated by a comma (because of the with csv)