Following are examples of update (upsert form) operations on a PTI table, with storage distribution based on:
- Time bucket
- Time bucket and columns clause
- Columns clause
Examples: Upsert with Storage Distribution Based on Time Bucket
Here is the definition for the table used in this example. The nonsequenced PTI table ocean_buoy_no_seq includes:
- Time zero specified as October 15, 2016, DATE '2016-10-15'
- One hour time bucket, HOURS(1)
CREATE TABLE ocean_buoy_no_seq ( TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN, TD_TIMECODE TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN, salinity INTEGER, temperature INTEGER) PRIMARY TIME INDEX (TIMESTAMP(1), DATE '2016-10-15', HOURS(1), NONSEQUENCED);
These insert operations populate the table with rows of data:
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 10:33:12.1', 23, 33);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 11:44:12.1', 24, 34);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 12:55:12.1', 25, 35);
This statement updates the row where the TD_TIMECODE value matches TIMESTAMP '2016-10-16 10:33:12.1' to set the salinity to 28:
UPDATE ocean_buoy_no_seq SET salinity = 28 WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.1' ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.1', 28, 33);
This statement inserts a new row after not finding a match in the TD_TIMECODE column for the value, TIMESTAMP '2016-10-16 10:33:12.6':
UPDATE ocean_buoy_no_seq SET salinity = 28 WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.6' ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.6', 28, 33);
Examples: Upsert with Storage Distribution Based on Time Bucket and Columns Clause
Here is the definition for the table used in this example. The nonsequenced PTI table ocean_buoy_no_seq includes:
- Time zero specified as October 15, 2016, DATE '2016-10-15'
- One hour time bucket, HOURS(1)
- The buoy_id column in the PRIMARY TIME INDEX, COLUMNS(buoy_id)
CREATE TABLE ocean_buoy_no_seq ( TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN, TD_TIMECODE TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN, BUOY_ID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER) PRIMARY TIME INDEX (TIMESTAMP(1), DATE '2016-10-15', HOURS(1), COLUMNS(buoy_id), NONSEQUENCED);
These insert operations populate the table with rows of data:
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 10:33:12.1', 333, 23, 33);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 11:44:12.1', 444, 24, 34);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 12:55:12.1', 555, 25, 35);
This statement updates the row where the TD_TIMECODE value matches TIMESTAMP '2016-10-16 10:33:12.1' for buoy_id 333 to set the salinity to 28:
UPDATE ocean_buoy_no_seq SET salinity = 28 WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.1' AND buoy_id = 333 ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.1', 333, 28, 33);
This statement inserts a new row after not finding a match in the TD_TIMECODE column for the value, TIMESTAMP '2016-10-16 10:33:12.6' for buoy_id 666:
UPDATE ocean_buoy_no_seq SET salinity = 28 WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.6' AND buoy_id = 666 ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.6', 666, 28, 33);
Here is the definition for the table used in this example. The sequenced PTI table ocean_buoy_seq includes:
- Time zero specified as October 15, 2016, DATE '2016-10-15'
- One hour time bucket, HOURS(1)
- The buoy_id column in the PRIMARY TIME INDEX, COLUMNS(buoy_id)
CREATE TABLE ocean_buoy_seq ( TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN, TD_TIMECODE TIMESTAMP(6) NOT NULL GENERATED TIMECOLUMN, TD_SEQNO INTEGER NOT NULL GENERATED TIMECOLUMN, buoy_id INTEGER, salinity INTEGER, temperature INTEGER) PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2016-10-15', HOURS(1), COLUMNS(buoy_id), SEQUENCED(200));
These insert operations populate the table with rows of data:
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 10:33:12.1', 1, 333, 23, 33);
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 11:44:12.1', 1, 444, 24, 34);
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 12:55:12.1', 1, 555, 25, 35);
This statement updates the row where the TD_TIMECODE value matches TIMESTAMP '2016-10-16 10:33:12.1' and TD_SEQNO =1 for buoy_id 333 to set the salinity to 28:
UPDATE ocean_buoy_seq SET salinity = 28 WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.1' AND TD_SEQNO = 1 AND buoy_id = 333 ELSE INSERT INTO ocean_buoy_seq (TIMESTAMP '2016-10-16 10:33:12.1', 1, 333, 28, 33);
This statement inserts a new row after not finding a match in the TD_TIMECODE column for the value, TIMESTAMP '2016-10-16 10:33:12.6' with TD_SEQNO = 1 for buoy_id 666:
UPDATE ocean_buoy_seq SET salinity = 28 WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.6' AND TD_SEQNO = 1 AND buoy_id = 666 ELSE INSERT INTO ocean_buoy_seq (TIMESTAMP '2016-10-16 10:33:12.6', 1, 666, 28, 33);
Examples: Upsert with Storage Distribution Based on Columns Clause
Here is the definition for the table used in this example. The nonsequenced PTI table ocean_buoy_no_seq includes:
- Time zero specified as October 15, 2016, DATE '2016-10-15'
- The ocean_zone and buoy_id columns in the PRIMARY TIME INDEX, COLUMNS(ocean_zone, buoy_id)
CREATE TABLE ocean_buoy_no_seq ( TD_TIMECODE TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN, ocean_zone CHAR(2), buoy_id INTEGER, salinity INTEGER, temperature INTEGER) PRIMARY TIME INDEX (TIMESTAMP(1), DATE '2016-10-15', COLUMNS(ocean_zone, buoy_id), NONSEQUENCED);
These insert operations populate the table with rows of data:
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 10:33:12.1', 'PC', 333, 23,33);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 11:44:12.1', 'AT', 444, 24,34);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 12:55:12.1', 'IN', 555, 25,35);
This statement updates the row where the TD_TIMECODE value matches TIMESTAMP '2016-10-16 10:33:12.1' and ocean_zone = 'PC' for buoy_id 333 to set the salinity to 28:
UPDATE ocean_buoy_no_seq SET salinity = 28 WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.1' AND ocean_zone = 'PC' AND buoy_id = 333 ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.1', 'PC', 333, 23, 33);
This statement inserts a new row after not finding a match in the TD_TIMECODE column for the value, TIMESTAMP '2016-10-16 10:33:12.6' and ocean_zone = 'PC' for buoy_id 666:
UPDATE ocean_buoy_no_seq SET salinity = 28 WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.6' AND ocean_zone = 'PC' AND buoy_id = 666 ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.6', 'PC', 666, 28, 33);
Here is the definition for the table used in this example. The sequenced PTI table ocean_buoy_seq includes:
- Time zero specified as October 15, 2016, DATE '2016-10-15'
- The ocean_zone and buoy_id columns in the PRIMARY TIME INDEX, COLUMNS(ocean_zone, buoy_id)
CREATE TABLE ocean_buoy_seq ( TD_TIMECODE TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN, TD_SEQNO INTEGER NOT NULL GENERATED TIMECOLUMN, ocean_zone CHAR(2), buoy_id INTEGER, salinity INTEGER, temperature INTEGER) PRIMARY TIME INDEX (TIMESTAMP(1), DATE '2016-10-15', COLUMNS(ocean_zone, buoy_id), SEQUENCED(20));
These insert operations populate the table with rows of data:
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 10:33:12.1', 1, 'PC', 333, 23,33);
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 11:44:12.1', 1, 'AT', 444, 24,34);
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 12:55:12.1', 1, 'IN', 555, 25,35);
This statement updates the row where the TD_TIMECODE value matches TIMESTAMP '2016-10-16 10:33:12.1', TD_SEQNO = 1, and ocean_zone = 'PC' for buoy_id 333 to set the salinity to 28:
UPDATE ocean_buoy_seq SET salinity = 28 WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.1' AND TD_SEQNO = 1 AND ocean_zone = 'PC' AND buoy_id = 333 ELSE INSERT INTO ocean_buoy_seq (TIMESTAMP '2016-10-16 10:33:12.1', 1, 'PC', 333, 23, 33);
This statement inserts a new row after not finding a match in the TD_TIMECODE column for the value, TIMESTAMP '2016-10-16 10:33:12.6', TD_SEQNO = 1, and ocean_zone = 'PC' for buoy_id 666:
UPDATE ocean_buoy_seq SET salinity = 28 WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.6' AND TD_SEQNO = 1 AND ocean_zone = 'PC' AND buoy_id = 666 ELSE INSERT INTO ocean_buoy_seq (TIMESTAMP '2016-10-16 10:33:12.6', 1, 'PC', 666, 28, 33);