Other special values besides NULL?

Are there other special values like NULL in Vertica SQL ?

NULL is nice in that data columns of any type can have NULL values.
We can interpret NULL as the concept of "N/A" (i.e. missing, not available, not asked, not answered, etc.).
I would also like to represent another concept, the idea of Overall, in the same result set.

Is there another special value that I can return in a query, other than NULL, but which like NULL:

  • can be returned in any type of column integer, float, string, date, etc.
  • doesn't match any actual finite value that might be in the data
  • 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 ??? as numeral, ??? as name;



  • moshegmosheg Vertica Employee Administrator

    NaN (Not a Number) does not equal anything, not even another NaN.
    You can query for them using the predicate … WHERE column != column
    See: https://www.vertica.com/blog/vertica-quick-tip-query-nan-values

    create table NaN_Test (c1 float);
    insert into NaN_Test select 'NaN';
    select * from NaN_Test;
    (1 row)
    select * from NaN_Test where c1 = 'NaN';
    (0 rows)
    select * from NaN_Test where c1 != c1;
    (1 row)
  • edited November 2022


    Circling back with new words from exploration, my actual goal was to calculate COUNT/GROUP BY with rollups and differentiate counts of actual NULL values from rollups across values. For this the Vertica ROLLUP(x1, x2, ...) feature is _almost_ perfect.

    But there are three complications in the toy example below:

    • the BY variables are of unknown possibly mixed types
    • I'd like to differentiate true NULL values from rollups but they both return as NULL
    • I'd like to merge the results back but you can't merge on NULL values

    Where I'm netting out is that it's practical if not pretty to:

    • cast all by columns to VARCHAR to avoid type conflicts
    • coalesce all NULL values to a special string to differentiate rollups from true nulls
    • use the vSQL syntax GROUP BY ROLLUP(1,2,3...)
    • on return, interpret NULL as a rollup value and the special string as a NULL
        base AS (
            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
        summed AS (
            SELECT numeral, name, GROUPING_ID(),count(*) 
            FROM base
            GROUP BY ROLLUP(1,2)
    SELECT * FROM summed
    ORDER BY grouping_id, numeral, name
    numeral name grouping_id count
    NULL NULL 0 1
    1 one 0 1
    2 two 0 1
    NULL NULL 1 1
    1 NULL 1 1
    2 NULL 1 1
    NULL NULL 3 3
  • VValdarVValdar Vertica Employee Employee

    Hi Pieter_Sheth-Vo,

    You're only lacking the knowledge of the grouping function that let you know if it's a base line row or a rolled up one.

    with cte_base (numeral, name) as
    select 1   , 'one' union all
    select 2   , 'two' union all
    select null, null
      select case grouping(numeral) when 1 then 999        else numeral end as numeral
           , case grouping(name)    when 1 then 'All name' else name    end as name
           , count(*)
        from cte_base
    group by rollup(numeral, name)
    order by grouping_id(), 1, 2;
    numeral  name     count
    -------- -------- -----
     [NULL]  [NULL]       1
          1  one          1
          2  two          1
     [NULL]  All name     1
          1  All name     1
          2  All name     1
        999  All name     3
  • moshegmosheg Vertica Employee Administrator

    ..and a VSQL count scale example:

    \set SCALE_MAX_CHARS 40
    with list1 (numeral, name) as
    select 1   , 'one' union all
    select 2   , 'two' union all
    select 2   , 'two' union all
    select 3   , 'three' union all
    select 3   , 'three' union all
    select 3   , 'three' union all
    select null, null
    list2 as (select case grouping(numeral) when 1 then 'TOTAL'    else ISNULL(TO_CHAR(numeral),'[NULL]') end as numeral
           , case grouping(name)    when 1 then '' else ISNULL(name,'[NULL]') end as name
           , grouping_id() as gid
           , count(*) as count
        from list1
    group by rollup(numeral,name)
    order by grouping_id(), 1, 2),
    norm as (select max(count / :SCALE_MAX_CHARS)::INT as norm_factor from list2)
    select numeral, name,
           case when gid > 0 then count end as count,
           case gid when 1 then REPEAT('*', ((count / (norm_factor +1))::INT)) else '' end as Count_Scale
    from list2, norm
    order by gid ,1,2;
     numeral |  name  | count | Count_Scale
     1       | one    |       |
     2       | two    |       |
     3       | three  |       |
     [NULL]  | [NULL] |       |
     1       |        |     1 | *
     2       |        |     2 | **
     3       |        |     3 | ***
     [NULL]  |        |     1 | *
     TOTAL   |        |     7 |
    (9 rows)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file