SQLite: Numeric values in CSV treated as text? -

i imported huge text file table, using .import command. ok, except fact seems treat numeric values text. instance, conditions such where field > 4 met. did not specify datatypes when created table, doesn't seem matter when small tables created.

any advice welcome. thanks!

edit/conclusion: turns out of values in csv file blanks. ended solving being bit less lazy , declaring datatypes explicitly.

the way sqlite handles types described on page: http://www.sqlite.org/datatype3.html

in particular:

under circumstances described below, database engine may convert values between numeric storage classes (integer , real) , text during query execution.

section 3.4 (comparison example) should give concrete examples, explain problem have. example:

-- because column "a" has text affinity, numeric values on -- right-hand side of comparisons converted text before -- comparison occurs. select < 40,   < 60,   < 600 t1; 0|1|1 

to avoid affinity guessed, can use cast explicitly (see section 3.2 too):

sqlite may attempt convert values between storage classes integer, real, and/or text before performing comparison. whether or not conversions attempted before comparison takes place depends on affinity of operands. operand affinity determined following rules:

• an expression simple reference column value has same affinity column. note if x , y.z column names, +x , +y.z considered expressions purpose of determining affinity.
• an expression of form "cast(expr type)" has affinity same column declared type of "type".
• otherwise, expression has none affinity.

here example:

create table test (value text); insert test values(2); insert test values(123); insert test values(500);  select value, value < 4 test; 2|1 123|1 500|0 

it's csv import create columns of affinity text.