Setting Column Types When SQLite Imports a CSV File Safely

SQLite does not enforce column types the way PostgreSQL or MySQL do, and the .import command does not give you any. Those two facts together mean that a CSV loaded in the obvious way arrives as a table of text, and a CSV loaded into a table you designed arrives with whatever the affinity rules decide. Both can lose information. This article shows what happens to a ZIP code and a price in four setups, from an untyped import to a STRICT table, and ends with the route I use when the data matters. The other steps of an import are in the SQLite import guide.

The test file

Four products with a decimal price, an integer stock and a ZIP code with a leading zero:

sku,name,price,stock,zip
A-100,Kabel 2 m,9.90,10,00501
A-101,"Stecker, schwarz",1.25,200,02134
B-007,Adapter,12.00,0,90210
B-010,Netzteil,24.50,15,10001

What .import creates

Imported into a table that does not exist, the columns have no declared type at all:

sqlite3 shop.db ".import --csv products.csv products" ".schema products" "SELECT sku, typeof(price), typeof(stock), typeof(zip), zip FROM products"

CREATE TABLE "products"(
"sku", "name", "price", "stock",
 "zip");
A-100|text|text|text|00501
A-101|text|text|text|02134
B-007|text|text|text|90210
B-010|text|text|text|10001

The schema is "sku", "name", "price", "stock", "zip" with nothing after the names. Every typeof is text, including for price and stock, and the ZIP codes are intact. This is the safest outcome for the data and the least useful for the table: it cannot sort price numerically, and the numbers as text article shows what that breaks.

What a typed column does

To get real numbers, create the table first and say what you want. I declared price REAL, stock INTEGER and, to make a point, zip INTEGER:

sqlite3 shop.db <<'SQL'
CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price REAL, stock INTEGER, zip INTEGER);
.import --csv --skip 1 products.csv products
SELECT sku, price, typeof(price), stock, typeof(stock), zip, typeof(zip) FROM products;
SQL

A-100|9.9|real|10|integer|501|integer
A-101|1.25|real|200|integer|2134|integer
B-007|12.0|real|0|integer|90210|integer
B-010|24.5|real|15|integer|10001|integer

Prices are real and stock integer, as intended. The ZIP codes are the problem: 00501 was stored as the integer 501 and 02134 as 2134, and the typeof column says integer. SQLite applied integer affinity, and text that looks like an integer is converted to one. The leading zero is not recoverable from 501, because the number does not remember how it was written. No warning appeared, and nothing in the table now distinguishes ZIP code 501 from an actual 501.

The rule is the one I gave for every engine in the PostgreSQL guide: identifiers are text. A ZIP code, a phone number, an account number, a SKU that happens to be digits, none of them is a number, since you never add two of them. Declare them TEXT.

Affinity is a hint, not a rule

SQLite's types are "affinities": a column prefers a type and converts when it can, and stores the value as it is when it cannot. That is why my non-strict REAL column accepted a value that is not a number:

sqlite3 shop.db <<'SQL' 2>&1
CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price REAL, stock INTEGER, zip TEXT);
.import --csv --skip 1 products-bad.csv products
SELECT sku, price, typeof(price) FROM products;
SQL

C-1|not a price|text
C-2|2.5|real

not a price was stored in the price column with typeof text, with no message, and the next row's 2.50 became the real 2.5. A table like this holds a mixture, and any query that treats the column as a number produces odd results for the bad row: sums count it as zero and comparisons order it after all numbers. You find out when a report is wrong. The datatype page documents the rules, including the five affinities and how a declared type name maps to one.

STRICT tables

Since 3.37, a table can be declared STRICT, and its columns then accept only values of the declared type, with the exception of conversions that lose nothing, such as '2.50' to a real. The allowed types are INT, INTEGER, REAL, TEXT, BLOB and ANY, as the STRICT tables page lists. With a TEXT column for the ZIP code:

sqlite3 shop.db <<'SQL' 2>&1
CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price REAL, stock INTEGER, zip TEXT) STRICT;
.import --csv --skip 1 products.csv products
SELECT sku, typeof(price), typeof(stock), zip FROM products;
SQL

A-100|real|integer|00501
A-101|real|integer|02134
B-007|real|integer|90210
B-010|real|integer|10001

The four rows loaded, prices as real, stock as integer, and the ZIP codes as the text 00501, 02134, 90210 and 10001. Now the bad file, where the first record has not a price:

sqlite3 shop.db <<'SQL' 2>&1
CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price REAL, stock INTEGER, zip TEXT) STRICT;
.import --csv --skip 1 products-bad.csv products
SELECT count(*) AS rows_loaded FROM products;
SQL

products-bad.csv:2: INSERT failed: cannot store TEXT value in REAL column products.price
1

products-bad.csv:2: INSERT failed: cannot store TEXT value in REAL column products.price. The record was skipped, the second record loaded, and the count is 1. That is a good failure with an important property: the import did not stop, so a file with one bad row still loads the others, and the only sign is a message on standard error and a count that is lower than the file's. The article on import errors explains that exit status and the script that turns the message into a failed job.

Convert in SQL instead

The approach that gives you the most control is to import the file untyped into a staging table and move the rows into a typed one with CAST. When a conversion cannot succeed, the cast does not fail, it returns zero for text, so the validation has to be written, but at least it is written where you can see it:

sqlite3 shop.db <<'SQL'
.import --csv products.csv raw
CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price REAL, stock INTEGER, zip TEXT) STRICT;
INSERT INTO products SELECT sku, name, CAST(price AS REAL), CAST(stock AS INTEGER), zip FROM raw;
DROP TABLE raw;
SELECT sku, typeof(price), typeof(stock), zip FROM products;
SQL

A-100|real|integer|00501
A-101|real|integer|02134
B-007|real|integer|90210
B-010|real|integer|10001

The raw table was imported with .import, a STRICT table was created, INSERT ... SELECT cast the price and stock and passed the ZIP code through as text, and the raw table was dropped. The result has real, integer and text columns and the ZIP codes intact. For a file I did not produce, I add a check before the insert: a query that finds the rows where CAST(price AS REAL) = 0 AND price <> '0', or where a pattern match shows the value is not a number, and sends them to a reject table. That is the same staging and reject idea as the PostgreSQL staging article.

Which one

For a file I trust and a table I own, a STRICT table with TEXT for identifiers, INTEGER and REAL for numbers, and --skip 1 on the import. The failure mode is an INSERT failed message and a missing row, which a count check catches. For a file I do not trust, the staging route with explicit checks. For a quick look, an untyped import, remembering that the numbers are text.

I do not declare dates as anything but TEXT in ISO 8601 form, since SQLite has no date type, and its date functions work on text in that form. A DATE column name is accepted and means numeric affinity, which would be wrong for 2025-03-14, and is an easy trap.

How to tell it worked

After the load, SELECT typeof(col), count(*) FROM t GROUP BY 1 for each column that should be one type. A numeric column that shows both real and text has a bad value in it. The query takes a second on 2,000,000 rows and finds what affinity hides.

When types should not be enforced

A file you want to explore once does not need types, and STRICT adds friction when the schema is not decided. A column that really holds mixed content, such as a spreadsheet column where people typed numbers and the word n/a, is better kept as text and cleaned later. And SQLite's flexible typing is a feature when you store JSON or blobs next to numbers. If you want a database that enforces everything and fails loudly, PostgreSQL does that by default, and the DuckDB guide shows an engine that infers types from the file in the first place.