Cast to NULL to unite tables (CH)

fujidaon 0
Viewed 21

I want to UNION two tables with different number of columns. For example to unite in PostgreSQL I can run following query:

SELECT
id,
last_update_date,
metric_key,
metric_value
FROM schema.table1

UNION ALL

SELECT 
id,
last_update_date,
NULL::text as metric_key,
NULL::numeric as metric_value
FROM schema.table2

But in CH i cannot cast to NULL NULL::text, I receive an error:

Cannot convert NULL to a non-nullable type: While processing CAST(NULL, 'text')

I can run NULL AS metric_key but it has data type Nothing, but i want to specify data type, because Apache NiFi (using later to retrieve data from view) cannot read Nothing data type. How I can do it?

clickhouse
1 Answers

By default types in CH are not Nullable

you need to use Nullable keyword

select
      NULL::Nullable(text),
      NULL::Nullable(int)
                             ;

SELECT
    CAST(NULL, 'Nullable(text)'),
    CAST(NULL, 'Nullable(int)')


┌─CAST(NULL, 'Nullable(text)')─┬─CAST(NULL, 'Nullable(int)')─┐
│ ᴺᵁᴸᴸ                         │                        ᴺᵁᴸᴸ │
└──────────────────────────────┴─────────────────────────────┘
Denny Crane0
Related