WITH DEFAULT

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

Some parts in the output code are omitted for clarity reasons.

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

IN -> Teradata_01.sql
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

OUT -> Teradata_01.sql
CREATE OR REPLACE 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)
--                              WITH DEFAULT
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0021 - WITH DEFAULT FOR 'NUMBER' NOT SUPPORTED IN SNOWFLAKE ***/!!!
                                                                                                      ,
    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)
--                                WITH DEFAULT
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0021 - WITH DEFAULT FOR 'CHARACTER(50)' NOT SUPPORTED IN SNOWFLAKE ***/!!!
                                                                                                             ,
    --clob_col CLOB,
    char_varying_col CHAR VARYING(100)
--                                       WITH DEFAULT
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0021 - WITH DEFAULT FOR 'CHAR VARYING(100)' NOT SUPPORTED IN SNOWFLAKE ***/!!!
                                                                                                                 ,
    --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) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DAY(4) DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0DAY',
    interval_day_to_hour_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DAY(4) TO HOUR DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0DAY',
    interval_hour_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL HOUR(2) DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0HOUR',
    interval_minute_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL MINUTE(2) DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0MINUTE',
    interval_month_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL MONTH DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0MONTH',
    interval_second_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL SECOND(2) DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0SECOND',
    interval_year_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL YEAR(4) DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0YEAR',
    -- Binary Types
    -- blob_col BLOB(1000),
    byte_col BINARY
--                    WITH DEFAULT
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0021 - WITH DEFAULT FOR 'BYTE(1000)' NOT SUPPORTED IN SNOWFLAKE ***/!!!
                                                                                                          ,
    varbyte_col BINARY(1000)
--                             WITH DEFAULT
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0021 - WITH DEFAULT FOR 'VARBYTE(1000)' NOT SUPPORTED IN SNOWFLAKE ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

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.

  1. SSC-EWI-0021: Not Supported in Snowflake.

  2. SSC-EWI-0036: Data type converted to another data type.

Last updated