Feature request: sqlite CLI allow `.mode line` and `.mode quote` together
(1.1) By bokwoon on 2021-09-30 03:36:43 edited from 1.0 [link] [source]
I use sqlite CLI's
.mode line output format as my default, however it does not show blobs quoted as hexadecimals like
.mode quote does. But if I use
.mode quote I lose the ability to display each column is a separate line. Is it possible to enable both
quote behaviours together? i.e. each column appears in its own line, but the value is quoted accordingly.
My use case is UUID values, where I have opted to use BLOB over TEXT because of the space efficiency. UUID values are not displayable in
.mode line output format at all.
sqlite> .schema CREATE TABLE sites ( site_id BLOB, domain TEXT, subdomain TEXT ); sqlite> .mode line sqlite> SELECT * FROM sites; site_id = domain = 𝗡𝗨𝗟𝗟 subdomain = 𝗡𝗨𝗟𝗟 sqlite> .mode quote sqlite> SELECT * FROM sites; 'site_id','domain','subdomain' X'747fd11e07fa4cc18279091f257c6292',NULL,NULL
What I would prefer:
sqlite> .mode line quote sqlite> SELECT * FROM sites; site_id = X'747fd11e07fa4cc18279091f257c6292' domain = NULL subdomain = NULL
(2) By Donald Griggs (dfgriggs) on 2021-09-30 13:45:21 in reply to 1.1 [source]
You may well be aware of this, but for immediate relief, you can use hex():
SELECT hex(site_id), domain, subdomain FROM sites;
To format the UUID, you can of course insert hyphens as in
If you're using standard formats, you might have to consider endianness and variants.