data import from csv, auto-incremented big integer for ID

[semorur]

Describe the Bug

If I have a table with auto-incremented big integer for ID and when I import data with id numbers it doesn't restart sequence number. I tried to reproduce it with auto-increment integer, but it worked fine.

for example, when I have latest number 10, I import data with ID 11,12,13,14,15. When I add a new item there is error, because incremention remained at 10, not 15. So the next ID will be 11, but it's imposible because values have to be unique.

Also the same problem, when you allow editing ID and write in some numbers. for integer its is fine, for big integer it is not.

To Reproduce

1. add new data model with ID auto-incremented big integer (make ID field visible, for comfort)
2. add some fields
3. add some data (3 entry, for example)
4. export data to *.csv with all fields
5. open *.csv and change ID fields (if you had 1,2,3 - make it 5,6,7)
6. import *.csv
7. add new data (you will see, that it gets ID=4)
8. you will not be able to create next entry, because ID=5, but you already have ID=5

Directus Version

v.10.8.2

Hosting Strategy

Self-Hosted (Docker Image)

[rijkvanzanten]

@semorur What database are you using?

[semorur]

Postgresql

[rijkvanzanten]

@jaads This feels very similar to #6321 with fix #19328 🤔 Any thoughts?

[semorur]

yes, it looks like the same problem. This problem now exists for BIG integer. Usual integer doesn’t have the problem. It takes the maximum id value +1 automatically best regards, Sergey Morozov

…

28 нояб. 2023 г., в 18:18, Rijk van Zanten ***@***.***> написал(а): @jaads <https://github.com/jaads> This feels very similar to #6321 <#6321> with fix #19328 <#19328> 🤔 Any thoughts? — Reply to this email directly, view it on GitHub <#20550 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BEJMCZUWMIJMMJHSU2OXQULYGYFEZAVCNFSM6AAAAAA74V5NK2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMZQGIYTEMJSGI>. You are receiving this because you were mentioned.

[jaads]

Interesting. I just checked it. Currently the sequence will be reset only for integer primary keys, not big integer:

directus/api/src/services/items.ts

Line 191 in c357d8d

pkField!.type === 'integer' &&

Assuming the SQL statement to reset stays the same, regardless of the type, this would be easy to fix/extend.