3

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.

  • 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. – Matjaž Dec 13 '16 at 17:58
  • @Matjaž Perhaps this is the answer. Could you please provide an answer and explain it a bit further? – Yannis Dran Dec 14 '16 at 0:09
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.