CODEWITHSUNDEEP
databasetypessqliteprecision
glesage
glesage

Reputation: 975

SQlite NUMERIC data types size

I've been testing out sqlite3 types for efficiency on a mobile app but I'm getting strange behavior.

I created a table this way:

create table numeric_t(int_4 INTEGER(4), int_8 INTEGER(8), real_4 REAL(4), real_8 REAL(8), new_real DOUBLE, int_1 INTEGER(1));

And am setting values and seing which rows show how many decimals and so on...

Was I wrong to assume that:
- the INTEGER columns should not be able to store numbers with decimals?
- the INTEGER(1), INTEGER(4) and REAL(4) should not be able to store as many decimal places as the others?

Thanks

Check out this snapshot of my results.

Upvotes: 2

Views: 7270

Answers (1)

CL.
CL.

Reputation: 180070

In SQLite, column types do not restrict data types:

> CREATE TABLE t(i INTEGER);
> INSERT INTO T VALUES(1);
> INSERT INTO T VALUES(1.23);
> INSERT INTO T VALUES('forty-two');
> SELECT typeof(i) FROM t;
integer
real
text

To quote the documentation:

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.
[...]
Any column ... may be used to store a value of any storage class.

Upvotes: 5

Related Questions