Search code examples
sqlamazon-athenaprestotrino

'NULL as' returns error on creating table from query

I want to create a new table from a query with new column with a NULL value for every row.

For this purpose, I use the following query:

CREATE TABLE my_table AS SELECT _id, NULL as value, true as __deleted 
FROM test_table

Which gives me the following error:

SYNTAX_ERROR: line 1:1: Column type is unknown: value. You may need to manually clean the data at location

Even though the query without the table creation SELECT _id, NULL as value, true as __deleted FROM test_table works well.

Is my query invalid for table creation? I did not find any info about such limitations.

Solution

  • If you check docs for CREATE TABLE you will see that column type is mandatory for every column declaration, and select typeof(null) will return unknown. A possible workaround is to cast NULL to some data type:

    CREATE TABLE my_table AS 
SELECT _id, cast(NULL as varchar) value, true __deleted 
FROM test_table