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}
                }
}