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.

• Matjaž: Since the empty spaces (NULL values) seem to be the problem, have you tried to change the way they are displayed instead of an empty space? .nullvalue [NULL] for example.
• Yannis Dran: @Matjaž Perhaps this is the answer. Could you please provide an answer and explain it a bit further?

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.

• Yannis Dran: took me three years, but I finaly made it, to award your answer. ;)
• Matjaž: Appreciated! :)
• Digicrat: These commands can be added to ~/.sqliterc to make them persistent. Courtesy of stackoverflow.com/questions/5240643/… (the second result in the search that brought me here).