The index column created with .to_sql() is dependent on the batch_size when writing
MEMBER
Thanks for reporting, @lsb. 

We are investigating it.

On the other hand, please note that in the next `datasets` release, the index will not be created by default (see #5583). If you would like to have it, you will need to explicitly pass `index=True`. 
2023-03-20T13:16:07Z
https://github.com/huggingface/datasets/issues/5649#issuecomment-1476216774
https://api.github.com/repos/huggingface/datasets/issues/5649
IC_kwDODunzps5X_UfG
https://api.github.com/repos/huggingface/datasets/issues/comments/1476216774/reactions
2023-03-20T13:16:07Z
https://api.github.com/repos/huggingface/datasets/issues/comments/1476216774
https://avatars.githubusercontent.com/u/8515462?v=4
https://api.github.com/users/albertvillanova/events{/privacy}
https://api.github.com/users/albertvillanova/followers
https://api.github.com/users/albertvillanova/following{/other_user}
https://api.github.com/users/albertvillanova/gists{/gist_id}
https://github.com/albertvillanova
albertvillanova
MDQ6VXNlcjg1MTU0NjI=
https://api.github.com/users/albertvillanova/orgs
https://api.github.com/users/albertvillanova/received_events
https://api.github.com/users/albertvillanova/repos
https://api.github.com/users/albertvillanova/starred{/owner}{/repo}
https://api.github.com/users/albertvillanova/subscriptions
User
https://api.github.com/users/albertvillanova
public
NONE
I think this is low enough priority for me to close this as Won't Fix. If I need any primary keys I can generate them beforehand. Feel free to reopen.
2023-06-17T07:01:51Z
https://github.com/huggingface/datasets/issues/5649#issuecomment-1595650492
https://api.github.com/repos/huggingface/datasets/issues/5649
IC_kwDODunzps5fG7G8
https://api.github.com/repos/huggingface/datasets/issues/comments/1595650492/reactions
2023-06-17T07:01:51Z
https://api.github.com/repos/huggingface/datasets/issues/comments/1595650492
https://avatars.githubusercontent.com/u/45281?v=4
https://api.github.com/users/lsb/events{/privacy}
https://api.github.com/users/lsb/followers
https://api.github.com/users/lsb/following{/other_user}
https://api.github.com/users/lsb/gists{/gist_id}
https://github.com/lsb
lsb
MDQ6VXNlcjQ1Mjgx
https://api.github.com/users/lsb/orgs
https://api.github.com/users/lsb/received_events
https://api.github.com/users/lsb/repos
https://api.github.com/users/lsb/starred{/owner}{/repo}
https://api.github.com/users/lsb/subscriptions
User
https://api.github.com/users/lsb
public
### Describe the bug

It seems like the "index" column is designed to be unique? The values are only unique per batch. The SQL index is not a unique index.

This can be a problem, for instance, when building a faiss index on a dataset and then trying to match up ids with a sql export.

### Steps to reproduce the bug

```
from datasets import Dataset
import sqlite3
db = sqlite3.connect(":memory:")
nice_numbers = Dataset.from_dict({"nice_number": range(101,106)})
nice_numbers.to_sql("nice1", db, batch_size=1)
nice_numbers.to_sql("nice2", db, batch_size=2)
print(db.execute("select * from nice1").fetchall()) # [(0, 101), (0, 102), (0, 103), (0, 104), (0, 105)]
print(db.execute("select * from nice2").fetchall()) # [(0, 101), (1, 102), (0, 103), (1, 104), (0, 105)]
```

### Expected behavior

I expected the "index" column to be unique

### Environment info

```
 % datasets-cli env

Copy-and-paste the text below in your GitHub issue.

- `datasets` version: 2.10.1
- Platform: macOS-13.2.1-arm64-arm-64bit
- Python version: 3.9.6
- PyArrow version: 7.0.0
- Pandas version: 1.5.2

zsh: segmentation fault  datasets-cli env
```