5

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?

membersound
  • 81,582
  • 193
  • 585
  • 1,120

1 Answers1

13

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)

  • Yes indeed I have multiple colums that are to be populated from csv. I tried leaving the `id` field blank during copy insert *without* specifying the column names in the copy command. So if I got you right, specifying the columns explicit and omitting the id does the trick? – membersound Apr 30 '15 at 09:19
  • @membersound: yes, if you leave out the column list, `copy` assumes that the input data contains **all** columns (this is similar to an `insert` with and without a column list) –  Apr 30 '15 at 09:23