Inserting rows with an MR_COUNTER column
While data is inserted in a multi-region table with an MR_COUNTER column, the system
generates a default value of 0 for the MR_COUNTER column value in the
following two cases.
- When the keyword
DEFAUL
is used in the insert_clause for the MR_COUNTER column. - When the MR_COUNTER column is skipped in the INSERT clause.
Example 1: Specifying DEFAULT clause while inserting data into an MR_COUNTER column
CREATE Table myTable( name STRING,
count INTEGER AS MR_COUNTER,
PRIMARY KEY(name)) IN REGIONS DEN,LON;
INSERT INTO myTable VALUES ("Bob", DEFAULT);
SELECT * FROM myTable;
{"name":"Bob","count":0}
Example 2: Skip the MR_COUNTER column while inserting data into a multi-region table
CREATE Table myTable( name STRING,
count INTEGER AS MR_COUNTER,
PRIMARY KEY(name)) IN REGIONS DEN,LON;
INSERT INTO myTable(name) VALUES ("Chris');
SELECT * FROM myTable;
{"name":"Chris","count":0}
Example 3: Error when MR_COUNTER column is skipped and no DEFAULT clause is given
If no DEFAULT clause is specified for the MR_COUNTER column and if the
column is not skipped from the INSERT clause, an error is thrown as
shown
below.
CREATE Table myTable( name STRING,
count INTEGER AS MR_COUNTER,
PRIMARY KEY(name)) IN REGIONS DEN,LON;
INSERT INTO myTable VALUES ("Chris")
Error handling command execute 'INSERT INTO myTable VALUES ("Chris")':
Error: at (1, 0) The number of VALUES expressions is not equal to the number of table columns
You cannot insert values into an MR_COUNTER column
explicitly. A DEFAULT construct must always be used or the
MR_COUNTER column should be skipped in the INSERT clause. If you try
to insert values into the MR_COUNTER column using the INSERT clause
or using API, an error is thrown as shown
below.
CREATE Table myTable ( name STRING,
count INTEGER AS MR_COUNTER,
PRIMARY KEY(name)) IN REGIONS DEN;
INSERT INTO myTable VALUES("Tom",0)';
Error handling command execute 'INSERT INTO myTable VALUES("Tom",0)': Error: at (1, 38) MRCounter column must use DEFAULT construct.
Inserting rows into a JSON column having MR_COUNTER data type:
While inserting data into the table, the system always initially inserts the default value of 0 for all MR_COUNTER data types.
Case 1: Skip the value for MR_COUNTER data type: When you skip providing
values for an MR_COUNTER data type, the value of 0 is automatically
assigned to
it.
INSERT INTO demoJSONMR VALUES ("Anna",
{
"id" : 1,
"person" : {
"age" : 10,
"number" : 100
}
}
);
SELECT * FROM demoJSONMR;
{"name":"Anna","jsonWithCounter":{"id" : 1,"counter":0,
"person":{"age":10,"count":0,"number":100}
}
}
Case 2: Provide a value for the MR_COUNTER data type: When
inserting a row into the multi-region table with a JSON MR_COUNTER,
a value of 0 is initially assigned to all MR_COUNTER data types even
if you explicitly assign a non-zero value. This also holds good when
you try to provide a value that is not an INTEGER or LONG or
NUMBER.
INSERT INTO demoJSONMR VALUES ("Anna",
{
"id" : 1,
"counter" : 5,
"person" : {
"age" : 10,
"count" : NULL,
"number" : 100
}
}
);
SELECT * FROM demoJSONMR;
{"name":"Anna","jsonWithCounter":{"id" : 1,"counter":0,
"person":{"age":10,"count":0,"number":100}
}
}