Data types¶

SQLite uses dynamic typing: you can declare a column with any type name you like, and any value can be stored in any column. Internally, though, every value belongs to one of five storage classes:

NULL — a null value.
INTEGER — a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on its magnitude.
REAL — a floating-point number, stored as an 8-byte IEEE value.
TEXT — a text string, stored in the database encoding (UTF-8, UTF-16BE, or UTF-16LE).
BLOB — a blob of data, stored exactly as it was supplied.

To present values in a natural way — as numbers, dates, money, images, and so on — SQLite Expert maps the declared type of each column to one of its own internal types. The predefined mappings are:


Declared type	Mapped to internal type	Description
DEC	Float	Floating-point numeric field (8 bytes)
DECIMAL	Float	Floating-point numeric field (8 bytes)
DOUBLE	Float	Floating-point numeric field (8 bytes)
DOUBLE PRECISION	Float	Floating-point numeric field (8 bytes)
FLOAT	Float	Floating-point numeric field (8 bytes)
NUMERIC	Float	Floating-point numeric field (8 bytes)
REAL	Float	Floating-point numeric field (8 bytes)
INT	Largeint	64-bit integer field
INTEGER	Largeint	64-bit integer field
SMALLINT	Largeint	64-bit integer field
TINYINT	Largeint	64-bit integer field
WORD	Largeint	64-bit integer field
AUTOINC	Largeint	64-bit integer field
BIGINT	Largeint	64-bit integer field
LARGEINT	Largeint	64-bit integer field
INT64	Largeint	64-bit integer field
CHAR	WideString	Wide string field
VARCHAR	WideString	Wide string field
VARCHAR2	WideString	Wide string field
TEXT	WideString	Wide string field
DATETEXT	WideString	Wide string field
NCHAR	WideString	Wide string field
NVARCHAR	WideString	Wide string field
NVARCHAR2	WideString	Wide string field
NTEXT	WideString	Wide string field
CURRENCY	Currency	Money field (8 bytes)
MONEY	Currency	Money field (8 bytes)
SMALLMONEY	Currency	Money field (8 bytes)
BLOB	Blob	Binary Large Object field
RAW	Blob	Binary Large Object field
BINARY	Blob	Binary Large Object field
VARBINARY	Blob	Binary Large Object field
JSON	WideString	Wide string field
CLOB	WideMemo	Text wide memo field
MEMO	WideMemo	Text wide memo field
JSONB	Blob	Binary Large Object field
DATE	Date	Date field (Borland TDateTime)
TIME	Time	Time field (Borland TDateTime)
DATETIME	DateTime	Date and time field (Borland TDateTime)
TIMESTAMP	DateTime	Date and time field (Borland TDateTime)
BOOLEAN	Boolean	Boolean field
GRAPHIC	Graphic	Graphic field

Note

For a Date, Time, or DateTime value, the integer part is the number of days since 30 December 1899, and the fractional part is the fraction of a 24-hour day that has elapsed. SQLite Expert stores these values in the format yyyy-mm-dd hh:mm:ss.sss, but also recognizes values stored in the Borland DateTime format.

Note

A declared type carrying no size — TEXT, NTEXT or JSON, as against VARCHAR(20) — becomes a memo field rather than a string one. A memo is left in the database file until something asks for it, which is what keeps a column of large documents from being loaded in its entirety just to paint a screenful of rows.

Decimal and money values¶

SQLite has no decimal type. DECIMAL, NUMERIC, MONEY and the rest give a column NUMERIC affinity, which converts what it is given to an integer or a binary floating-point number as it is stored. The conversion happens inside SQLite, as the value is inserted — before SQLite Expert sees it:

You insert into a `DECIMAL(10,2)` column	SQLite stores
`19.99`	the real `19.99`
`'19.99'` as text	the real `19.99`
`'0019.99'` or `'19.990'`	the real `19.99`
`'1e3'`	the integer `1000`
`'19.999999999999999999'`	the integer `20`
`'abc'`	the text `abc` — not a number, so not converted

So a DECIMAL column does not hold decimals. It holds doubles, with the rounding a double implies, and SQLite Expert reads it as a floating-point column because that is what is in the file. Declaring more digits does not change this: the scale is remembered only to format the value for display.

If you need exact decimals, store them in a way SQLite will not convert:

Scaled integers — a price as a whole number of cents, in an INTEGER column. Exact, and it sorts and sums correctly.
Text, in a column whose declared type has TEXT affinity (TEXT, VARCHAR). The digits are kept exactly as written, at the cost of numeric sorting and arithmetic.

Note

A whole number keeps its INTEGER storage class even in a DECIMAL column — 5.00 is stored as the integer 5. Beyond 2⁵³ such an integer is larger than a double can hold exactly, so it is shaded in the grid like any other value its column's type cannot represent, and shown and exported as the exact number the file holds rather than the nearest double.

JSON and JSONB¶

Since version 3.45 SQLite has had two representations of a JSON document: json() returns it as text, and jsonb() returns SQLite's own binary encoding as a blob. Columns are usually declared JSON or JSONB to say which one they hold, and SQLite Expert maps them accordingly — JSON as text, JSONB as a blob.

This matters beyond appearances. A JSONB value is binary, and reading it as though it were text produces nonsense; it also means the grid's Show JSONB as readable JSON option can apply to the column, since that option works on blob columns.

Either kind can be opened in the JSON Editor, and both are queried the same way — see Working with JSON.

Note

The declared type says what a column is meant to hold, not what it does hold. A JSONB column containing text, or a JSON column containing a blob, is shaded in the grid like any other value whose storage class does not suit its column. A blob in a column read as text shows as (blob) — bytes have no text form, and an empty cell would not be telling you they are there.

Boolean values¶

SQLite has no boolean type either. It has understood the keywords TRUE and FALSE since version 3.23, but they are simply the integers 1 and 0, and that is what a column declared BOOLEAN normally holds. SQLite Expert shows such a column as a checkbox.

Text can end up in one all the same — from an import, from another program, or from an INSERT that quoted the value. BOOLEAN gives the column NUMERIC affinity, and 'yes' is not a number, so it stays text exactly as it was written.

SQLite Expert reads that text against two lists of accepted values, which you can change under Tools → Options → Values → Boolean:

Setting	Default
Text values meaning TRUE	`1;true;t`
Text values meaning FALSE	`0;false;f`

The lists are semicolon-separated and not case sensitive, so TRUE, True and true are all the same entry. Surrounding spaces are ignored.

A value in either list is read as that boolean and shown as a ticked or unticked checkbox, the same as a stored 1 or 0.

A value in neither list is not read as a boolean at all. The cell shows the text it holds — yes, ja, N/A — in place of the checkbox, shaded like any other value whose storage class does not suit its column. This is deliberate: a checkbox has no third state, so an unticked box would state that the value is false, which is something nobody has established and is indistinguishable from a stored 0.

If your databases use words the defaults do not cover, add them to the lists and those cells become checkboxes again. The defaults are kept narrow on purpose, so that an unfamiliar value is shown to you rather than quietly interpreted.

Note

These lists apply only to text. A column holding the integers 1 and 0 — what SQLite itself writes — is unaffected by them.

Type affinity¶

The declared type does not restrict what a column can hold, but it does decide what SQLite makes of the values put into it. Each declared type gives the column one of five affinities, by matching the type name in this order:

The declared type contains	Affinity
`INT`	INTEGER
`CHAR`, `CLOB` or `TEXT`	TEXT
`BLOB`, or no declared type at all	BLOB
`REAL`, `FLOA` or `DOUB`	REAL
anything else	NUMERIC

Affinity is a preference, not a constraint. A column converts a value where it can and keeps it where it cannot:

A column with NUMERIC or INTEGER affinity stores text that reads as a number as a number, and anything else as text. '1.0' and '1e3' become the integer 1 and 1000; 'Mansfield Road' stays text.
A column with REAL affinity does the same, but forces whole numbers into floating point — so it never holds an integer.
A column with TEXT affinity writes numbers out as text. It holds only TEXT, BLOB and NULL.
A column with BLOB affinity — including one with no declared type — converts nothing. It is the only column that stores exactly what it is given.

This is why a column declared REAL can hold a word, and why storing the text 123 in one is not possible: the column reads it as a number first. The Value Inspector reports the affinity of a column and every class it can hold.

STRICT tables¶

A table created with the STRICT option enforces its declared types instead of preferring them. Two things change:

Every column must be declared as one of exactly six types: INT, INTEGER, REAL, TEXT, BLOB or ANY. Any other type name — VARCHAR(20), NUMERIC, BIGINT, DOUBLE — is rejected when the table is created. When Strict is ticked on the Design tab, the SQL Type list offers only those six.
A value that does not fit the declared type is refused with an error, rather than stored as something else. A STRICT INTEGER column takes 7, 1.0 and 1e3 — all whole numbers — but rejects 1.5 and Mansfield Road, where a non-strict column would have stored them as a real and as text.

A column declared ANY is exempt: it stores what it is given, with no conversion at all. Note that ANY means this only in a STRICT table; elsewhere it is an ordinary type name with NUMERIC affinity.

Custom data types¶

Besides the predefined types, you can declare your own — as long as each one maps to one of the internal types in the table above. To add a custom type or change an existing mapping, use the Type Mappings page under Changing options.

For more about SQLite's type system, see the SQLite documentation.