GUC Parameters

sql_mode

Parameter description: The parameter value is a character string separated by commas (,). Only valid character strings are allowed. If the parameter value is invalid, a warning is reported after the startup. Similarly, if the new value is invalid, a warning is reported and the old value is not changed. The default string of the current sql_mode is sql_mode_strict,sql_mode_full_group. Currently, sql_mode is used in the following scenarios:

1. sql_mode_strict: When a value that does not comply with the current column type is inserted, data conversion is performed. There are two scenarios: insert into table values (…) and insert into table select …. Conversion between various data types is involved. Currently, the following types are involved: tinyint (tinyint is not considered because its data scope is different from that of MySQL), smallint, int, bigint, float, double, numeric, clob, char, and varchar.
2. sql_mode_strict: If the length of the inserted column value exceeds the length limit of the column, the maximum or minimum value is assigned to the column. The involved types are tinyint, smallint, int, bigint, float, double, numeric, clob, char, and varchar.
3. sql_mode_strict: During insert, if a column whose attribute is not empty and does not have a default value is not in the insert list, the default value is added to the column. (The involved types are the same as the preceding types.)
4. sql_mode_strict: supports explicit insertion of default to columns whose attributes are not empty and do not have default values. (The involved types are the same as the preceding types.)
5. sql_mode_full_group: determines whether columns (without aggregate functions) in the SELECT list must be included in the GROUP BY clause. In sql_mode_full_group mode (default mode), if a column in the select list does not use an aggregate function or appear in the GROUP BY clause, an error is reported. Otherwise, the execution is successful and the first tuple is selected from all tuples that meet the conditions.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Appendices.

Value range: a string

Default value: 'sql_mode_strict,sql_mode_full_group'

Example:

--Create a table named test1.
MogDB=# CREATE TABLE test1
(
  a1 smallint not null,
  a2 int not null,
  a3 bigint not null,
  a4 float not null,
  a5 double not null,
  a6 numeric not null,
  a7 varchar(5) not null
);

--Failed to insert records into the table.
MogDB=# insert into test1(a1,a2) values(123412342342314,3453453453434324);
--Failed to query the table.
MogDB=# select a1,a2 from test1 group by a1;

--A record is successfully inserted into the table.
MogDB=# set sql_mode = '';
MogDB=# insert into test1(a1,a2) values(123412342342314,3453453453434324);
--A table is queried successfully.
MogDB=# select a1,a2 from test1 group by a1;

--Deleting a Table
MogDB=# DROP TABLE test1;

b_db_timestamp

Parameter description: The parameter value is a floating point number. This parameter affects the curdate, current_time, curtime, current_timestamp, localtime, localtimestamp, and now functions in Dolphin. If this parameter is set to 0, the preceding functions return the current date or time. If the parameter value is within the range [1,2147483647], the preceding functions use the value of this parameter as the second offset and return the date or time corresponding to 1970-01-01 00:00:00 UTC + Second offset + Current time zone offset. If the value of this parameter is not in the preceding valid range, an error is reported.

This parameter is a USERSET parameter. Set it based on instructions provided in Appendices.

Value range: [1.0, 2147483647.0]

Default value: 0

Example

MogDB=# show b_db_timestamp;
 b_db_timestamp
----------------
 0
(1 row)

MogDB=# select now();
        now()
---------------------
 2022-09-18 19:52:23
(1 row)

MogDB=# set b_db_timestamp = 1.0;
SET
MogDB=# select now();
        now()
---------------------
 1970-01-01 08:00:01
(1 row)

default_week_format

Parameter description: The parameter value is an integer. This parameter affects the week function in the Dolphin plug-in. The value range of this parameter is [0,7], which corresponds to eight calculation policies. For details about these policies, see Time and Date Functions. If the value of this GUC parameter exceeds the corresponding boundary value, a warning is reported and the GUC parameter is set to the corresponding boundary value.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Appendices.

Value range: [0, 7]

Default value: 0

Example

MogDB=# show default_week_format;
default_week_format
---------------------
0
(1 row)

MogDB=# select week('2000-1-1');
week
------
    0
(1 row)

MogDB=# alter system set default_week_format = 2;
ALTER SYSTEM SET

MogDB=# select week('2000-1-1');
week
------
52
(1 row)

lc_time_names

Parameter description: Specifies the language in which the dayname and monthname functions of the dolphin plug-in output results. The parameter value is a character string. There are 111 values for this parameter. If the value of a parameter is not within the valid value range, an error is reported.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Appendices.

Value range: The options of lc_time_names are as follows:

Default value: 'en_US'

Example

MogDB=# select dayname('2000-1-1');
dayname
----------
Saturday
(1 row)

MogDB=# alter system set lc_time_names = 'zh_CN';
ALTER SYSTEM SET

MogDB=# select dayname('2000-1-1');
dayname
---------
Saturday
(1 row)