PostgreSQL auto-increment aren't incremented using COPY

Question

I have the following table in PostgreSQL:

CREATE TABLE cars (id SERIAL PRIMARY KEY,
                car_id SERIAL REFERENCES car_models (id) ON DELETE CASCADE);

When using COPY with the following:

COPY cars FROM '/Users/my-user/cars.csv' DELIMITER ',' CSV HEADER;

Containing:

id, car_id
1, 4
2, 3
3, 9

Then my primary key aren't incremented, so calling afterwards:

insert into cars (car_id) values (11)

fails with:

ERROR:  duplicate key value violates unique constraint "cars_pkey"
DETAIL:  Key (id)=(1) already exists.

Shawn.X · Accepted Answer · 2019-05-20 08:54:11Z

2

It's easy to solve this problem, as below, you can set the start value of the sequence after you copy data into your table (your_now_max_value, for example 123).

alter sequence cars_id_seq restart with your_now_max_value;

The script shell copy_cars.sh maybe has 4 lines as below:

psql -d database -c"copy cars from xx.txt with delimiter ','"

max_id=`psql -d database -c"copy(select max(id) from cars) to stdout"`

max_id=$(($max_id + 1))

psql -d database -c"alter sequence cars_id_seq restart with ${max_id}"

Of course, you can add some alert code to ensure the robustness.Then you can set a scheduler for the script to achieve your aim of twice a month.

edited May 20, 2019 at 8:54

answered May 20, 2019 at 8:25

Shawn.X

1,3637 silver badges17 bronze badges

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

12 Comments

Alfred Balle Over a year ago

I see, it isn't possible to do dynamicly, so I don't have to know max_value?

Shawn.X Over a year ago

The copy operation is frequently? so what way do you use to copy data into your table ? A shell script?

Alfred Balle Over a year ago

The COPY is not frequently, but it occurs once or twice each month, with 11 tables from export to CSV.

Shawn.X Over a year ago

The way above is OK. You can execute the SQL "select max(id) from cars" every time you copy the data into your table, and then "alter sequence cars_id_seq with max+1". you can just implement a shell script to complete the process.

Alfred Balle Over a year ago

Could one to ALTER sequence cars_pkey RESTART WITH SELECT MAX(id) from cars;

|

jarlh · Accepted Answer · 2019-05-20 08:42:49Z

0

There is a flag 'EXPLICIT_IDS' which is used for this purpose.

Try using

COPY cars FROM '/Users/my-user/cars.csv' DELIMITER ',' CSV HEADER EXPLICIT_IDS;

Hope this helps.

edited May 20, 2019 at 8:42

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

answered May 20, 2019 at 8:24

umair qayyum

2863 silver badges11 bronze badges

2 Comments

Alfred Balle Over a year ago

Not sure how that will respect auto-increment when doing import?

umair qayyum Over a year ago

does your file have ids in it?

Collectives™ on Stack Overflow

PostgreSQL auto-increment aren't incremented using COPY

2 Answers 2

12 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related