ALTER TABLE Statement (ANSI Bitemporal Table Form) | Teradata Vantage - 17.00 - ALTER TABLE (ANSI Bitemporal Table Form) - Teradata Database

Teradata Vantageā„¢ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

Purpose

Temporal syntax for ALTER TABLE allows you to create ANSI bitemporal tables from existing nontemporal tables, and combine the ALTER TABLE syntaxes for system-time and valid-time tables.

Adding valid time to the table involves these ALTER TABLE operations:
  • Adding or altering the two DateTime columns that will serve as the beginning and ending bounds of the valid-time period
  • Adding a valid-time derived period column to the table, and designating the derived column VALIDTIME
Adding system time to the table involves these ALTER TABLE operations:
  • Adding a SYSTEM_TIME derived period column to the table by specifying the columns that will serve as the beginning and ending bounds of the system-time period.
  • Adding or altering the columns that will serve as the beginning bound and ending bound of the system-time period. If these columns already exist in the table, special attributes must be added to them.
  • Adding system versioning to the table

Syntax

There is no special syntax for altering tables to bitemporal tables. Use the combined special syntax that is discussed in ALTER TABLE (ANSI System-Time Table Form) and ALTER TABLE (ANSI Valid-Time Table Form).

The table must be altered to add a valid-time dimension prior to adding the system-time dimension, because ALTER TABLE operations on system-time tables are severely restricted.

Example: ALTER TABLE to Convert a Nontemporal Table to an ANSI Bitemporal Table

The following SQL creates a regular nontemporal table, and inserts some rows:

CREATE MULTISET TABLE employee_bitemp (
 eid INTEGER NOT NULL,
 ename VARCHAR(5),
 deptno INTEGER NOT NULL,
 terms VARCHAR(5),
 job_start DATE NOT NULL,
 job_end DATE NOT NULL,
 sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL,
 sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL
 )
 PRIMARY INDEX (eid);

INSERT INTO employee_bitemp VALUES 

 (1001,'Sania',111,'TW08',DATE'2002-01-01',DATE'2006-12-31',
  TIMESTAMP'2002-01-01 00:00:00.000000-08:00',
  TIMESTAMP'2002-07-01 12:00:00.350000+00:00');
INSERT INTO employee_bitemp VALUES 
 (1004,'Fred',222,'PW12', DATE'2001-05-01',DATE'9999-12-31',
  TIMESTAMP'2001-05-01 12:00:00.350000-08:00',
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_bitemp VALUES
 (1002,'Ash',333,'TA05',DATE'2003-01-01',DATE'2003-12-31',
  TIMESTAMP'2003-01-01 12:11:00.000000-08:00', 
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_bitemp VALUES 
 (1003,'SRK',111,'TM02',DATE'2004-02-10',DATE'2005-02-10', 
  TIMESTAMP'2004-02-10 00:00:00.000000-08:00', 
  TIMESTAMP'2004-12-01 00:12:23.120000+00:00');
INSERT INTO employee_bitemp VALUES 
 (1005,'Alice',222,'TW10',DATE'2004-12-01',DATE'9999-12-31',
  TIMESTAMP'2004-12-01 12:00:00.450000-08:00', 
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_bitemp VALUES 
 (1010,'Mike',444,'TW07',DATE'2015-01-01',DATE'2016-12-31',
  TIMESTAMP'2004-12-01 00:12:23.120000-08:00', 
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

After rows have been inserted, an unqualified SELECT on the table returns all rows:

SELECT * FROM employee_bitemp;

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-01-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00
1001 Sania    111  TW08  2002/01/01  2006/12/31  2002-01-01 00:00:00.000000-08:00  2002-07-01 12:00:00.350000+00:00
1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00
1003 SRK      111  TM02  2004/02/10  2005/02/10  2004-02-10 00:00:00.000000-08:00  2004-12-01 00:12:23.120000+00:00

The following ALTER TABLE statement adds valid time to the table:

ALTER TABLE employee_bitemp
 ADD PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME;

A simple select from the table still returns all rows, as it would from any valid-time table:

SELECT * FROM employee_bitemp;

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-01-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00
1001 Sania    111  TW08  2002/01/01  2006/12/31  2002-01-01 00:00:00.000000-08:00  2002-07-01 12:00:00.350000+00:00
1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00
1003 SRK      111  TM02  2004/02/10  2005/02/10  2004-02-10 00:00:00.000000-08:00  2004-12-01 00:12:23.120000+00:00

The following ALTER TABLE statements add system time to the table:

ALTER TABLE employee_bitemp
 ADD PERIOD FOR SYSTEM_TIME(sys_start,sys_end)
 ADD sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL
               GENERATED ALWAYS AS ROW START
 ADD sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL
               GENERATED ALWAYS AS ROW END;
ALTER TABLE employee_bitemp
 ADD SYSTEM VERSIONING;

Because the table now includes a system-time dimension, rows that had dates for sys_end that were not 9999-12-31 23:59:59.999999+0:0 are considered closed rows, logically deleted from the database, so now a simple select shows only four rows:

SELECT * FROM employee_bitemp;

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-01-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00