0

I am using:

  • SQLite version 3.21.0

I look for away to prettify my SQLite SELECT shell output (to make it more human readable, in essence). This answer helped me a lot.

Though, now I am wondering if there are ways to:

  1. Set a column width to be equal exactly to the width of its header? I went through the corresponding section of the documentation — the closest thing it mentions is .width 0, which effect is explained as follows:

    If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data.

    if it was only 'the maximum of two numbers: 10 and the width of the header' — it would suffice my needs, though '...and the width of the first row of data.' spoils it for me.

  2. Is there a way to set width of all columns to be the same, without knowing in advance how many columns are there? E.g. instead of doing .width 5 5 5 for 3-column SELECT, I would like to do something like .width all 5.

2 Answers2

0

The sqlite3 shell is designed to be simple, so there are limited customization mechanisms.

There is no built-in way to modify the widths in this way.

You'd have to either modify the shell's source code, or use some alternative shell written in a scripting language.

CL.
  • 5,213
  • 1
  • 20
  • 23
0

You could use a combination of .width, and use an alias name padded with spaces.

Advantage is that you are adjusting the width on the fly in your query only for those columns that require extra width, while the rest of the columns keep using the auto-adjusting width.

All columns will use the auto-adjusting width, that is based on the sqlite documentation width rule:

If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.

Let's say your table "my_table" has the columns "name", "age" and "address". And you are interested in showing:

  1. "name": first 3 characters
  2. "age": auto-adjusting
  3. "address": first 30 characters

Your query will be:

.mode columns
.width 3 0 0
.headers on


CREATE TABLE my_table (name TEXT, age INTEGER, address TEXT);

INSERT INTO my_table VALUES ("short name",
22, "my house");

INSERT INTO my_table VALUES ("my name is very long",
22, "i live in my house somewhere in the planet Earth");

SELECT name AS "name                ",
age,
address AS "address                       "
FROM my_table;

Your output:

nam  age         address                       
---  ----------  ------------------------------
sho  22          my house                      
my   22          i live in my house somewhere i
luis_js
  • 111
  • 1