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.

DDL - Part 2 - Column Level attributes

COLUMN LEVEL ATTRIBUTES:

Most of the column level attributes are Teradata extensions.

ANSI has very few column level attributes.

ANSI:

NOT NULL	--> Disallows NULL in the column.
DEFAULT user	--> Uses user id as default value.
DEFAULT value	--> Uses default value is input value is not provided.
DEFAULT NULL	--> Uses NULL as the default value.

Teradata:

UPPERCASE	--> Stores the data entered in upper case.
CASESPECIFIC	--> Treats data as case specific for comparisons and sorting. By default in teradata 'A' and 'a' would mean the same and hence they will sort in any sequence. However when we make it case specific the sorting differs.
FORMAT	--> Used to control display format of a field.
TITLE	--> Used to provide default titles.
NAMED/ AS	--> Uses Default column name.
COMPRESS	--> Used to compress NULL's to take no physical space.
COMPRESS NULL	--> Used to compress NULL's to take no physical space.
COMPRESS value	--> Used to compress a particular value and NULL's to take no physical space
WITH DEFAULT	--> Uses System default values.
DEFAULT DATE	--> Uses today's date as default date.
DEFAULT TIME	--> Uses Current Time as default time.

Note that DEFAULT DATE ,DEFAULT TIME and WITH DEFAULT are all not ANSI.

Example:

CREATE SET TABLE EDW_RESTORE_TABLES.TEST3 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     DATABLOCKSIZE = 21504 BYTES, FREESPACE = 30 PERCENT, CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      COLUMN1 CHAR(1) UPPERCASE)

       PRIMARY INDEX ( COLUMN1 );

insert into EDW_RESTORE_TABLES.TEST3 values ('c')

select * from EDW_RESTORE_TABLES.TEST3

Gives 'C'   Capital C