17.05 - Example: Simple INSERT Requests Using a DEFAULT Function - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The following example set uses this table definition:

     CREATE TABLE table7 (
       col1 INTEGER,
       col2 INTEGER DEFAULT 10,
       col3 INTEGER DEFAULT 20,
       col4 CHARACTER(60) );

The following INSERT request is valid:

     INSERT INTO table7
       VALUES (1, 2, DEFAULT, 'aaa');

The DEFAULT function evaluates to the default value of col3, the third column position in the insert list. The example INSERT request inserts the value 20 into table7 for col3.

The resulting row is as follows:

      col1        col2        col3 col4
---------- ----------- ----------- -----------
         1           2          20 aaa

The following INSERT request is valid:

     INSERT INTO table7 (col1, col3, col4)
     VALUES (1, DEFAULT, 'bbb');

The DEFAULT function evaluates to the default value of col3 because DEFAULT is specified second in the expression list, and the second column in the column list is col3.

The resulting row is as follows:

      col1        col2        col3 col4
---------- ----------- ----------- ----------
1          10          20 bbb

You can specify a DEFAULT function with a column name in an expression list. This is a Teradata extension to the ANSI SQL:2011 standard.

The following INSERT request is valid:

     INSERT INTO table7
     VALUES (1, 2, DEFAULT(col2), 'aaa');

The DEFAULT function evaluates to the default value of col2 because col2 is passed as an input argument to the DEFAULT function. The INSERT results in the value 10 for col3 in the row because col3 is the third column position in the insert list.

The resulting row is as follows:

       col1        col2        col3 col4
----------- ----------- ----------- ------------
          1           2          10 aaa

The following INSERT request is valid:

     INSERT INTO table7 (col1, col3, col4)
     VALUES (1, DEFAULT(col2), 'bbb');

Because col2 is passed as an input argument to the DEFAULT function, the function evaluates to the default value of col2. Because the second column in the column list is col3, the system assigns it the value 10, which is the default value of col2).

The resulting row is as follows:

            col1        col2        col3 col4
     ----------- ----------- ----------- ------
               1          10          10 bbb

You can specify the DEFAULT function with a column name anywhere in the expression. This is a Teradata extension to the ANSI SQL:2011 standard.

The following INSERT request is valid:

     INSERT INTO table7
     VALUES (1, 2, DEFAULT (col2)+5, 'aaa');

The DEFAULT function evaluates to the default value of col2 plus 5 (or 10+5). Because col3 is in the third column position in the insert list, the resulting row is as follows:

           col1        col2        col3 col4
     ---------- ----------- ----------- -----------------
              1           2          15 aaa

The following INSERT request is valid:

     INSERT INTO table7 (col1, col3, col4)
     VALUES (1, DEFAULT(col2)+5, 'bbb');

The DEFAULT function evaluates to the default value of col2 plus 5 (or 10+5). Because the second column in the column list is col3, the system assigns the value 15 to it. The resulting row is as follows:

     col1        col2        col3           col4
     ---------- ----------- ----------- ---------------
              1          10          15            bbb

When there is no explicit default value associated with a column, the DEFAULT function evaluates to null.

Assume the following table definition for this example:

     CREATE TABLE table9 (
       col1 INTEGER,
       col2 INTEGER NOT NULL,
       col3 INTEGER NOT NULL DEFAULT NULL
       col4 INTEGER CHECK (Col4>100) DEFAULT 99   );

The following INSERT request is valid:

     INSERT INTO table9
       VALUES (DEFAULT, 99, 101, 101);

In this example, col1 is nullable and does not have an explicit default value associated with it. Therefore, the DEFAULT function evaluates to null.

The resulting row is as follows:

            col1         col2         col3         col4
     -----------  -----------  ----------- ------------
               ?           99          101          101

Assume the following table definition for the examples below:

     CREATE TABLE table10
       col1 INTEGER ,
       col2 INTEGER DEFAULT 55,
       col3 INTEGER NOT NULL DEFAULT 99 );

The following examples are correct and use Teradata extensions to the DEFAULT function:

     INSERT INTO table10
     VALUES (1, DEFAULT(col2), DEFAULT(col3));

     INSERT INTO table10 (col1, col2)
     VALUES (1, DEFAULT(col2));

     INSERT INTO table10 (col1, col2, col3)
     VALUES (1, DEFAULT(col2), DEFAULT(col3));

The following examples are correct and use ANSI SQL:2011-compliant syntax for the DEFAULT Function:

     INSERT INTO table10
     VALUES (1, DEFAULT, DEFAULT);

     INSERT INTO table10 (col1, col2)
     VALUES (1, DEFAULT);

     INSERT INTO table10 (col1, col2, col3)
     VALUES (1, DEFAULT, DEFAULT);

The resulting row for all the above insert operations is the following:

      col1         col2         col3
     -----  -----------  -----------
         1           55           99

The following INSERT requests are all equivalent to one another. The first example uses an ANSI-SQL:2011-compliant syntax:

     INSERT INTO table10
     VALUES (5, DEFAULT, 99);

The following example uses Teradata extensions to the ANSI-SQL:2011 syntax:

     INSERT INTO table10
     VALUES (5, DEFAULT(col2), 99);

The resulting row for both of these insert operations is as follows:

       col1         col2         col3
     ------  -----------  -----------
          5           55           99