SQLite3 Terminal Output Formatting .width

Question

This is just purely for eye candy while working with SQLite in the terminal, but is there a way to format column width with headers so that each header is resized appropriately (and independently of the other columns)? In other words, here's the output with

.width auto

for a simple table

Id          Name        Price     
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600

It does what I'd expect. It resizes each column so that the longest item in any one column can be displayed. However, I'd like to automatically have the output formatted such that each column is wide enough for the longest item in only its column. In other words, I don't want to have to type in

.width 2 10 5

after the fact to get this output

Id  Name        Price
--  ----------  -----
1   Audi        52642
2   Mercedes    57127
3   Skoda       9000 
4   Volvo       29000
5   Bentley     35000
6   Citroen     21000
7   Hummer      41400
8   Volkswagen  21600

Is there something I can do to automate column sizing correctly?

score 6 · Answer 1 · answered Sep 14 '17 at 00:09

6

From the documentation of SQLite:

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.

answered Sep 14 '17 at 00:09

RFenzo

69
1
1

While this explains the default behavior, it doesn't answer the question of how to automatically set the correct width, e.g. when the first row doesn't contain the longest value. – Jan Wilamowski Mar 08 '22 at 06:23

score 5 · Accepted Answer · answered Sep 19 '13 at 19:47

5

The sqlite3 tool has no such function.

You would have to compute the column widths by hand (SELECT max(length(col1)) ...).

answered Sep 19 '13 at 19:47

CL.

173,858
17
217
259

11

I've used a number of DBs...I can't believe SQL makes this so frustratingly hard. What do people do when they want to explore SQLite data without it being truncated and without resetting the width before every query? – mlissner Sep 25 '15 at 23:33

score 5 · Answer 3 · answered Oct 10 '16 at 05:05

5

For "human readable" output, you can use column mode, and turn header output on. That will get you something similar to the sqlplus output in your examples:

sqlite> select * from foo;
234|kshitiz|dba.se

sqlite> .mode column
sqlite> select * from foo;
234         kshitiz     dba.se

sqlite> .headers on
sqlite> select * from foo;
bar         baz         baf
----------  ----------  ----------
234         kshitiz     dba.se

answered Oct 10 '16 at 05:05

Talha Mughal

131
1
10

8

This is all true, but it really doesn't answer the question at all which was specifically asking about automatically setting appropriate column widths. So I have no idea why it's currently the most up-voted answer. – Adam Spiers Nov 03 '19 at 20:18
1

Using sqlite 3.37.0 on my machine, `.mode column` resizes each individual column to the minimum width it needs, as the OP requested. I'm not sure why it doesn't appear that way in the example posted in the answer. – rcorre Feb 06 '23 at 19:22

score 5 · Answer 4 · answered May 11 '20 at 04:29

Alternative to .width, 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:

"name": first 20 characters
"age": auto-adjusting
"address": first 30 characters

Your query will be:

.mode columns
.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:

name                  age         address                       
--------------------  ----------  ------------------------------
short name            22          my house                      
my name is very long  22          i live in my house somewhere i

So how do you _automatically_ determine the amount of padding needed? — Jan Wilamowski, Mar 08 '22 at 06:25

score 4 · Answer 5 · answered May 02 '17 at 01:14

4

You can use a simple awk script such as in:

sqlite3 -batch -list db "select ..." awk -F'|' '{printf "%3s %4s %s\n", $1, $2, $3}'

Very simple. Also a lot easier and more flexible than sqlite's column mode.

answered May 02 '17 at 01:14

jw203198

41
1

What does the `select ...` exactly mean ? I am a beginner with sqlite3 and also not that versatile with terminal batches. Can you please specify on how the cmd looks in a concrete example ? – iKK May 05 '18 at 18:34
1

There is a pipe `|` missing before `awk` (after the closing quote of `"select ..."`). – Victor Jun 22 '20 at 13:29

score 3 · Answer 6 · edited May 23 '17 at 12:26

3

You can do it automatically with rlwrap and column:

$ rlwrap -a -N -c -z pipeto sqlite3 -header foo.db
> select * from cars; | column -n -t -s '|'

For more details, see my answer to sqlite3 is chopping/cutting/truncating my text columns.

edited May 23 '17 at 12:26

Community

1
1

answered Nov 11 '15 at 16:09

arekolek

9,128
3
58
79

For me it worked better without `-N`. Also, my `column` didn't have `-n`, and its params could be squashed into: `| column -ts'|'` – akavel Apr 23 '17 at 19:43

score -1 · Answer 7 · answered Mar 02 '21 at 18:24

-1

I know this question is a bit old, but for those in the future, you can specify the width of all output columns with varying lengths. For example:

sqlite > .width 5 25 15

answered Mar 02 '21 at 18:24

idryer

129
3

The question is about how to do this automatically, based on the output. – Jan Wilamowski Mar 08 '22 at 06:21

SQLite3 Terminal Output Formatting .width

7 Answers7