The Value Inspector

SQLite types values, not columns, so a column declared INTEGER can hold text in one row and a blob in the next. On top of that, the grid shows a value through the type mappings, the date and time reading and the GUID setting, any of which can make the stored value and the displayed one look nothing alike.

The Value Inspector reports what a cell actually holds, and lets you change it.

To open it: right-click a cell in the Data tab and choose Value Inspector.

What the report shows

The upper half describes the column and the value:

Column
  Name           ADDRESS_1
  Declared type  TEXT
  Affinity       TEXT
  Allowed        TEXT, BLOB, NULL
  Access         Read-write

Value
  Storage class  TEXT
  Length         17
  Contents       100 Cranberry St.

Declared type is what the schema says. Affinity is what SQLite makes of it — one of INTEGER, REAL, NUMERIC, TEXT or BLOB. Affinity is a preference, not a constraint: a column converts the values it is given where it can, and keeps them where it cannot, which is why a column declared REAL will happily hold a word.

Allowed lists every storage class this column can end up holding. It is a property of the column alone. A TEXT column never holds a number, and — less obviously — a REAL column never holds an integer, because whole numbers are forced into floating point on the way in.

For an integer or real value the report adds a Read as a date section, listing the same number read as a Julian day, as Unix time in three scales, as a Borland TDateTime and as Microsoft ticks. Most of those readings will be out of range, and that is the point: the one that lands in a plausible year is usually the one that was meant.

If the value's storage class does not suit the type the column is read as, the report says so — and it is the same judgement the grid makes when it greys a cell out, so the inspector explains the cell you are looking at rather than a different one. SQLite allows such values: a declared type constrains nothing.

Note that this is not the same as the column's affinity. A DATETIME column has NUMERIC affinity, but a date held in it as text is read as a date and is not flagged; text that cannot be read as a date is.

A blob that holds JSON

For a BLOB the report gives the length and the first bytes. Where SQLite reads those bytes as JSONB — the binary form a jsonb() function writes — the report names it, and a read-only JSON pane opens under the value with the document decoded and laid out:

Value
  Storage class  BLOB
  Length         229
  Contents       JSONB
  First bytes    CC E3 77 6F 72 64 65 72 ...

The hex stays where it is. It is the value, and it is the only place a stray byte or a document that stops halfway shows itself — which is most of the reason for opening this window on a blob at all.

The pane reads; it does not write. Changing JSON means encoding it back to JSONB, which is the JSON editor's business — open the cell there to edit it. Apply here still writes what the Value box holds.

Whether a blob is JSONB is SQLite's answer rather than a guess about the bytes, so there are two ordinary reasons for a JSONB value to show no pane: a SQLite library older than 3.45, which has no JSONB at all, and a value larger than Max BLOB size to load in grids, which is then only partly loaded and so is not valid JSONB. Raise that setting to see the whole document.

Storing a value

The Store as list offers the storage classes this value can actually be given, and the Value box holds the value itself. Word wrap folds long lines into the box rather than scrolling them sideways; it is remembered, so it stays as you left it the next time you open a cell.

The list is usually shorter than five entries, and often has only one, in which case it is shown as a statement — Will be stored as — rather than as a choice. Two things narrow it:

• What you have typed. There is no integer equal to a word, so INTEGER is not offered for Mansfield Road.
• What the column will do with it. Text that reads as a number is stored as a number by any column with numeric affinity, so typing 123 into a REAL column offers REAL — not TEXT, which the column would silently convert.

Where several classes really are reachable — in a column declared BLOB, or with no declared type at all, which store exactly what they are given — the list is a genuine choice.

Click Apply to write the value. A confirmation shows the statement, the row's rowid, and the value the table will actually be left holding, which is not always the one you typed:

Store this value as INTEGER?

The table will hold:  1

update [Customer] set Code = ? where rowid = ?

rowid = 4

This writes to the table immediately, outside the usual edit and post.

Typing 01 into a numeric column stores 1 — the leading zero is not kept. The confirmation is where you find that out.

Note

The Value box holds the value itself, not SQL. There is no quoting convention: typing '01' stores the four characters '01', apostrophes included.

Warning

Apply writes to the table immediately with an UPDATE statement, outside the usual edit-and-post cycle. This is the only way to store a value of a chosen storage class, but it means the write is not part of any pending row edit. If a transaction is open, the write belongs to that transaction like any other.

STRICT tables

In a STRICT table the declared type is enforced rather than preferred. The report says so, and Allowed narrows to the declared type and NULL:

Column
  Name           Quantity
  Declared type  INTEGER
  Strict         yes - the declared type is enforced, not preferred
  Allowed        INTEGER, NULL

The Store as list narrows with it. A STRICT INTEGER column takes a whole number however it is spelled — 7, 1.0 and 1e3 all become the integer — but 1.5 and Mansfield Road are refused outright rather than stored as something else, so neither is offered.

A column declared ANY is the exception: it stores exactly what it is given, with no conversion at all.

When the value can only be read

The Access row says whether the value can be written from here, whatever the answer, so a read-only column, query or database needs nothing further said about it. Two cases get a sentence under the report as well, because neither is visible in the report itself:

• the row has unposted changes — the inspector writes with an UPDATE, which would go around those changes and be overwritten when the row is posted. Post or cancel the row first.
• the row cannot be identified on its own — the query draws on more than one table, or on a view, so there is no single row to update.

See also: Editing table data · Data types · Working with JSON