Default values for crosstab results

Question

I'm trying to write my first crosstab query in Postgres. Some of the results have no entries and so the rows that are returned have null entries. How do I set a default value for those entries?

Here is a row that is returned:

-[ RECORD 1 ]-------------------+-----
username                        | some name
some_column_name_1              | 2
some_column_name_2              |

Here is my crosstab query:

SELECT * 
FROM crosstab( 'select username, stage, count from some_view order by 1,2') 
     AS final_result(username TEXT,
         "some_column_name_1" BIGINT,
         "some_column_name_2" BIGINT,
);

I've tried little things like this with no luck:

"some_column_name_1" BIGINT 0
"some_column_name_1" BIGINT default 0
"some_column_name_1" BIGINT set default 0

score 4 · Accepted Answer · answered Jun 30 '18 at 02:45

More importantly, if ...

some of the results have no entries

You need crosstab(text, text) - the variant with 2 input parameters instead of crosstab(text) you are using now to avoid incorrect results. Detailed explanation:

PostgreSQL Crosstab Query

So, assuming the respective values in the column stage are 'some_column_name_1' and 'some_column_name_2':

SELECT username
     , COALESCE(col1, 0) AS some_column_name_1  -- sets default for NULL
     , COALESCE(col2, 0) AS some_column_name_2
FROM   crosstab(
   'select username, stage, count from some_view order by 1,2'
  , $$VALUES ('some_column_name_1'), ('some_column_name_2')$$  -- !!!
   ) AS final_result (username text, col1 bigint, col2 bigint);

And COALESCE provides the default value for NULL that you asked for - like AMG commented.

Default values for crosstab results

1 Answers1