How to properly align columns in SQLite?

Question

I recently began learning with SQLite, and I am trying to output the data of a table into the Terminal (macOS). However, the result after I've set .mode column or .mode tab is not properly aligned when some columns have no data, such in the second entry.

For example, in column mode where Donna doesn't have age and place data:

name            surname             age         place              
--------------  ------------------  ----------  ------------------
John  Smith  34          assistant manager
Donna  Patterson  

and in tab mode:

name surname  age   place   
John Smithonian     34 assistant manager
Donna Patterson

but the desirable result is:

name            surname             age         place              
--------------  ------------------  ----------  ------------------
John            Smith               34          assistant manager
Donna           Patterson                     

Is there anything I am not aware of? All the examples in the book I study from depict all columns properly aligned.

The same thing happens with tab mode.

I am working through Terminal.app on a mac with the pre-installed 3.8 version of SQLite

In later tests, I discovered that if my columns are all filled with information, it is possible to have all data aligned or not. It depends on the information. When I added a Phone Number column to one of the examples, the nice arrangement was lost.

Answer 1

With .mode tab the output is not guaranteed to be aligned because af the nature of the TAB characters. On the other hand .mode column should be working fine.

Since it looks like empty fields for NULL values are creating problems, you can change the way NULL values are displayed with .nullvalue SOMESTRING. I personally like .nullvalue [NULL].

Summing it up:

sqlite> .mode column
sqlite> .headers on
sqlite> .nullvalue [NULL]
sqlite> SELECT * FROM people;
name            surname             age         place              
--------------  ------------------  ----------  ------------------
John            Smith               34          assistant manager
Donna           Patterson           [NULL]      [NULL]

Also be sure to check the .width option to set the number of characters per column (negative values to right-align, 0 to reset) in case you need it.