CODEWITHSUNDEEP
pythondatabasesqlite
Béa
Béa

Reputation: 121

can we do an autoincrement strings in sqlite3?

Can we do autoincrement string in sqlite3? IF not how can we do that? Exemple: RY001 RY002 ... With Python, I can do it easily with print("RY"+str(rowid+1)). But how about it performances? Thank you

Upvotes: 1

Views: 317

Answers (2)

Adobe
Adobe

Reputation: 13477

Something like this:

select
  printf("RY%03d", rowid) as "id"
, *
from myTable

?

Upvotes: 1

forpas
forpas

Reputation: 164099

If your version of SQLite is 3.31.0+ you can have a generated column, stored or virtual:

CREATE TABLE tablename(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   str_id TEXT GENERATED ALWAYS AS (printf('RY%03d', id)),
   <other columns>
);

The column id is declared as the primary key of the table and AUTOINCREMENT makes sure that no missing id value (because of deletions) will ever be reused.
The column str_id will be generated after each new row is inserted, as the concatenation of the 'RY' and the left padded with 0s value of id.
As it is, str_id will be VIRTUAL, meaning that it will be created every time you query the table.
If you add STORED to its definition:

str_id TEXT GENERATED ALWAYS AS (printf('RY%03d', id)) STORED

it will be stored in the table.

Upvotes: 2

Related Questions