Why SQLite Stores Your CSV Numbers as Text and How to Fix It

Import a CSV into SQLite, run SELECT sum(amount), and the number is right. Run ORDER BY amount or WHERE amount > 9 on the same column and the answers are wrong in a way that looks like a bug in the database. It is not a bug. The values are text, and SQLite does exactly what it documents for text. This article shows the symptoms on a six row file, explains the rule behind each, and gives the three fixes in the order I would apply them. The type choices around an import are in setting column types, and the full import in the SQLite import guide. The same trap in other tools is described in why a CSV has no types, and the .import options are in the .import article.

The file and the import

Six orders with amounts of different lengths, so that text order and numeric order disagree, and a ZIP code with a leading zero:

id,amount,zip
1,9.50,00501
2,10.25,02134
3,100.00,90210
4,2.75,10001
5,25.00,02134
6,1000.5,00501

Imported with the plain command, the table has no types and every value is text:

sqlite3 t.db ".import --csv amounts.csv a" ".schema a" "SELECT id, amount, typeof(amount) FROM a"

CREATE TABLE "a"(
"id", "amount", "zip");
1|9.50|text
2|10.25|text
3|100.00|text
4|2.75|text
5|25.00|text
6|1000.5|text

The schema is "id", "amount", "zip" and typeof(amount) is text for all six rows, including 1000.5. This is the default result of .import into a table that does not exist, and it is the right default for a tool that cannot know what a column is. The cost shows up as soon as you treat the column as numbers.

The symptoms

Sorting first. A text sort compares character by character, so 10.25 sorts before 2.75, because 1 comes before 2:

sqlite3 t.db "SELECT group_concat(amount, '  ') FROM (SELECT amount FROM a ORDER BY amount)" "SELECT 'max(amount) = ' || max(amount) FROM a" "SELECT 'rows where amount > 9: ' || count(*) FROM a WHERE amount > 9" "SELECT 'rows where amount > 500: ' || count(*) FROM a WHERE amount > 500"

10.25  100.00  1000.5  2.75  25.00  9.50
max(amount) = 9.50
rows where amount > 9: 6
rows where amount > 500: 6

10.25, 100.00, 1000.5, 2.75, 25.00, 9.50 is the order of the six values, and the largest according to max(amount) is 9.50, since 9 comes after 1 and 2 in text order. The report that shows "the highest sale is 9.50" is the one that gets noticed, if anything does.

The comparisons are the surprise. rows where amount > 9: 6 and rows where amount > 500: 6. Every row matched both filters, including 2.75. The rule is in the datatype page: when SQLite compares values of different storage classes, an integer or real is always less than text. The literal 9 is an integer, the column holds text, so the text is greater, whatever it says. A condition like amount > 500 is true for every row and filters nothing. That one is dangerous, because the query runs and returns a plausible number of rows.

The aggregate is the part that hides all of this:

sqlite3 t.db "SELECT sum(amount), typeof(sum(amount)), avg(amount) FROM a"

1148.0|real|191.33333333333334

sum(amount) is 1148.0 with typeof real, and avg is 191.33. Arithmetic functions convert text that looks like a number, so the total is correct, which is a reason people trust the column and then get a wrong ORDER BY. Note that text that does not look like a number would count as 0 in the sum, silently.

Why it works that way

SQLite does not have fixed column types. Each value carries its own storage class, and a column's declared type only sets an affinity, a preference for converting values as they arrive. A column with no declared type has no preference, so values stay as given; .import hands it text, and text it remains. The datatype page lists the five affinities and the rules for comparison, and the one to know is the order: NULL less than numbers, numbers less than text, text less than blobs.

A column declared with a type name that implies numbers gets a numeric affinity, and then the same text is converted on the way in. I made a table with columns of each affinity and inserted the same four strings:

sqlite3 :memory: "CREATE TABLE t (a, b TEXT, c INTEGER, d REAL, e NUMERIC)" "INSERT INTO t VALUES ('12', '12', '12', '12', '12'), ('007', '007', '007', '007', '007'), ('1e3', '1e3', '1e3', '1e3', '1e3'), ('abc', 'abc', 'abc', 'abc', 'abc')" ".mode box" "SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e), a, c, e FROM t"

╭───────────┬───────────┬───────────┬───────────┬───────────┬─────┬──────┬──────╮
│ typeof(a) │ typeof(b) │ typeof(c) │ typeof(d) │ typeof(e) │  a  │  c   │  e   │
╞═══════════╪═══════════╪═══════════╪═══════════╪═══════════╪═════╪══════╪══════╡
│ text      │ text      │ integer   │ real      │ integer   │ 12  │   12 │   12 │
│ text      │ text      │ integer   │ real      │ integer   │ 007 │    7 │    7 │
│ text      │ text      │ integer   │ real      │ integer   │ 1e3 │ 1000 │ 1000 │
│ text      │ text      │ text      │ text      │ text      │ abc │ abc  │ abc  │
╰───────────┴───────────┴───────────┴───────────┴───────────┴─────┴──────┴──────╯

The first column, with no type, kept 12 and 007 as text. The TEXT column kept everything as text. The INTEGER column stored '12' as the integer 12, '007' as 7, '1e3' as 1000 and left abc as text. REAL made them reals, and NUMERIC stored 1e3 as an integer 1000. So a typed column fixes the comparison and loses the leading zero of 007, the problem the types article shows with ZIP codes.

Fix 1: create the table first

Declare the types and skip the header. SQLite then converts as it loads:

sqlite3 t2.db "CREATE TABLE a (id INTEGER, amount REAL, zip TEXT)" ".import --csv --skip 1 amounts.csv a" "SELECT group_concat(amount, '  ') FROM (SELECT amount FROM a ORDER BY amount)" "SELECT 'rows where amount > 500: ' || count(*) FROM a WHERE amount > 500" "SELECT zip, typeof(zip) FROM a LIMIT 1"

2.75  9.5  10.25  25.0  100.0  1000.5
rows where amount > 500: 1
00501|text

With amount REAL, the sort is 2.75, 9.5, 10.25, 25.0, 100.0, 1000.5, and amount > 500 matches 1 row, which is correct. The ZIP column stayed TEXT and kept 00501. The values print as 9.5 and 100.0, since real numbers do not remember how many digits you wrote after the point; if the trailing zero matters, as in a price shown to two decimals, store integer cents or format on output.

Fix 2: cast in the query

When the table is already loaded and you cannot change it, convert in the query. CAST(amount AS REAL) makes a number out of the text for that expression:

sqlite3 t.db "SELECT group_concat(amount, '  ') FROM (SELECT amount FROM a ORDER BY CAST(amount AS REAL))" "SELECT 'rows where CAST(amount AS REAL) > 500: ' || count(*) FROM a WHERE CAST(amount AS REAL) > 500"

2.75  9.50  10.25  25.00  100.00  1000.5
rows where CAST(amount AS REAL) > 500: 1

The order is now 2.75, 9.50, 10.25, 25.00, 100.00, 1000.5, the original text unchanged, and the filter matched 1 row. The cost is that an index on the text column cannot be used for an expression like that, and that every query has to remember. A CAST that cannot convert returns 0 for a non-numeric string, so CAST('n/a' AS REAL) is 0.0 and passes any < 5 test, which is a fine way to get a wrong report.

Fix 3: rebuild the table with the right types

The durable fix for a loaded table is a new table with the types you want and a CAST on the way in. SQLite cannot change a column's type in place, so the pattern is create, copy, drop, rename:

sqlite3 t.db "CREATE TABLE a_typed AS SELECT CAST(id AS INTEGER) AS id, CAST(amount AS REAL) AS amount, zip FROM a" ".schema a_typed" "SELECT typeof(amount), typeof(zip) FROM a_typed LIMIT 1"

CREATE TABLE a_typed(id INT,amount REAL,zip);
real|text

CREATE TABLE ... AS SELECT made a_typed with amount REAL, but note what the declared schema looks like: CREATE TABLE a_typed(id INT,amount REAL,zip). The cast sets the column's type from the expression, and the ZIP column has no declared type, so it keeps whatever it held. It is simpler to write the table definition yourself, with STRICT, and INSERT INTO ... SELECT CAST(...), which is what the types article shows.

What the other databases do

The behavior is particular to SQLite. PostgreSQL and MySQL tables have fixed column types, so a COPY or LOAD DATA into a numeric column either converts the value or fails (or, for MySQL's local loads, clips it with a warning, as the MySQL guide shows). In those engines text numbers never end up in a numeric column, and the cost of getting the type wrong comes at load time. SQLite defers the cost to query time and gives no signal at either point, which is the reason to pay attention at the import: the SQLite documentation on type affinity states that any column can hold any type, and the declared type is only a preference. Use STRICT tables when you want the fixed types of the other engines.

How to tell it worked

Check each column that should be numeric: SELECT typeof(amount), count(*) FROM t GROUP BY 1 should return one row, real. Run SELECT min(amount), max(amount) and compare with the file's range, since a text max of 9.50 is the quick tell. And run a filter you can predict, such as amount > 500, and check the count.

Where text numbers are harmless

If you only sum, count, group by another column or look at the data, text numbers cost you nothing, which is why the problem survives so long. If the column is an identifier that you never compare numerically, text is the correct type. And if a table is a temporary landing place that SQL will convert, leaving the types off until the INSERT ... SELECT is a feature. The harm starts at ORDER BY, MIN, MAX and comparisons, and at an index you expect to speed up a range filter, so type the columns you will filter and sort, and keep the rest as text on purpose.