Postgres concurrent copy without ID value?

Question

I am performing concurrent copy commands but am not specifying a value for a serial ID field. As far as I know this is ok if I have just one copy command since Postgres will generate an ID.

But would this cause conflicts with more than 1 copy command running since the sequence is never updated by a copy command?

Curry · Accepted Answer · 2013-10-05 16:00:59Z

1

copy command update id serial automatically. so, it works fine without id conflicts.

I test performing concurrent copy commands in postgresql 9.24

I create table like below

create table tbl_test (id serial primary key, name varchar(16), age integer);

I also made 2 csv file having 1,000,000 data.

file1.csv

"1", 1
"2", 2
...
"1000000", 1000000

file2.csv

"n1", 1
"n2", 2
...
"n1000000", 1000000

when I try to copy simultaneously from file1, I get result like below

...
1000245 | n453649 | 453649
1000246 | 546595  | 546595
1000247 | n453650 | 453650
1000248 | 546596  | 546596
1000249 | n453651 | 453651
1000250 | 546597  | 546597
...

all data copied well.

postgres=# select count(*) from tbl_test;
  count
---------
 2000000
(1 row)

answered Oct 5, 2013 at 16:00

Curry

8955 silver badges15 bronze badges

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

3 Comments

Henley Wing Chiu Over a year ago

I still need to update the serial id sequence after the copies are done, correct?

bma Over a year ago

Why do you think that? SERIAL and BIGSERIAL are by definition auto-incrementing as data is loaded into your table, so no extra processing is required to ensure they increment.

Curry Over a year ago

@bma is right. id sequence is automatically updated. you can see the updated value by 'select * from table_id_sequence_name'

bma · Accepted Answer · 2013-10-05 15:28:44Z

0

As long as the column has a sequence as a default (or is a SERIAL/BIGSERIAL datatype) and you are not referencing that directly in the COPY command you will not have ever have conflicts on that id.

Sequences are designed to be atomic, even within transactions, which also generates another common question "How do I get gapless sequences?"

answered Oct 5, 2013 at 15:28

bma

9,8562 gold badges35 silver badges22 bronze badges

Collectives™ on Stack Overflow

Postgres concurrent copy without ID value?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related