SQLite3 – Set Column Width Equal to Header Width

formatselectsqlite

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:

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.
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.

Best Answer

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.

Related Solutions

Showing data in a table horizontally

It looks to me like you are trying to 'pivot' the data, which can be done with multiple case statements:

test data:

create table timestream(
  idTimestream integer primary key autoincrement
, time int not null
, value float not null
, idSensor integer not null);

insert into timestream(time, value, idSensor) values(1,0.1,100);

insert into timestream(time, value, idSensor) values(1,0.2,101);

insert into timestream(time, value, idSensor) values(1,0.3,102);

insert into timestream(time, value, idSensor) values(2,0.4,101);

query:

select time, sum(case idSensor when 100 then value end) as s100,
             sum(case idSensor when 101 then value end) as s101,
             sum(case idSensor when 102 then value end) as s102
from timestream
group by time;

time | s100 | s101 | s102
:--- | :--- | :--- | :---
1    | 0.1  | 0.2  | 0.3 
2    | null | 0.4  | null

dbfiddle here

Postgresql – Advice on scripting a “whitewashed” and downsized development db from main db

What you're referring to is usually called Test Data Management. There are several commercial solutions, e.g. IBM Optim Test Data Management (disclosure: I work for IBM, but don't speak for them). I'm not aware of any free products that can perform that function, so I'm afraid you'll have to stick to your Python scripts unless you have deep pockets.

On a side note, I would suggest using the same database platform, i.e. PostgreSQL, for both production and development -- it will probably save you some troubleshooting time later.

Related Question