Useful Queries

List of some queries I've found to be useful.

All encoded storage items under a specific prefix

example uses SystemAccount prefix

-- Query for all storage items under a specific key prefix

SELECT encode(storage::bytea, 'hex'), block_num FROM
    (SELECT storage.key, storage.storage, storage.block_num, blocks.spec FROM storage, blocks
        WHERE
	    storage.key like (E'\\x26aa394eea5630e07c48ae0c9558cef7b99d880ec681799c0cf30e8886371da9')::bytea||'%'
        AND
	    blocks.block_num = storage.block_num
        AND
	    blocks.spec = 2023
    ) as z
    ORDER BY block_num;

Blocks that have not been executed for storage changes

SELECT block_num FROM blocks
WHERE NOT EXISTS
   (SELECT block_num FROM storage WHERE storage.block_num = blocks.block_num)
ORDER BY block_num;

Blocks where a runtime upgrade has occurred

SELECT DISTINCT ON (spec) spec, block_num
FROM blocks
ORDER BY spec, block_num ASC;