Problem with displaying "wide" tables in psql

Started by Sergey Muraviovover 12 years ago13 messageshackers
Jump to latest
#1Sergey Muraviov
sergey.k.muraviov@gmail.com

Hi.

Psql definitely have a problem with displaying "wide" tables.
Even in expanded mode, they look horrible.
So I tried to solve this problem.

Before the patch:
postgres=# \x 1
Expanded display (expanded) is on.
postgres=# \pset border 2
Border style (border) is 2.
postgres=# select * from pg_stats;

+-[ RECORD 1
]-----------+------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------+
| schemaname             | pg_catalog

|
| tablename | pg_proc

...

and after:

+-[ RECORD 1
]-----------+-----------------------------------------------------+
| schemaname | pg_catalog
|
| tablename | pg_proc
|
| attname | proname
|
| inherited | f
|
| null_frac | 0
|
| avg_width | 64
|
| n_distinct | -0.823159
|
| most_common_vals |
{max,min,overlaps,has_column_privilege,date_part,int4
,length,substring,sum,to_char,avg,int8,numeric,abs,generate_series,has_any_colum
n_privilege,has_database_privilege,has_foreign_data_wrapper_privilege,has_functi
on_privilege,has_language_privilege,has_schema_privilege,has_sequence_privilege,
has_server_privilege,has_table_privilege,has_tablespace_privilege,has_type_privi
lege,overlay,pg_has_role,point,stddev,stddev_pop,stddev_samp,text,time,timestamp
tz,timezone,var_pop,var_samp,variance,age,float4,float8,int2,isfinite,pg_get_vie
wdef,timestamp,bit_and,bit_or,mod,octet_length,polygon,substr,trunc,ts_headline,
ts_rank,ts_rank_cd,area,bit,bit_length,box,bpchar,btrim,circle,date,date_trunc,i
nterval,ishorizontal,isvertical,lag,lead,like,log,money,name,notlike,position,ro
und,timetz,to_ascii,abbrev,abstime,array_fill,array_to_json,array_to_string,ceil
,ceiling,center,char,char_length,character_length,count,daterange,enum_range,exp
,floor,format,generate_subscripts,get_bit,gin_extract_tsquery,gin_extract_tsvect
or}
|
...
| correlation | 0.254019
|
| most_common_elems |
|
| most_common_elem_freqs |
|
| elem_count_histogram |
|
+-[ RECORD 2
]-----------+-----------------------------------------------------+
| schemaname | pg_catalog
|
| tablename | pg_proc
|
| attname | pronamespace
|
| inherited | f
|
| null_frac | 0
|
| avg_width | 4
|
| n_distinct | 2
|
| most_common_vals | {11,12410}
|
| most_common_freqs | {0.995274,0.00472627}
|
| histogram_bounds |
|
| correlation | 1
|
| most_common_elems |
|
| most_common_elem_freqs |
|
| elem_count_histogram |
|
+-[ RECORD 3
]-----------+-----------------------------------------------------+

Best regards,
Sergey Muraviov

Attachments:

fix_psql_print_aligned_vertical.patchtext/x-patch; charset=US-ASCII; name=fix_psql_print_aligned_vertical.patchDownload+20-1
#2Pavel Stehule
pavel.stehule@gmail.com
In reply to: Sergey Muraviov (#1)
Re: Problem with displaying "wide" tables in psql

Hello

do you know a pager less trick

http://merlinmoncure.blogspot.cz/2007/10/better-psql-with-less.html

Regards

Pavel Stehule

2013/12/3 Sergey Muraviov <sergey.k.muraviov@gmail.com>

Show quoted text

Hi.

Psql definitely have a problem with displaying "wide" tables.
Even in expanded mode, they look horrible.
So I tried to solve this problem.

Before the patch:
postgres=# \x 1
Expanded display (expanded) is on.
postgres=# \pset border 2
Border style (border) is 2.
postgres=# select * from pg_stats;

+-[ RECORD 1
]-----------+------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----------------------------------------------------------+
| schemaname | pg_catalog

|
| tablename | pg_proc

...

and after:

+-[ RECORD 1
]-----------+-----------------------------------------------------+
| schemaname | pg_catalog
|
| tablename | pg_proc
|
| attname | proname
|
| inherited | f
|
| null_frac | 0
|
| avg_width | 64
|
| n_distinct | -0.823159
|
| most_common_vals |
{max,min,overlaps,has_column_privilege,date_part,int4

,length,substring,sum,to_char,avg,int8,numeric,abs,generate_series,has_any_colum

n_privilege,has_database_privilege,has_foreign_data_wrapper_privilege,has_functi

on_privilege,has_language_privilege,has_schema_privilege,has_sequence_privilege,

has_server_privilege,has_table_privilege,has_tablespace_privilege,has_type_privi

lege,overlay,pg_has_role,point,stddev,stddev_pop,stddev_samp,text,time,timestamp

tz,timezone,var_pop,var_samp,variance,age,float4,float8,int2,isfinite,pg_get_vie

wdef,timestamp,bit_and,bit_or,mod,octet_length,polygon,substr,trunc,ts_headline,

ts_rank,ts_rank_cd,area,bit,bit_length,box,bpchar,btrim,circle,date,date_trunc,i

nterval,ishorizontal,isvertical,lag,lead,like,log,money,name,notlike,position,ro

und,timetz,to_ascii,abbrev,abstime,array_fill,array_to_json,array_to_string,ceil

,ceiling,center,char,char_length,character_length,count,daterange,enum_range,exp

,floor,format,generate_subscripts,get_bit,gin_extract_tsquery,gin_extract_tsvect
or}
|
...
| correlation | 0.254019
|
| most_common_elems |
|
| most_common_elem_freqs |
|
| elem_count_histogram |
|
+-[ RECORD 2
]-----------+-----------------------------------------------------+
| schemaname | pg_catalog
|
| tablename | pg_proc
|
| attname | pronamespace
|
| inherited | f
|
| null_frac | 0
|
| avg_width | 4
|
| n_distinct | 2
|
| most_common_vals | {11,12410}
|
| most_common_freqs | {0.995274,0.00472627}
|
| histogram_bounds |
|
| correlation | 1
|
| most_common_elems |
|
| most_common_elem_freqs |
|
| elem_count_histogram |
|
+-[ RECORD 3
]-----------+-----------------------------------------------------+

Best regards,
Sergey Muraviov

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#3Sergey Muraviov
sergey.k.muraviov@gmail.com
In reply to: Pavel Stehule (#2)
Re: Problem with displaying "wide" tables in psql

Thank you for this trick.
It would be nice if this trick was documented.

However, with the pager I can't see wide value on one screen, select and
copy it entirely.
And I have to press many keys to find the necessary part of the value.
There is no such problems with the patch.

2013/12/3 Pavel Stehule <pavel.stehule@gmail.com>

Hello

do you know a pager less trick

http://merlinmoncure.blogspot.cz/2007/10/better-psql-with-less.html

Regards

Pavel Stehule

2013/12/3 Sergey Muraviov <sergey.k.muraviov@gmail.com>

Hi.

Psql definitely have a problem with displaying "wide" tables.
Even in expanded mode, they look horrible.
So I tried to solve this problem.

Before the patch:
postgres=# \x 1
Expanded display (expanded) is on.
postgres=# \pset border 2
Border style (border) is 2.
postgres=# select * from pg_stats;

+-[ RECORD 1
]-----------+------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----------------------------------------------------------+
| schemaname | pg_catalog

|
| tablename | pg_proc

...

and after:

+-[ RECORD 1
]-----------+-----------------------------------------------------+
| schemaname | pg_catalog
|
| tablename | pg_proc
|
| attname | proname
|
| inherited | f
|
| null_frac | 0
|
| avg_width | 64
|
| n_distinct | -0.823159
|
| most_common_vals |
{max,min,overlaps,has_column_privilege,date_part,int4

,length,substring,sum,to_char,avg,int8,numeric,abs,generate_series,has_any_colum

n_privilege,has_database_privilege,has_foreign_data_wrapper_privilege,has_functi

on_privilege,has_language_privilege,has_schema_privilege,has_sequence_privilege,

has_server_privilege,has_table_privilege,has_tablespace_privilege,has_type_privi

lege,overlay,pg_has_role,point,stddev,stddev_pop,stddev_samp,text,time,timestamp

tz,timezone,var_pop,var_samp,variance,age,float4,float8,int2,isfinite,pg_get_vie

wdef,timestamp,bit_and,bit_or,mod,octet_length,polygon,substr,trunc,ts_headline,

ts_rank,ts_rank_cd,area,bit,bit_length,box,bpchar,btrim,circle,date,date_trunc,i

nterval,ishorizontal,isvertical,lag,lead,like,log,money,name,notlike,position,ro

und,timetz,to_ascii,abbrev,abstime,array_fill,array_to_json,array_to_string,ceil

,ceiling,center,char,char_length,character_length,count,daterange,enum_range,exp

,floor,format,generate_subscripts,get_bit,gin_extract_tsquery,gin_extract_tsvect
or}
|
...
| correlation | 0.254019
|
| most_common_elems |
|
| most_common_elem_freqs |
|
| elem_count_histogram |
|
+-[ RECORD 2
]-----------+-----------------------------------------------------+
| schemaname | pg_catalog
|
| tablename | pg_proc
|
| attname | pronamespace
|
| inherited | f
|
| null_frac | 0
|
| avg_width | 4
|
| n_distinct | 2
|
| most_common_vals | {11,12410}
|
| most_common_freqs | {0.995274,0.00472627}
|
| histogram_bounds |
|
| correlation | 1
|
| most_common_elems |
|
| most_common_elem_freqs |
|
| elem_count_histogram |
|
+-[ RECORD 3
]-----------+-----------------------------------------------------+

Best regards,
Sergey Muraviov

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Best regards,
Sergey Muraviov

#4Alexander Korotkov
aekorotkov@gmail.com
In reply to: Sergey Muraviov (#3)
Re: Problem with displaying "wide" tables in psql

On Wed, Dec 4, 2013 at 11:20 AM, Sergey Muraviov <
sergey.k.muraviov@gmail.com> wrote:

Thank you for this trick.
It would be nice if this trick was documented.

However, with the pager I can't see wide value on one screen, select and
copy it entirely.
And I have to press many keys to find the necessary part of the value.
There is no such problems with the patch.

I think the solution is to provide proposed behaviour as an option.

------
With best regards,
Alexander Korotkov.

#5Pavel Stehule
pavel.stehule@gmail.com
In reply to: Sergey Muraviov (#3)
Re: Problem with displaying "wide" tables in psql

Hello

postgres=# \pset format wrapped
Output format (format) is wrapped.
postgres=# select 'afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf
sadf sa df sadfsadfasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf
sad fadsf';

?column?
---------------------------------------------------------------------------------------------------------------------
afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa df
sadfsadfasd fsad fsa df sadf asd fa sfd sadfsadf a.
.sdf sad f sadf sad fadsf
(1 row)

It works as expected

but it is not supported for row view. So any fix of this mode should be nice

Regards

Pavel

2013/12/4 Sergey Muraviov <sergey.k.muraviov@gmail.com>

Show quoted text

Thank you for this trick.
It would be nice if this trick was documented.

However, with the pager I can't see wide value on one screen, select and
copy it entirely.
And I have to press many keys to find the necessary part of the value.
There is no such problems with the patch.

2013/12/3 Pavel Stehule <pavel.stehule@gmail.com>

Hello

do you know a pager less trick

http://merlinmoncure.blogspot.cz/2007/10/better-psql-with-less.html

Regards

Pavel Stehule

2013/12/3 Sergey Muraviov <sergey.k.muraviov@gmail.com>

Hi.

Psql definitely have a problem with displaying "wide" tables.
Even in expanded mode, they look horrible.
So I tried to solve this problem.

Before the patch:
postgres=# \x 1
Expanded display (expanded) is on.
postgres=# \pset border 2
Border style (border) is 2.
postgres=# select * from pg_stats;

+-[ RECORD 1
]-----------+------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----------------------------------------------------------+
| schemaname | pg_catalog

|
| tablename | pg_proc

...

and after:

+-[ RECORD 1
]-----------+-----------------------------------------------------+
| schemaname | pg_catalog
|
| tablename | pg_proc
|
| attname | proname
|
| inherited | f
|
| null_frac | 0
|
| avg_width | 64
|
| n_distinct | -0.823159
|
| most_common_vals |
{max,min,overlaps,has_column_privilege,date_part,int4

,length,substring,sum,to_char,avg,int8,numeric,abs,generate_series,has_any_colum

n_privilege,has_database_privilege,has_foreign_data_wrapper_privilege,has_functi

on_privilege,has_language_privilege,has_schema_privilege,has_sequence_privilege,

has_server_privilege,has_table_privilege,has_tablespace_privilege,has_type_privi

lege,overlay,pg_has_role,point,stddev,stddev_pop,stddev_samp,text,time,timestamp

tz,timezone,var_pop,var_samp,variance,age,float4,float8,int2,isfinite,pg_get_vie

wdef,timestamp,bit_and,bit_or,mod,octet_length,polygon,substr,trunc,ts_headline,

ts_rank,ts_rank_cd,area,bit,bit_length,box,bpchar,btrim,circle,date,date_trunc,i

nterval,ishorizontal,isvertical,lag,lead,like,log,money,name,notlike,position,ro

und,timetz,to_ascii,abbrev,abstime,array_fill,array_to_json,array_to_string,ceil

,ceiling,center,char,char_length,character_length,count,daterange,enum_range,exp

,floor,format,generate_subscripts,get_bit,gin_extract_tsquery,gin_extract_tsvect
or}
|
...
| correlation | 0.254019
|
| most_common_elems |
|
| most_common_elem_freqs |
|
| elem_count_histogram |
|
+-[ RECORD 2
]-----------+-----------------------------------------------------+
| schemaname | pg_catalog
|
| tablename | pg_proc
|
| attname | pronamespace
|
| inherited | f
|
| null_frac | 0
|
| avg_width | 4
|
| n_distinct | 2
|
| most_common_vals | {11,12410}
|
| most_common_freqs | {0.995274,0.00472627}
|
| histogram_bounds |
|
| correlation | 1
|
| most_common_elems |
|
| most_common_elem_freqs |
|
| elem_count_histogram |
|
+-[ RECORD 3
]-----------+-----------------------------------------------------+

Best regards,
Sergey Muraviov

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Best regards,
Sergey Muraviov

#6Sergey Muraviov
sergey.k.muraviov@gmail.com
In reply to: Pavel Stehule (#5)
Re: Problem with displaying "wide" tables in psql

And my patch affects the row view only.

postgres=# \x 1
postgres=# create table wide_table (value text);
postgres=# insert into wide_table values ('afadsafasd fasdf asdfasd fsad
fas df sadf sad f sadf sadf sa df sadfsadfasd fsad fsa df sadf asd fa sfd
sadfsadf asdf sad f sadf sad fadsf');
postgres=# insert into wide_table values ('afadsafasd fasdf asdfasd');
postgres=# select * from wide_table;
-[ RECORD 1
]-------------------------------------------------------------------
-------------------------------------------------------------------
value | afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa df
sadfsad
fasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf sad fadsf
-[ RECORD 2
]-------------------------------------------------------------------
-------------------------------------------------------------------
value | afadsafasd fasdf

If we add a new column to this table and put the border on, we can see that
all values in the table have the same width.

postgres=# alter table wide_table add column id integer;
postgres=# \pset border 2
postgres=# select * from wide_table;
+-[ RECORD 1
]------------------------------------------------------------------
----------------------------------------------------------------------+
| value | afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf  sadf sa df
sadfs
adfasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf sad fadsf |
| id    |
                                                                      |
+-[ RECORD 2
]------------------------------------------------------------------
----------------------------------------------------------------------+
| value | afadsafasd fasdf asdfasd

|
| id |

                                                                      |
+-------+-----------------------------------------------------------------------
----------------------------------------------------------------------+

My patch tries to solve these problems:

-[ RECORD 1
]-------------------------------------------------------------------
value | afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa df
sadfsad
fasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf sad fadsf
-[ RECORD 2
]-------------------------------------------------------------------
value | afadsafasd fasdf asdfasd

and

+-[ RECORD 1
]-----------------------------------------------------------------+
| value | afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf  sadf sa df
sadfs
adfasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf sad fadsf
   |
| id    |
   |
+-[ RECORD 2
]-----------------------------------------------------------------+
| value | afadsafasd fasdf asdfasd
    |
| id    |
   |
+-------+----------------------------------------------------------------------+

Regards

2013/12/4 Pavel Stehule <pavel.stehule@gmail.com>

Hello

postgres=# \pset format wrapped
Output format (format) is wrapped.
postgres=# select 'afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf
sadf sa df sadfsadfasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf
sad fadsf';

?column?

---------------------------------------------------------------------------------------------------------------------
afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa df
sadfsadfasd fsad fsa df sadf asd fa sfd sadfsadf a.
.sdf sad f sadf sad fadsf
(1 row)

It works as expected

but it is not supported for row view. So any fix of this mode should be
nice

Regards

Pavel

2013/12/4 Sergey Muraviov <sergey.k.muraviov@gmail.com>

Thank you for this trick.
It would be nice if this trick was documented.

However, with the pager I can't see wide value on one screen, select and
copy it entirely.
And I have to press many keys to find the necessary part of the value.
There is no such problems with the patch.

2013/12/3 Pavel Stehule <pavel.stehule@gmail.com>

Hello

do you know a pager less trick

http://merlinmoncure.blogspot.cz/2007/10/better-psql-with-less.html

Regards

Pavel Stehule

2013/12/3 Sergey Muraviov <sergey.k.muraviov@gmail.com>

Hi.

Psql definitely have a problem with displaying "wide" tables.
Even in expanded mode, they look horrible.
So I tried to solve this problem.

Before the patch:
postgres=# \x 1
Expanded display (expanded) is on.
postgres=# \pset border 2
Border style (border) is 2.
postgres=# select * from pg_stats;

+-[ RECORD 1
]-----------+------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----------------------------------------------------------+
| schemaname | pg_catalog

|
| tablename | pg_proc

...

and after:

+-[ RECORD 1
]-----------+-----------------------------------------------------+
| schemaname | pg_catalog
|
| tablename | pg_proc
|
| attname | proname
|
| inherited | f
|
| null_frac | 0
|
| avg_width | 64
|
| n_distinct | -0.823159
|
| most_common_vals |
{max,min,overlaps,has_column_privilege,date_part,int4

,length,substring,sum,to_char,avg,int8,numeric,abs,generate_series,has_any_colum

n_privilege,has_database_privilege,has_foreign_data_wrapper_privilege,has_functi

on_privilege,has_language_privilege,has_schema_privilege,has_sequence_privilege,

has_server_privilege,has_table_privilege,has_tablespace_privilege,has_type_privi

lege,overlay,pg_has_role,point,stddev,stddev_pop,stddev_samp,text,time,timestamp

tz,timezone,var_pop,var_samp,variance,age,float4,float8,int2,isfinite,pg_get_vie

wdef,timestamp,bit_and,bit_or,mod,octet_length,polygon,substr,trunc,ts_headline,

ts_rank,ts_rank_cd,area,bit,bit_length,box,bpchar,btrim,circle,date,date_trunc,i

nterval,ishorizontal,isvertical,lag,lead,like,log,money,name,notlike,position,ro

und,timetz,to_ascii,abbrev,abstime,array_fill,array_to_json,array_to_string,ceil

,ceiling,center,char,char_length,character_length,count,daterange,enum_range,exp

,floor,format,generate_subscripts,get_bit,gin_extract_tsquery,gin_extract_tsvect
or}
|
...
| correlation | 0.254019
|
| most_common_elems |
|
| most_common_elem_freqs |
|
| elem_count_histogram |
|
+-[ RECORD 2
]-----------+-----------------------------------------------------+
| schemaname | pg_catalog
|
| tablename | pg_proc
|
| attname | pronamespace
|
| inherited | f
|
| null_frac | 0
|
| avg_width | 4
|
| n_distinct | 2
|
| most_common_vals | {11,12410}
|
| most_common_freqs | {0.995274,0.00472627}
|
| histogram_bounds |
|
| correlation | 1
|
| most_common_elems |
|
| most_common_elem_freqs |
|
| elem_count_histogram |
|
+-[ RECORD 3
]-----------+-----------------------------------------------------+

Best regards,
Sergey Muraviov

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Best regards,
Sergey Muraviov

--
Best regards,
Sergey Muraviov

#7Robert Haas
robertmhaas@gmail.com
In reply to: Sergey Muraviov (#6)
Re: Problem with displaying "wide" tables in psql

On Thu, Dec 5, 2013 at 1:09 AM, Sergey Muraviov
<sergey.k.muraviov@gmail.com> wrote:

And my patch affects the row view only.

To help us avoid forgetting about this patch, please add it here:

https://commitfest.postgresql.org/action/commitfest_view/open

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#8Jeff Janes
jeff.janes@gmail.com
In reply to: Sergey Muraviov (#1)
Re: Problem with displaying "wide" tables in psql

On Mon, Dec 2, 2013 at 10:45 PM, Sergey Muraviov <
sergey.k.muraviov@gmail.com> wrote:

Hi.

Psql definitely have a problem with displaying "wide" tables.
Even in expanded mode, they look horrible.
So I tried to solve this problem.

I get compiler warnings:

print.c: In function 'print_aligned_vertical':
print.c:1238: warning: ISO C90 forbids mixed declarations and code
print.c: In function 'print_aligned_vertical':
print.c:1238: warning: ISO C90 forbids mixed declarations and code

But I really like this and am already benefiting from it. No point in
having the string of hyphens between every record wrap to be 30 lines long
just because one field somewhere down the list does so. And configuring
the pager isn't much of a solution because the pager doesn't know that the
hyphens are semantically different than the other stuff getting thrown at
it.

Cheers,

Jeff

#9Sergey Muraviov
sergey.k.muraviov@gmail.com
In reply to: Jeff Janes (#8)
Re: Problem with displaying "wide" tables in psql

Hi.

I've improved the patch.
It works in expanded mode when either format option is set to wrapped
(\pset format wrapped), or we have no pager, or pager doesn't chop long
lines (so you can still use the trick).
Target output width is taken from either columns option (\pset columns 70),
or environment variable $COLUMNS, or terminal size.
And it's also compatible with any border style (\pset border 0|1|2).

Here are some examples:

postgres=# \x 1
postgres=# \pset format wrapped
postgres=# \pset border 0
postgres=# select * from wide_table;
* Record 1

value afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa df
sadfsadfa
sd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf sad fadsf
* Record 2

value afadsafasd fasdf asdfasd

postgres=# \pset border 1
postgres=# \pset columns 70
postgres=# select * from wide_table;
-[ RECORD 1 ]---------------------------------------------------------
value | afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa
| df sadfsadfasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f
| sadf sad fadsf
-[ RECORD 2 ]---------------------------------------------------------
value | afadsafasd fasdf asdfasd

postgres=# \pset border 2
postgres=# \pset columns 60
postgres=# select * from wide_table;
+-[ RECORD 1 ]---------------------------------------------+
| value | afadsafasd fasdf asdfasd fsad fas df sadf sad f  |
|       | sadf  sadf sa df sadfsadfasd fsad fsa df sadf as |
|       | d fa sfd sadfsadf asdf sad f sadf sad fadsf      |
+-[ RECORD 2 ]---------------------------------------------+
| value | afadsafasd fasdf asdfasd                         |
+-------+--------------------------------------------------+

Regards,
Sergey

2013/12/10 Jeff Janes <jeff.janes@gmail.com>

On Mon, Dec 2, 2013 at 10:45 PM, Sergey Muraviov <
sergey.k.muraviov@gmail.com> wrote:

Hi.

Psql definitely have a problem with displaying "wide" tables.
Even in expanded mode, they look horrible.
So I tried to solve this problem.

I get compiler warnings:

print.c: In function 'print_aligned_vertical':
print.c:1238: warning: ISO C90 forbids mixed declarations and code
print.c: In function 'print_aligned_vertical':
print.c:1238: warning: ISO C90 forbids mixed declarations and code

But I really like this and am already benefiting from it. No point in
having the string of hyphens between every record wrap to be 30 lines long
just because one field somewhere down the list does so. And configuring
the pager isn't much of a solution because the pager doesn't know that the
hyphens are semantically different than the other stuff getting thrown at
it.

Cheers,

Jeff

--
Best regards,
Sergey Muraviov

Attachments:

fix_psql_print_aligned_vertical_v2.patchtext/x-patch; charset=US-ASCII; name=fix_psql_print_aligned_vertical_v2.patchDownload+118-6
#10Sameer Thakur
samthakur74@gmail.com
In reply to: Sergey Muraviov (#9)
Re: Problem with displaying "wide" tables in psql

On Wed, Dec 11, 2013 at 11:13 PM, Sergey Muraviov
<sergey.k.muraviov@gmail.com> wrote:

Hi.

I've improved the patch.
It works in expanded mode when either format option is set to wrapped (\pset
format wrapped), or we have no pager, or pager doesn't chop long lines (so
you can still use the trick).
Target output width is taken from either columns option (\pset columns 70),
or environment variable $COLUMNS, or terminal size.
And it's also compatible with any border style (\pset border 0|1|2).

Here are some examples:

postgres=# \x 1
postgres=# \pset format wrapped
postgres=# \pset border 0
postgres=# select * from wide_table;
* Record 1
value afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa df
sadfsadfa
sd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf sad fadsf
* Record 2
value afadsafasd fasdf asdfasd

postgres=# \pset border 1
postgres=# \pset columns 70
postgres=# select * from wide_table;
-[ RECORD 1 ]---------------------------------------------------------
value | afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa
| df sadfsadfasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f
| sadf sad fadsf
-[ RECORD 2 ]---------------------------------------------------------
value | afadsafasd fasdf asdfasd

postgres=# \pset border 2
postgres=# \pset columns 60
postgres=# select * from wide_table;
+-[ RECORD 1 ]---------------------------------------------+
| value | afadsafasd fasdf asdfasd fsad fas df sadf sad f  |
|       | sadf  sadf sa df sadfsadfasd fsad fsa df sadf as |
|       | d fa sfd sadfsadf asdf sad f sadf sad fadsf      |
+-[ RECORD 2 ]---------------------------------------------+
| value | afadsafasd fasdf asdfasd                         |
+-------+--------------------------------------------------+

Regards,
Sergey

The patch applies and compile cleanly. I tried the following
\pset format wrapped
\pset columns 70.
Not in expanded mode
select * from wide_table works fine.
select * from pg_stats has problems in viewing. Is it that pg_stats
can be viewed easily only in expanded mode i.e. if columns displayed
are wrapped then there is no way to view results in non expanded mode?
regards
Sameer

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#11Sergey Muraviov
sergey.k.muraviov@gmail.com
In reply to: Sameer Thakur (#10)
Re: Problem with displaying "wide" tables in psql

Hello

2013/12/18 Sameer Thakur <samthakur74@gmail.com>

On Wed, Dec 11, 2013 at 11:13 PM, Sergey Muraviov
<sergey.k.muraviov@gmail.com> wrote:

Hi.

I've improved the patch.
It works in expanded mode when either format option is set to wrapped

(\pset

format wrapped), or we have no pager, or pager doesn't chop long lines

(so

you can still use the trick).
Target output width is taken from either columns option (\pset columns

70),

or environment variable $COLUMNS, or terminal size.
And it's also compatible with any border style (\pset border 0|1|2).

Here are some examples:

postgres=# \x 1
postgres=# \pset format wrapped
postgres=# \pset border 0
postgres=# select * from wide_table;
* Record 1
value afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa df
sadfsadfa
sd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf sad fadsf
* Record 2
value afadsafasd fasdf asdfasd

postgres=# \pset border 1
postgres=# \pset columns 70
postgres=# select * from wide_table;
-[ RECORD 1 ]---------------------------------------------------------
value | afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa
| df sadfsadfasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f
| sadf sad fadsf
-[ RECORD 2 ]---------------------------------------------------------
value | afadsafasd fasdf asdfasd

postgres=# \pset border 2
postgres=# \pset columns 60
postgres=# select * from wide_table;
+-[ RECORD 1 ]---------------------------------------------+
| value | afadsafasd fasdf asdfasd fsad fas df sadf sad f  |
|       | sadf  sadf sa df sadfsadfasd fsad fsa df sadf as |
|       | d fa sfd sadfsadf asdf sad f sadf sad fadsf      |
+-[ RECORD 2 ]---------------------------------------------+
| value | afadsafasd fasdf asdfasd                         |
+-------+--------------------------------------------------+

Regards,
Sergey

The patch applies and compile cleanly. I tried the following
\pset format wrapped
\pset columns 70.
Not in expanded mode
select * from wide_table works fine.
select * from pg_stats has problems in viewing. Is it that pg_stats
can be viewed easily only in expanded mode i.e. if columns displayed
are wrapped then there is no way to view results in non expanded mode?
regards
Sameer

The problem with non expanded mode is that all column headers have to be
displayed on one line.
Otherwise, it is difficult to bind values to columns.
And I have no idea how to solve this problem.

--
Best regards,
Sergey Muraviov

#12Peter Eisentraut
peter_e@gmx.net
In reply to: Sergey Muraviov (#9)
Re: Problem with displaying "wide" tables in psql

Please fix this:

src/bin/psql/print.c:1269: trailing whitespace.
src/bin/psql/print.c:1351: trailing whitespace.
src/bin/psql/print.c:1359: trailing whitespace.
src/bin/psql/print.c:1364: trailing whitespace.
src/bin/psql/print.c:2263: trailing whitespace.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#13Sergey Muraviov
sergey.k.muraviov@gmail.com
In reply to: Peter Eisentraut (#12)
Re: Problem with displaying "wide" tables in psql

fixed

2013/12/24 Peter Eisentraut <peter_e@gmx.net>

Please fix this:

src/bin/psql/print.c:1269: trailing whitespace.
src/bin/psql/print.c:1351: trailing whitespace.
src/bin/psql/print.c:1359: trailing whitespace.
src/bin/psql/print.c:1364: trailing whitespace.
src/bin/psql/print.c:2263: trailing whitespace.

--
Best regards,
Sergey Muraviov

Attachments:

fix_psql_print_aligned_vertical_v3.patchtext/x-patch; charset=US-ASCII; name=fix_psql_print_aligned_vertical_v3.patchDownload+123-12