1

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
  • 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. – Evan Carroll Jun 28 '18 at 18:23
  • 1
    Have you tried to use coalesce: postgresqltutorial.com/postgresql-coalesce ? – AMG Jun 28 '18 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 '18 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 '18 at 20:53
2

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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