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?

user330315 · Accepted Answer · 2015-04-30 09:15:21Z

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)

answered Apr 30, 2015 at 9:15

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

membersound Over a year ago

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?

user330315 Over a year ago

@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)

Collectives™ on Stack Overflow

How to increment primary key during postgres COPY batch insert?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related