2

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
4
  • 1
    Please Edit your question and add the CREATE TABLE statements for the tables in question adding the desired output. Those answering need a Minimal, Complete, and Verifiable example. Use formatted text please, no screen shots. Jun 28, 2018 at 18:23
  • 1
    Have you tried to use coalesce: postgresqltutorial.com/postgresql-coalesce ?
    – AMG
    Jun 28, 2018 at 19:37
  • Bingo! Coalesce was the word I was missing. Post this as the answer and I'll mark it as correct! @AMG
    – agent nate
    Jun 28, 2018 at 20:53
  • SELECT username, COALESCE(some_column_name_1, 0), COALESCE(some_column_name_2, 0) 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, );
    – agent nate
    Jun 28, 2018 at 20:53

1 Answer 1

4

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:

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.

1
  • Thank you for mentioning this second parameter variant Erwin!
    – agent nate
    Jul 2, 2018 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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