Using Datatypes with RSQLite

Question

I create a database in language R using package RSQLite with the following code.

con <- dbConnect(RSQLite::SQLite(), "TEST.db")
dbSendQuery(con, "CREATE TABLE TabA (C1 INTEGER NOT NULL, C2 INTEGER NOT NULL, C3 REAL, PRIMARY KEY (C1))")
dbSendQuery(con, "INSERT INTO TabA (C2,C3) VALUES (2.6,2.6)")
dbGetQuery(con, "SELECT * FROM TabA")
dbDisconnect(con)

I define a column C2 with datatype integer and C3 with datatype double. Now I expect the following values in my Database:

  C1  C2  C3
1  1   3 2.6

But my query gives the following result:

  C1  C2  C3
1  1 2.6 2.6

The defined datatypes are ignored totaly. What's the bug in my program?

Thanks

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

This is a feature of SQLite: columns can contain different data types in each row, the type declaration is stored but not enforced.

From https://www.sqlite.org/datatype3.html:

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.:

Using Datatypes with RSQLite

1 Answers1