Teradata DEFAULT VALUES.

DEFAULT VALUES:

We can insert DEFAULT values to table using the 'DEFAULT VALUES' clause.

Following is how we will write the query:

INSERT INTO TABLENAME DEFAULT VALUES;

However for this to work either of the following must be true for each of the column in the table.

1. Column must have Default values specified. Default values can be specified using DEFAULT keyword,
   
   Ex: COLUMN1 STRING(30) DEFAULT USER,

      COLUMN2 INTEGER DEFAULT Value,

      COLUMN3 START_DATE DEFAULT DATE '2013-12-11'

When we use the insert query with DEFAULT VALUES(as shown in the insert query above), these default values that we specify get inserted.

2. The column must have default values specified by system defaults. System defaults are specified by using 'WITH DEFAULT' for the column

Ex: COLUMN1 STRING(30) WITH DEFAULT
 

System default values are

• Spaces for char string,
• zero for numeric data types and
• current date for date data types.

When we use the insert query with DEFAULT VALUES(as shown in the insert query above), these system default values get inserted.

3. The column must allow  NULLs. .i.e.   Columns should not have a NOT NULL clause.

If we don’t have user defined default values or system level default values specified , NULLs will be inserted

If NONE of the above 3 is true then the INSERT WITH DEFAULT VALUES will fail.

INSERT WITH DEFAULT VALUES will
 

• INSERTs defined values into each column.
   
• INSERTs NULL if no default values is specified.
   

• Will Fail if Default values are not specified and Nulls are not allowed.

What is another ways to insert default values?

Using positional comma's in the insert statement.

Example:

INSERT INTO TABLENAME VALUES (,,,,,,,);

Use of positional comma's indicate use of default values if specified using DEFAULT or WITH DEFAULT.

IF none is specified then it tries to insert NULL's.

IF column is defined as NOT NULL then it will fail as it wont be able to insert either default values or nulls.