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 zero for the MR_COUNTER column value in the following two cases.

• When the keyword DEFAULT is used in the INSERT clause for the MR_COUNTER column.
• When the MR_COUNTER column is skipped in the INSERT clause.

Specifying DEFAULT clause while inserting data into an MR_COUNTER column

Example 7-15 Insert a row to a multi-region table using the DEFAULT clause

Create a multi-region table in DEN and LON regions with an MR_COUNTER:

CREATE Table myTable( name STRING,
                      count INTEGER AS MR_COUNTER,
                      PRIMARY KEY(name)) IN REGIONS DEN,LON

Insert a row to the table by specifying the DEFAULT clause to the counter:

INSERT INTO myTable VALUES ("Bob", DEFAULT)

Fetch the counter value:

SELECT * FROM myTable

Output:

{"name":"Bob","count":0}

Skipping the MR_COUNTER column while inserting data into a multi-region table

Example 7-16 Insert a row to a multi-region table without specifying a value to the MR_COUNTER

Create a multi-region table in DEN and LON regions with an MR_COUNTER:

CREATE Table myTable( name STRING,
                      count INTEGER AS MR_COUNTER,
                      PRIMARY KEY(name)) IN REGIONS DEN,LON

Insert a row to the table by specifying the column names, without specifying the counter or supplying any value to it:

INSERT INTO myTable(name) VALUES ("Chris')

Fetch the table data. Notice that the system automatically inserts a zero value to the counter.

SELECT * FROM myTable

Output:

 {"name":"Chris","count":0}

Error when MR_COUNTER column is not skipped and no DEFAULT clause is given

While inserting all column values to a row, you must specify the DEFAULT clause for the MR_COUNTER column. Else, the system displays an error.

Example 7-17 Insert all columns in a row without specifying a value to the MR_COUNTER

Create a multi-region table in DEN and LON regions with an MR_COUNTER:

CREATE Table myTable( name STRING,
                      count INTEGER AS MR_COUNTER,
                      PRIMARY KEY(name)) IN REGIONS DEN,LON

Insert all the columns in a row. Notice that you don't supply a DEFAULT value to the counter.

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.

Example 7-18 Insert a row to a multi-region table by specifying a value other than DEFAULT to the MR_COUNTER

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:

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

Note:

• The system initially assigns a value of zero 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.

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

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