I'm importing CSV files into a relatively simple PostgreSQL database. (The CSVs are sometimes created manually from information in a book, sometimes created from spreadsheets with a little massaging via scripting.) I do it in small batches to make sure things are going ok, and I've noticed that when COPY fails, it doesn't roll back the sequence attached to the column in the table. (Note: I am importing via pgAdmin.)
For example, let's say this is my table:
TABLE
------------------
id | data1 | data2
------------------
1 | abc | def
2 | ghi | klm
Then let's say I try to import two more columns and it fails. I fix the error, and then the import succeeds. I'd expect the table to look like this:
TABLE
------------------
id | data1 | data2
------------------
1 | abc | def
2 | ghi | klm
3 | nop | qrs
4 | tuv | wxy
Instead, it looks like this:
TABLE
------------------
id | data1 | data2
------------------
1 | abc | def
2 | ghi | klm
5 | nop | qrs
6 | tuv | wxy
The tables all rely on each other (i.e. pretty much every table has a FK pointing to the ID of some other table), so if the IDs stay predictable, my data entry job gets a lot easier. If not, I have to keep double checking the IDs when I finish a section.
Is there any way to prevent this behavior?
Best Answer
The sequences are designed like that, the are not supposed to produce contiguous numbers.
However, you can reset them before (re)trying the
COPY
with