WITH DEFAULT

Translation reference to convert Teradata WITH DEFAULT clause in column definitions to Snowflake Scripting

Description

Teradata's WITH DEFAULT clause sets a system-default value to columns that are inserted with no values. This value is typically the equivalent of zero or empty.

Syntax:

WITH DEFAULT

The following table shows Teradata's data types, their corresponding type in Snowflake, and the default value to be set if supported.

Teradata	Snowflake	Default Value
BLOB[(n)]	BYTE	NOT SUPPORTED
BYTE[(n)]	BYTE	NOT SUPPORTED
VARBYTE[(n)]	BYTE	NOT SUPPORTED
BIGINT	BIGINT	0
BYTEINT	BYTEINT	0
DECIMAL [(n[,m])]	DECIMAL	0
DOUBLE PRECISION	DOUBLE PRECISION	0
FLOAT	FLOAT	0
INTEGER	INTEGER	0
NUMBER(n[,m])	NUMBER	0
NUMBER[(*[,m])]	NUMBER	0
NUMERIC [(n[,m])]	NUMERIC	0
REAL	REAL	0
SMALLINT	SMALLINT	0
DATE	DATE	CURRENT_DATE
TIME [(n)]	TIME	CURRENT_TIME
TIMESTAMP [(n)]	TIMESTAMP	CURRENT_TIMESTAMP
TIMESTAMP WITH TIME ZONE	TIMESTAMP_TZ	LOCALTIMESTAMP
INTERVAL DAY [(n)]	VARCHAR(21)	'0DAY'
INTERVAL DAY [(n)] TO HOUR	VARCHAR(21)	'0DAY'
INTERVAL DAY [(n)] TO MINUTE	VARCHAR(21)	'0DAY'
INTERVAL DAY [(n)] TO SECOND	VARCHAR(21)	'0DAY'
INTERVAL HOUR [(n)]	VARCHAR(21)	'0HOUR'
INTERVAL HOUR [(n)] TO MINUTE	VARCHAR(21)	'0HOUR'
INTERVAL HOUR [(n)] TO SECOND	VARCHAR(21)	'0HOUR'
INTERVAL MINUTE [(n)]	VARCHAR(21)	'0MINUTE'
INTERVAL MINUTE [(n)] TO SECOND [(m)]	VARCHAR(21)	'0MINUTE'
INTERVAL MONTH	VARCHAR(21)	'0MONTH'
INTERVAL SECOND [(n,[m])]	VARCHAR(21)	'0SECOND'
INTERVAL YEAR [(n)]	VARCHAR(21)	'0YEAR'
INTERVAL YEAR [(n)] TO MONTH	VARCHAR(21)	'0YEAR'
CHAR[(n)]	CHAR	''
CHARACTER(n) CHARACTER SET GRAPHIC	-	NOT SUPPORTED
CLOB	-	NOT SUPPORTED
CHAR VARYING(n)	VARCHAR	''
LONG VARCHAR	-	NOT SUPPORTED
LONG VARCHAR CHARACTER SET GRAPHIC	-	NOT SUPPORTED
VARCHAR(n)	VARCHAR	''
VARCHAR(n) CHARACTER SET GRAPHIC	-	NOT SUPPORTED
PERIOD(DATE)	VARCHAR(24)	NOT SUPPORTED
PERIOD(TIME [(n)])	VARCHAR(24)	NOT SUPPORTED
PERIOD(TIMESTAMP [(n)])	VARCHAR(24)	NOT SUPPORTED

Sample Source Patterns

Teradata

Query

CREATE TABLE SAMPLE_TABLE
(
    ID INT,

    -- Numeric Types
    big_integer_col BIGINT WITH DEFAULT,
    byteint_col BYTEINT WITH DEFAULT,
    decimal_col DECIMAL(10,2) WITH DEFAULT,
    double_precision_col DOUBLE PRECISION WITH DEFAULT,
    float_col FLOAT WITH DEFAULT,
    integer_col INTEGER WITH DEFAULT,
    number_col NUMBER WITH DEFAULT,
    numeric_col NUMERIC(10,2) WITH DEFAULT,
    real_col REAL WITH DEFAULT,
    smallint_col SMALLINT WITH DEFAULT,

    -- Character Types
    char_col CHAR(50) WITH DEFAULT,
    character_col CHARACTER(50) WITH DEFAULT,
    --clob_col CLOB,
    char_varying_col CHAR VARYING(100) WITH DEFAULT,
    --long_varchar_col LONG VARCHAR WITH DEFAULT,
    --long_varchar_graphic_col LONG VARCHAR CHARACTER SET GRAPHIC WITH DEFAULT,
    varchar_col VARCHAR(255) WITH DEFAULT,
    --varchar_graphic_col VARCHAR(255) CHARACTER SET GRAPHIC WITH DEFAULT,

    -- Date and Time Types
    date_col DATE WITH DEFAULT,
    time_col TIME WITH DEFAULT,
    time_precision_col TIME(6) WITH DEFAULT,
    timestamp_col TIMESTAMP WITH DEFAULT,
    timestamp_precision_col TIMESTAMP(6) WITH DEFAULT,
    tz_timestamp_col TIMESTAMP WITH TIME ZONE WITH DEFAULT,
    tz_timestamp_precision_col TIMESTAMP(6) WITH TIME ZONE WITH DEFAULT,    
    interval_col INTERVAL DAY(4) WITH DEFAULT,
    interval_day_to_hour_col INTERVAL DAY(4) TO HOUR WITH DEFAULT,
    interval_hour_col INTERVAL HOUR(2) WITH DEFAULT,
    interval_minute_col INTERVAL MINUTE(2) WITH DEFAULT,
    interval_month_col INTERVAL MONTH WITH DEFAULT,
    interval_second_col INTERVAL SECOND(2) WITH DEFAULT,
    interval_year_col INTERVAL YEAR(4) WITH DEFAULT,

    -- Binary Types
    -- blob_col BLOB(1000),
    byte_col BYTE(1000) WITH DEFAULT,
    varbyte_col VARBYTE(1000) WITH DEFAULT
);

Snowflake Scripting

Query

CREATE TABLE SAMPLE_TABLE
(
    ID INT,
    -- Numeric Types
    big_integer_col BIGINT DEFAULT 0,
    byteint_col BYTEINT DEFAULT 0,
    decimal_col DECIMAL(10,2) DEFAULT 0,
    double_precision_col DOUBLE PRECISION DEFAULT 0,
    float_col FLOAT DEFAULT 0,
    integer_col INTEGER DEFAULT 0,
    number_col NUMBER(38, 18) DEFAULT 0,
    numeric_col NUMERIC(10,2) DEFAULT 0,
    real_col REAL DEFAULT 0,
    smallint_col SMALLINT DEFAULT 0,
    -- Character Types
    char_col CHAR(50) DEFAULT '',
    character_col CHARACTER(50) DEFAULT '',
    --clob_col CLOB,
    char_varying_col CHAR VARYING(100) DEFAULT '',
    --long_varchar_col LONG VARCHAR WITH DEFAULT,
    --long_varchar_graphic_col LONG VARCHAR CHARACTER SET GRAPHIC WITH DEFAULT,
    varchar_col VARCHAR(255) DEFAULT '',
    --varchar_graphic_col VARCHAR(255) CHARACTER SET GRAPHIC WITH DEFAULT,

    -- Date and Time Types
    date_col DATE DEFAULT CURRENT_DATE,
    time_col TIME DEFAULT CURRENT_TIME,
    time_precision_col TIME(6) DEFAULT CURRENT_TIME(6),
    timestamp_col TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    timestamp_precision_col TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    tz_timestamp_col TIMESTAMP_TZ DEFAULT LOCALTIMESTAMP,
    tz_timestamp_precision_col TIMESTAMP_TZ(6) DEFAULT LOCALTIMESTAMP(6),
    interval_col VARCHAR(21) /*** MSC-WARNING - MSCEWI1036 - INTERVAL DAY(4) DATA TYPE CONVERTED TO VARCHAR ***/ DEFAULT '0DAY',
    interval_day_to_hour_col VARCHAR(21) /*** MSC-WARNING - MSCEWI1036 - INTERVAL DAY(4) TO HOUR DATA TYPE CONVERTED TO VARCHAR ***/ DEFAULT '0DAY',
    interval_hour_col VARCHAR(21) /*** MSC-WARNING - MSCEWI1036 - INTERVAL HOUR(2) DATA TYPE CONVERTED TO VARCHAR ***/ DEFAULT '0HOUR',
    interval_minute_col VARCHAR(21) /*** MSC-WARNING - MSCEWI1036 - INTERVAL MINUTE(2) DATA TYPE CONVERTED TO VARCHAR ***/ DEFAULT '0MINUTE',
    interval_month_col VARCHAR(21) /*** MSC-WARNING - MSCEWI1036 - INTERVAL MONTH DATA TYPE CONVERTED TO VARCHAR ***/ DEFAULT '0MONTH',
    interval_second_col VARCHAR(21) /*** MSC-WARNING - MSCEWI1036 - INTERVAL SECOND (2) DATA TYPE CONVERTED TO VARCHAR ***/ DEFAULT '0SECOND',
    interval_year_col VARCHAR(21) /*** MSC-WARNING - MSCEWI1036 - INTERVAL YEAR(4) DATA TYPE CONVERTED TO VARCHAR ***/ DEFAULT '0YEAR',
    -- Binary Types
    -- blob_col BLOB(1000),
    byte_col BINARY
--                    WITH DEFAULT
--    /*** MSC-ERROR - MSCEWI1021 - WITH DEFAULT FOR 'BYTE(1000)' NOT SUPPORTED ***/
                                                                                  ,
    varbyte_col BINARY(1000)
--                             WITH DEFAULT
--    /*** MSC-ERROR - MSCEWI1021 - WITH DEFAULT FOR 'VARBYTE(1000)' NOT SUPPORTED ***/
);

Known Issues

1. Unsupported types

As shown in the table in the description table, some types are not supported and no default value will be set when transforming the WITH DEFAULT clause.

Related EWIs

MSCEWI1021: Node is not supported.