Create Null Column in Snowflake Query and Define Data Type

Question

I have a View1 with 15 columns and I would like to Union it to View2 which has 100 columns. The 15 columns in View1 align with 15 of the 100 columns in View2 but from my understanding, I can't Union 2 tables or views in Snowflake unless they have the same set of columns.

Instead, I'm trying to add the other 85 columns with a NULL value to View1 and define it in the View1 query but the data types for those 85 missing columns vary (boolean, string, integer, date, etc). I can write something like this:

CREATE VIEW "VIEW1" AS
SELECT FIELD1
      ,FIELD2
      ,...
      ,FIELD15
      ,NULL AS FIELD16
      ,NULL AS FIELD17
      ,...
      ,NULL AS FIELD100
FROM SOURCE_TABLE

But Snowflake is automatically assigning Fields 16-100 a VARCHAR data type which will still create a problem when I go to union View1 and View2. Is there a way to define the data type in the query? Sort of like "NULL AS FIELD16 BOOLEAN"? If not, is there another way (or better way) of doing this? I can't alter the source table, unfortunately.

Lukasz Szozda · Accepted Answer · 2022-06-16 15:23:16Z

1

Is there a way to define the data type in the query? Sort of like "NULL AS FIELD16 BOOLEAN"?

NULL by design has TEXT data type:

CREATE OR REPLACE VIEW v1 
AS
SELECT NULL AS c;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ILIKE 'v1';
-- DATA TYPE
-- TEXT

When set operator UNION ALL is applied the matching column will be subject to Implicit Casting (“Coercion”).

General Usage Notes

Make sure that the data type of each column is consistent across the rows from different sources.

In order to avoid it, NULL value should be explicitly casted to proper data type:

CREATE VIEW "VIEW1" AS
SELECT FIELD1
      ,FIELD2
      ,...
      ,FIELD15
      ,NULL::BOOLEAN AS FIELD16
      ,NULL::BOOLEAN AS FIELD17
      ,...
      ,NULL::INT AS FIELD100
FROM SOURCE_TABLE

answered Jun 16, 2022 at 15:23

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2022-06-16 06:30:52Z

0

You could try to cast the NULL literals to the type you want, e.g.

CREATE VIEW "VIEW1" AS
SELECT FIELD1,
       FIELD2,
       ...
       FIELD15,
       CAST(NULL AS BOOLEAN) AS FIELD16,
       ...
FROM SOURCE_TABLE;

answered Jun 16, 2022 at 6:30

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Raja Sekhar Reddy Moola · Accepted Answer · 2022-06-17 10:13:57Z

0

Type casting will clear the data type problem.

answered Jun 17, 2022 at 10:13

Raja Sekhar Reddy Moola

111 bronze badge

Collectives™ on Stack Overflow

Create Null Column in Snowflake Query and Define Data Type

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related