I must store many SHA256-values in a sqlite-database and now i wonder which sqlite-affinity would be the correct one. SHA256-values have an output-length of 256bit=32byte. I am looking for an sqlite-affinity which can handle this but which also does not "waste" diskspace since in my case i have very many datasets. I have read on this site many useful things about datatypes in sqlite but i still do not know which type would be correct for SHA256-values. i would prefer something like "bytearray(32)" so i thought varchar(32) could be the one. but then i remembered that i can probably not converting all of this bytes to printable characters to get them stored as character-array. So it seems that I must use Blob.

Unfortunately do not really find any useful information about limiting the Blob-size in sqlite and how sqlite handles blobs internally. I would like to use the information that my hash-values have always the same length to optimize the disk-usage of the database: I want sqlite to know that for this Blob-column it always have to reserve only 32 bytes, never more. So I would like to use something like Blob(32) as column-type. Is this possible? And if yes: Does it make sense or is there any fallacy?


You can use BLOB(32) as data type.

SQLite does not limit the size of text or blob values, and never allocates more storage than actually needed. The number is pretty much ignored by SQLite, and its primary purpose is to serve as documentation for you.

If you want to enforce the length, add a constraint: CHECK (length(my_column) = 32).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.