How to test if a blob in an sqlite table is not null, without reading the blob entirely?

Question

I am trying to query a table that has a blob column, and need to filter the results to only provide rows that have content (any content) in the blob column.

However, doing SELECT column_name FROM table_name WHERE blob_column IS NOT NULL takes a long time, which I assume is due to the fact that some of the blobs are quite heavy. It seems that the WHERE clause is reading the entire content of the blobs and comparing them to null.

Is there a way to test whether a blob column is null or not, without having sqlite read the entire contents of the blob?

Answer 1

Since version 3.7.12, using the length() or typeof() functions avoids reading the binary data (the query is much faster). So to avoid the unnecessary overhead, one must change

SELECT column_name FROM table_name WHERE blob_column IS NOT NULL

to

SELECT column_name FROM table_name WHERE LENGTH(blob_column) IS NOT NULL

or

SELECT column_name FROM table_name WHERE TYPEOF(blob_column) != 'null'