Inserting rows with an MR

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

Output:

{"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

Output:

 {"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")

Output:

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)'

Output:

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:

Example: Insert data into multi-region table with a JSON MR_COUNTER

When inserting a row into the multi-region table with a JSON MR_COUNTER, you must supply a value 0 to the MR_COUNTER.

Note:

The system initially assigns a value of 0 to all MR_COUNTER data types even if you explicitly supply a non-zero value. This also holds good when you try to provide a value that is not an INTEGER or LONG or NUMBER.
You can't supply the keyword DEFAULT while inserting a JSON MR_COUNTER.
The system will return an error if you try to insert data into an MR table without supplying a value to the declared JSON MR_COUNTER field or using the keyword DEFAULT.

INSERT INTO demoJSONMR VALUES ("Anna",
    {
        "id" : 1,
        "counter" : 0,
        "person" : {
            "age" : 10,
            "count" : 0,
            "number" : 100
        }
    }
)

SELECT * FROM demoJSONMR

Output:

{"name":"Anna","jsonWithCounter":{"id" : 1,"counter":0,
                                 "person":{"age":10,"count":0,"number":100}
                }
}