Snowflake - How do I handle a bad Virtual Column after it is INSERTed?

user_17990172 0
Viewed 58

I am surprise that I can insert a Row that results in a Virtual Column going bad. How then do I detect the bad Virtual Columns and handle it (especially in code)?

CREATE TABLE TEST
(
  REC_DATE          VARCHAR2(500),
  REC_TIME          VARCHAR2(500),
  REC_DATETIME      DATETIME AS TO_TIMESTAMP( REC_DATE || ' ' || REC_TIME, 'DD/MM/YYYY HH24:MI:SS'  )
);

INSERT INTO TEST ( REC_DATE, REC_TIME ) VALUES ( '02/02/2022', '02:22:22' );
INSERT INTO TEST ( REC_DATE, REC_TIME ) VALUES ( '03/03/2023', '03:33:33' );

And then insert a bad row that results in the bad Virtual Column:

INSERT INTO TEST ( REC_DATE, REC_TIME ) VALUES ( '02/02/2022', '99:22:22' );

SELECT * FROM TEST;

and you should get this error:

"SQL compilation error: error line 1 at position 7 invalid identifier 'TEST'"

I wonder how you would detect and weed out the bad rows, especially if there is a lot of data?

snowflake-cloud-data-platform
1 Answers

A quick way to fix this is to use a safe definition for the virtual column.

Step 1: Drop the virtual column.

Step 2: Recreate the virtual column with a safe op — instead of TO_TIMESTAMP use TRY_TO_TIMESTAMP.

alter table test
drop column rec_datetime
;

alter table test
add column  REC_DATETIME    DATETIME AS try_TO_TIMESTAMP( REC_DATE || ' ' || REC_TIME, 'DD/MM/YYYY HH24:MI:SS'  )
;

select * from test;

enter image description here

To identify the bad rows you can look for nulls once the previous fix is applied:

select * from test 
where rec_date is not null 
and rec_datetime is null
user_1324380
Related