What is the difference between GENERATED BY DEFAULT and GENERATED ALWAYS ?

Database
Enthusiast

What is the difference between GENERATED BY DEFAULT and GENERATED ALWAYS ?

 

If I'm creating a table 

 

CREATE SET TABLE my_database1.my_table ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      p_id BIGINT GENERATED ALWAYS AS IDENTITY
           (START WITH 1             INCREMENT BY 1             MINVALUE 1 
MAXVALUE 922337203685477580 NO CYCLE), created_by VARCHAR(20) , salary INTEGER, bonus INTEGER, created_ts TIMESTAMP(6)) UNIQUE PRIMARY INDEX ( p_id );

 

What if I used 

GENERATED BY DEFAULT AS IDENTITY

 
What would be the difference? One thing I know I would be able to insert custom values in that field. Are there any other differences?
 

1 REPLY
Teradata Employee

Re: What is the difference between GENERATED BY DEFAULT and GENERATED ALWAYS ?

[as described in the documentation] GENERATED BY DEFAULT will generate a value if a null is provided when inserting a row and will insert the value provided if not null. 

 

Some caution is indicated since generated by default does not make any attempt to analyse existing values when generating a new one. If a combination of custom values and generated values are used, then it is the user/application's responsibility to avoid collisions. 

 

Generated by default is required if copying existing values from somewhere else, such as from a previous copy of the table. But after the copy, it would probably be good to set the minvalue to the max of the values in the identity column and then only uswe generated values from then forward. Or set a maxvalue and insert custom values greater than the maxvalue , letting the system generate values between minvalue and maxvalue.