Question 113936 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL literal value that is alternative to NULL

Are there other special literal values besides NULL in SQL / PostgresQL?

NULL is nice in that we can interpret NULL as the concept of "nothing" (i.e. missing, not available, not asked, not answered, etc.), and data columns of any type can have NULL values.

I would like another value that I can interpret as representing another concept (here the idea of "everything"), in the same result set.

Is there another special value that I can return in a query, which like NULL doesn’t type conflict?

Basically anything that doesn’t throw ERROR: For 'UNION', types varchar and numeric are inconsistent in this toy query:

select 1    as numeral, 'one' as name UNION ALL 
select 2    as numeral, 'two' as name UNION ALL 
select NULL as numeral, NULL  as name UNION ALL 
select -999 as numeral, -999  as name UNION ALL  -- type conflict
select '?'  as numeral, 'x'   as name            -- type conflict

Here,

  • -999 doesn’t work as its type conflicts with varchar columns
  • '~' doesn’t work as its type conflicts with numeric columns
  • NULL doesn’t work as it needs

More specifically here’s my actual case, counting combinations of values and also include "Overall" rows in the same query. Generally I won’t know or control the types of columns A, B, C in advance. And A, B, or C might also have NULL values which I would would still want to count separately.

SELECT A, COUNT(*) FROM table GROUP BY 1
UNION ALL
SELECT ?, COUNT(*) FROM table GROUP BY 1

and get a result set like:

A COUNT
NULL 2
1 3
2 5
3 10
(all) 20 
SELECT B, COUNT(*) FROM table GROUP BY 1
UNION ALL
SELECT ?, COUNT(*) FROM table GROUP BY 1

and get a result set like:

B COUNT
NULL 2
‘Circle’ 3
‘Line’ 5
‘Triangle’ 10
(all) 20
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    NOTE: Thanks to the comments above, I should completely rephrase this question as "How to COUNT/GROUP BY with ROLLUP using multiple columns of mixed/arbitrary/unknown types, and differentiate true NULL values from ROLLUP placeholders?"

    The correct answer I believe is provided by @a_horse_with_no_name: use ROLLUP with GROUPING.
    Below is is just me drafting that more completely with a revised example:

    This toy example has an integer and a string

    WITH table AS (
    select 1    as numeral, 'one' as name UNION ALL 
    select 2    as numeral, 'two' as name UNION ALL 
    select 2    as numeral, 'two' as name UNION ALL 
    select NULL as numeral, NULL  as name UNION ALL
    select NULL as numeral, NULL  as name UNION ALL
    select NULL as numeral, NULL  as name 
)
select name, numeral, COUNT(*), GROUPING_ID() 
FROM table
GROUP BY ROLLUP(1,2)
ORDER BY GROUPING_ID, name, numeral ;

    It returns the following result:

    numeral name count grouping_id note
    NULL NULL 3 0 both are true NULLs as grouping is 0
    1 one 1 0
    2 two 2 0
    NULL NULL 3 1 first is a true NULL, second is a ROLLUP
    1 NULL 1 1
    2 NULL 2 1
    NULL NULL 6 3 both NULLs are ROLLUPs

  2. 0

    You can use function CAST to convert the format to VARCHAR to be considered as string.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search