When using the CREATE TABLE x AS SELECT (...) structure, the table being created may need to have more columns than the source table it is being created from. A common scenario would be a destination table with data feeding from multiple sources, and the sources have most, but not all, columns in common. The destination table would have a superset of all the columns from all the sources, and individual sources would not have all the columns in the destination table.
If the destination table is initially created from one of the sources, the SELECT portion of the CTAS structure will have placeholder constant values to populate the columns that are not in the source table. If the desired constant value is NULL, an error will occur:
Invalid operation error: Failed to create table as select due to unsupported data typeThe error occurs because the data type for a placeholder constant column is inferred from the constant value, and NULL does not provide a valid data type.
To avoid the error, cast the NULL placeholder constant as the desired datatype.
EXAMPLE
/*******************************/* Create test source table/*******************************/CREATE FACT TABLE IF NOT EXISTS source (int_col INT NOT NULL, text_col TEXT NULL) PRIMARY INDEX int_col;INSERT INTO source VALUES (1234,'xxx');/*******************************/* This statement fails/*******************************/CREATE FACT TABLE source_ctas PRIMARY index int_col AS (SELECT int_col, text_col,NULL AS text2_col FROM SOURCE);/*******************************/* This statement succeeds/*******************************/CREATE FACT TABLE source_ctas PRIMARY index int_col AS (SELECT int_col, text_col,NULL::TEXT AS text2_col FROM SOURCE);