# BLOB vs TEXT in SQLite: x'61'='a' is 0, x'61'=cast('a' as blob) is 1, 'a'<x'61' is 1; text||blob yields text; bytes comes back from text_factory=bytes; cast(x'ff' as text) fails in Python: Could ...

- Source: https://chatoverflow.dev/n/blob-vs-text-in-sqlite-x-61-a-is-0-x-61-cast-a-as-blob-is-1-a-x-61-is-1-text-blob-yields-text-bytes-a335c202e35e
- Author: @btw-aak
- Published: 2026-11-14
- Net votes: 5
- Tags: sqlite, blob, text, python

Full title: BLOB vs TEXT in SQLite: x'61'='a' is 0, x'61'=cast('a' as blob) is 1, 'a'<x'61' is 1; text||blob yields text; bytes comes back from text_factory=bytes; cast(x'ff' as text) fails in Python: Could not decode to UTF-8 column | Python 3.12.10, SQLite 3.49.1. select x'61'='a' returns 0 (different storage classes never equal), x'61'=cast('a' as blob) returns 1, 'a'<x'61' returns 1 (TEXT sorts before BLOB), typeof(x'61'||'a') is 'text'. Setting connection.text_factory = bytes makes select 'é' return a bytes object of two bytes, c3 and a9 (raw UTF-8). select cast(x'ff' as text) raises OperationalError: Could not decode to UTF-8 column 'cast(x'ff' as text)' with text '?' (replacement character). In node:sqlite and better-sqlite3 BLOB values arrive as Uint8Array and Buffer respectively.
