Clever Geek Handbook

Vectors / Lists in SQLite

I'm new to SQL, are there types of lists or vector variables? Can I have a column whose type is a list of strings?

Maybe something like:

CREATE TABLE myTbl(domain varchar PRIMARY KEY, linkList list<varchar>);

Is it possible to add a line like this (using the "+ =" operator):

UPDATE myTbl SET linkList += "www.test.com," WHERE domain="blah";
// Table is like so
CREATE TABLE myTbl(domain varchar PRIMARY KEY, linkList varchar);
// Column linkList is a string of links separated by comma's
+3


source to share


3 answers


No, there are no array / list / vector data types in SQLite . SQLite string concatenation operator -||

.



+1


source


As far as I know, this is not possible in SQLite (perhaps this is one of the reasons for the "Lite" in the name). Under the hood, it only has storage types: 64-bit integer SQLITE_INTEGER

, 64-bit float (double) SQLITE_FLOAT

, blob (binary), SQLITE_BLOB

and string SQLITE_TEXT

. All SQL types map to them. There is also SQLITE_NULL

.



0


source


There is no list data type in SQLite.

But you can get around this limitation:

  • Convert the list to JSON (which involves escaping each item separately),
  • Place the JSON string in the table as SQLITE_TEXT.

Obviously JSON has a big performance impact and adding an item to your list will take time (read, deserialize, add, serialize, update).

0


source






More articles:


All Articles