February 4th, 2015 by depesz | Tags: , , , | 5 comments »
Did it help? If yes - maybe you can help me?

I was working today on some updates to client database. While doing it, I figured it would be simpler if I saw all “codenames" and ids of rows from dictionary table – not so big. But it was bigger than my screen – I have only 90 lines of text on screen, and there were ~ 200 rows of data in the table. So I started thinking – how to show this (codename, id) into more than one column, in psql.

First, let's make some simple sample table/data. We don't need 200 rows as I just want to show the mechanics.

So, let's say I will do:

$ create table test (id serial primary key, codename text not null unique);
CREATE TABLE
 
$ copy test (codename) from stdin;
shareholders
hollering
witchery
chameleon
revoking
murderess
auditing
confronts
pardons
wiener
pendulums
weaving
piteously
password
outlawed
taxis
dignities
croquettes
individualizing
chide
\.
COPY 20

Obviously, when I just select the data, I will see 20 rows of data, summary, header and separator:

select id, codename from test order by id;
 id |    codename     
----+-----------------
  1 | shareholders
  2 | hollering
  3 | witchery
  4 | chameleon
  5 | revoking
  6 | murderess
  7 | auditing
  8 | confronts
  9 | pardons
 10 | wiener
 11 | pendulums
 12 | weaving
 13 | piteously
 14 | password
 15 | outlawed
 16 | taxis
 17 | dignities
 18 | croquettes
 19 | individualizing
 20 | chide
(20 rows)

How could we make it show the same data, but in more than one column?

Well, I could do some awk trickery:

$ \o | awk 'NR < 3 || 0 == NR % 2 {print $0} NR>2 && 1 == NR%2 {printf "%s", $0}'
 
$ select id, codename from test order by id;
 id |    codename
----+-----------------
  1 | shareholders  2 | hollering
  3 | witchery  4 | chameleon
  5 | revoking  6 | murderess
  7 | auditing  8 | confronts
  9 | pardons 10 | wiener
 11 | pendulums 12 | weaving
 13 | piteously 14 | password
 15 | outlawed 16 | taxis
 17 | dignities 18 | croquettes
 19 | individualizing 20 | chide
(20 rows)
 
$ \o

While it kinda worked, it wasn't really nice – the columns are not aligned. But maybe I can do it in plain SQL?

First, let's try to add basic information – which column given row should go to. To make things interesting – let's go into 3 column layout:

$ select
    id,
    codename,
    (row_number() over (order by codename) - 1 ) % 3 as column_number,
    (row_number() over (order by codename) - 1 ) / 3 as row_number
from
    test
order by
    codename
;
 id |    codename     | column_number | row_number 
----+-----------------+---------------+------------
  7 | auditing        |             0 |          0
  4 | chameleon       |             1 |          0
 20 | chide           |             2 |          0
  8 | confronts       |             0 |          1
 18 | croquettes      |             1 |          1
 17 | dignities       |             2 |          1
  2 | hollering       |             0 |          2
 19 | individualizing |             1 |          2
  6 | murderess       |             2 |          2
 15 | outlawed        |             0 |          3
  9 | pardons         |             1 |          3
 14 | password        |             2 |          3
 11 | pendulums       |             0 |          4
 13 | piteously       |             1 |          4
  5 | revoking        |             2 |          4
  1 | shareholders    |             0 |          5
 16 | taxis           |             1 |          5
 12 | weaving         |             2 |          5
 10 | wiener          |             0 |          6
  3 | witchery        |             1 |          6
(20 rows)

Now, this is something we can build on.

Let's use it as CTE, and decrease number of rows (using group by), and add new columns with case…when…else…end:

with numbered as (
select
    id,
    codename,
    (row_number() over (order by codename) - 1 ) % 3 as column_number,
    (row_number() over (order by codename) - 1 ) / 3 as row_number
from
    test
)
select
    min( case when column_number = 0 then id else null end ) as id_1,
    min( case when column_number = 0 then codename else null end ) as codename_1,
    min( case when column_number = 1 then id else null end ) as id_2,
    min( case when column_number = 1 then codename else null end ) as codename_2,
    min( case when column_number = 2 then id else null end ) as id_3,
    min( case when column_number = 2 then codename else null end ) as codename_3
from numbered
group by row_number
order by row_number;
 id_1 |  codename_1  | id_2 |   codename_2    |  id_3  | codename_3 
------+--------------+------+-----------------+--------+------------
    7 | auditing     |    4 | chameleon       |     20 | chide
    8 | confronts    |   18 | croquettes      |     17 | dignities
    2 | hollering    |   19 | individualizing |      6 | murderess
   15 | outlawed     |    9 | pardons         |     14 | password
   11 | pendulums    |   13 | piteously       |      5 | revoking
    1 | shareholders |   16 | taxis           |     12 | weaving
   10 | wiener       |    3 | witchery        | [null] | [null]
(7 rows)

Kinda-nice. I mean – I got what I wanted, but the query is not really beautiful. It would be so great to get this kind of formatting help in psql itself 🙂

While I'm at this – it struck me – it's all nice, but I'd prefer to see it oriented the other way around – i.e. first fill first column, and then switch to next. This will require some heavier magic.

$ with base_info as (
    select id, codename from test
),
math as (
    select
        count(*),
        3::int4 as need_columns,
        ceil(count(*)::float8 / 3.0)::int4 as need_rows
    from
        base_info
)
select * from math;
 count | need_columns | need_rows 
-------+--------------+-----------
    20 |            3 |         7
(1 row)

This shows simple calculation, based on number of rows, how many rows we need for given number of columns. With this in place, I can use it to get “column_number" and “row_number" calculated:

with base_info as (
    select id, codename from test
),
math as (
    select
        count(*),
        3::int4 as need_columns,
        ceil(count(*)::float8 / 3.0)::int4 as need_rows
    from
        base_info
),
numbered as (
select
    id,
    codename,
    (row_number() over (order by codename) - 1 ) / need_rows as column_number,
    (row_number() over (order by codename) - 1 ) % need_rows as row_number
from
    base_info,
    math
)
select
    min( case when column_number = 0 then id else null end ) as id_1,
    min( case when column_number = 0 then codename else null end ) as codename_1,
    min( case when column_number = 1 then id else null end ) as id_2,
    min( case when column_number = 1 then codename else null end ) as codename_2,
    min( case when column_number = 2 then id else null end ) as id_3,
    min( case when column_number = 2 then codename else null end ) as codename_3
from numbered
group by row_number
order by row_number;
 id_1 | codename_1 | id_2 |   codename_2    |  id_3  |  codename_3  
------+------------+------+-----------------+--------+--------------
    7 | auditing   |   19 | individualizing |      5 | revoking
    4 | chameleon  |    6 | murderess       |      1 | shareholders
   20 | chide      |   15 | outlawed        |     16 | taxis
    8 | confronts  |    9 | pardons         |     12 | weaving
   18 | croquettes |   14 | password        |     10 | wiener
   17 | dignities  |   11 | pendulums       |      3 | witchery
    2 | hollering  |   13 | piteously       | [null] | [null]
(7 rows)

Note: the final query, after all the ctes – is the same as in previous example – it's just using group/min/case to convert values into grid based on their column_number/row_number.

We can also remove the math cte, and just do some math in base_info:

with base_info as (
    select
        id,
        codename,
        ceil((count(*) over ())::float8 / 3.0)::int4 as need_rows
    from test
),
numbered as (
select
    id,
    codename,
    (row_number() over (order by codename) - 1 ) / need_rows as column_number,
    (row_number() over (order by codename) - 1 ) % need_rows as row_number
from
    base_info
)
select
    min( case when column_number = 0 then id else null end ) as id_1,
    min( case when column_number = 0 then codename else null end ) as codename_1,
    min( case when column_number = 1 then id else null end ) as id_2,
    min( case when column_number = 1 then codename else null end ) as codename_2,
    min( case when column_number = 2 then id else null end ) as id_3,
    min( case when column_number = 2 then codename else null end ) as codename_3
from numbered
group by row_number
order by row_number;

But it's not all that different.

Anyway, I have the feeling that it can be done in a simpler way, but I'm out of ideas. For now at least. If you can do it better – please share your approach.

  1. 5 comments

  2. Feb 4, 2015

    It is interesting idea. Linux has interesting pager “column” – export PAGER=column psql -c “SELECT …”

  3. # Corey
    Feb 4, 2015
    $ export PAGER=column; psql analytics
     Timing is on.
     psql (9.4.0)
     Type "help" for help.
    

    Neat feature. Never knew about that one.

     [local]:ubuntu@analytics# select * from test;
     id |    codename        24 | chameleon          29 | pardons            34 | password           39 | individualizing
     ----+-----------------   25 | revoking           30 | wiener             35 | outlawed           40 | chide
      21 | shareholders       26 | murderess          31 | pendulums          36 | taxis             (20 rows)
      22 | hollering          27 | auditing           32 | weaving            37 | dignities
      23 | witchery           28 | confronts          33 | piteously          38 | croquettes
    

    (hope that formatted well, feel free to edit if it didn’t)

  4. Feb 5, 2015

    The problem with column is that it will break when codenames (well, any of the columns) will contain spaces.

  5. # xabolcs
    Feb 6, 2015

    Nice!

    First steps to PIVOT. 🙂

  6. # Pavel Luzanov
    Feb 11, 2015

    I think that you already made a main thing – transform original select statement to multi column output.
    Now we can write psql script to automatically made this transformation.
    Below script and it’s output.

    postgres=# i mc.sql
    select
        col1: id
        col2: codename
        from: test
    number of columns: 3
     
     id_1 |  codename_1  | id_2 |   codename_2    | id_3 | codename_3
    ------+--------------+------+-----------------+------+------------
        7 | auditing     |    4 | chameleon       |   20 | chide
        8 | confronts    |   18 | croquettes      |   17 | dignities
        2 | hollering    |   19 | individualizing |    6 | murderess
       15 | outlawed     |    9 | pardons         |   14 | password
       11 | pendulums    |   13 | piteously       |    5 | revoking
        1 | shareholders |   16 | taxis           |   12 | weaving
       10 | wiener       |    3 | witchery        |      |
    (7 rows)
    postgres=# i mc.sql
    select
        col1: reltype
        col2: relname
        from: pg_class where relname like 'pg_stat%'
    number of columns: 2
     
     reltype_1 |            relname_1             | reltype_2 |         relname_2
    -----------+----------------------------------+-----------+----------------------------
         11187 | pg_stat_activity                 |     11157 | pg_stat_all_indexes
         11125 | pg_stat_all_tables               |     11207 | pg_stat_bgwriter
         11193 | pg_stat_database                 |     11196 | pg_stat_database_conflicts
         11190 | pg_stat_replication              |     11161 | pg_stat_sys_indexes
         11133 | pg_stat_sys_tables               |     11199 | pg_stat_user_functions
         11164 | pg_stat_user_indexes             |     11140 | pg_stat_user_tables
         11129 | pg_stat_xact_all_tables          |     11137 | pg_stat_xact_sys_tables
         11203 | pg_stat_xact_user_functions      |     11144 | pg_stat_xact_user_tables
         11167 | pg_statio_all_indexes            |     11177 | pg_statio_all_sequences
         11147 | pg_statio_all_tables             |     11171 | pg_statio_sys_indexes
         11181 | pg_statio_sys_sequences          |     11151 | pg_statio_sys_tables
         11174 | pg_statio_user_indexes           |     11184 | pg_statio_user_sequences
         11154 | pg_statio_user_tables            |     10818 | pg_statistic
             0 | pg_statistic_relid_att_inh_index |     11087 | pg_stats
    (14 rows)
    /* _____ Start of mc.sql _____ */
    /* RETURNING DATA IN MULTIPLE COLUMNS */
     
    echo select
    prompt '    col1: ' col1
    prompt '    col2: ' col2
    prompt '    from: ' from
    prompt 'number of columns: ' n
    echo
     
    /* Generate first part of select statement */
    select
    'with numbered as ( ' ||
    'select ' ||
       :'col1'  || ', ' ||
       :'col2'  ||
    '  ,(row_number() over (order by ' ||:'col2'||') - 1 ) % '|| :n || ' as column_number' ||
    '  ,(row_number() over (order by ' ||:'col2'||') - 1 ) / '|| :n || ' as row_number' ||
    ' from ' || :'from' || 
    ') select ' as select_part1
    gset
     
    /* Generate second part of select statement */
    with t as (
    select row_number() over ()::text as row_num
          ,(row_number() over ()-1)::text as row_num2
      from generate_series (1, :n)
    )
    select string_agg (
    '    min( case when column_number = '||row_num2||' then '||:'col1'||' else null end ) as '||:'col1'||'_'||row_num||
    '   ,min( case when column_number = '||row_num2||' then '||:'col2'||' else null end ) as '||:'col2'||'_'||row_num
      , ',') as select_part2
    from t
    gset
     
    /* Generate final part of select statement */
    select
    '  from numbered' ||
    ' group by row_number' ||
    ' order by row_number'  as select_part3
    gset
     
    /* Run all together */
    :select_part1 :select_part2 :select_part3
     
     
    /* Cleanup */
    unset col1
    unset col2
    unset from
    unset n
    unset select_part1
    unset select_part2
    unset select_part3
    /* _____ End of mc.sql _____ */

Leave a comment