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: Data is converted if the inserted value does not comply with the current column type. The involved scenarios are INSERT INTO table VALUES (…) and INSERT INTO table SELECT … Currently, the involved types are TINYINT[UNSIGNED],SMALLINT[UNSIGNED],INT[UNSIGNED],BIGINT[UNSIGNED],FLOAT,DOUBLE,NUMERIC,CLOB,CHAR, and VARCHAR.

2. sql_mode_strict: If the value length of an inserted column exceeds the limit, the maximum or minimum value of the column is used. The involved types are TINYINT[UNSIGNED],SMALLINT[UNSIGNED],INT[UNSIGNED],BIGINT[UNSIGNED],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.

6. ansi_quotes: It is mainly used in places where double quotation marks need to be used to indicate string values. When ansi_quotes is enabled, the content in the double quotation marks is considered as an object reference. When ansi_quotes is disabled, the content in the double quotation marks is considered as a string value. When ansi_quotes is disabled, some meta-commands become invalid. The following table lists the invalid meta-commands.

   Parameter	Description
   \d[S+]	Lists all tables, views, and sequences of all schemas in search_path. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed.
   \da[S] [PATTERN]	Lists all available aggregate functions, together with their return value types and the data types.
   \db[+] [PATTERN]	Lists all available tablespaces.
   \dc[S+] [PATTERN]	Lists all available conversions between character sets.
   \dC[+] [PATTERN]	Lists all available type conversions.
   \dd[S] [PATTERN]	Lists descriptions about objects matching PATTERN.
   \ddp [PATTERN]	Lists all default permissions.
   \dD[S+] [PATTERN]	Lists all available domains.
   \ded[+] [PATTERN]	Lists all data source objects.
   \det[+] [PATTERN]	Lists all foreign tables.
   \des[+] [PATTERN]	Lists all foreign servers.
   \deu[+] [PATTERN]	Lists all user mappings.
   \dew[+] [PATTERN]	Lists foreign-data wrappers.
   \df[antw][S+] [PATTERN]	Lists all available functions, together with their parameters and return types. a indicates an aggregate function, n indicates a common function, t indicates a trigger, and w indicates a window function.
   \dF[+] [PATTERN]	Lists all text search configurations.
   \dFd[+] [PATTERN]	Lists all text search dictionaries.
   \dFp[+] [PATTERN]	Lists all text search parsers.
   \dFt[+] [PATTERN]	Lists all text search templates.
   \dl	This is an alias for \lo_list, which shows a list of large objects.
   \dL[S+] [PATTERN]	Lists all available program languages.
   \dm[S+] [PATTERN]	Lists materialized views.
   \dn[S+] [PATTERN]	Lists all schemas (namespaces).
   \do[S] [PATTERN]	Lists all available operators with their operand and return types.
   \dO[S+] [PATTERN]	Lists collations.
   \dp [PATTERN]	Lists tables, views, and related permissions.
   \drds [PATTERN1 [PATTERN2]]	Lists all modified configuration parameters. These settings can be for roles, for databases, or for both. PATTERN1 and PATTERN2 indicate a role pattern and a database pattern, respectively.
   \dT[S+] [PATTERN]	Lists all data types.
   \dE[S+] [PATTERN]	In this group of commands, the letters E, i, s, t, and v stand for a foreign table, index, sequence, table, or view, respectively. You can specify any or a combination of these letters sequenced in any order to obtain an object list. For example, \dit lists all indexes and tables. If a command is suffixed with a plus sign (+), physical dimensions and related descriptions of each object will be displayed.
   \dx[+] [PATTERN]	Lists installed extensions.
   \l[+]	Lists the names, owners, character set encodings, and permissions of all the databases in the server.
   \z [PATTERN]	Lists all tables, views, and sequences in the database and their access permissions.

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

Value range: a string

Default value: 'sql_mode_strict,sql_mode_full_group,ansi_quotes'

Example:

--Create a table named test1.
openGauss=# 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.
openGauss=# insert into test1(a1,a2) values(123412342342314,3453453453434324);
--Failed to query the table.
openGauss=# select a1,a2 from test1 group by a1;

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

--Deleting a Table
openGauss=# 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 Table 1.

Value range: [1.0, 2147483647.0]

Default value: 0

Example

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

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

openGauss=# set b_db_timestamp = 1.0;
SET
openGauss=# 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 Table 1.

Value range: [0, 7]

Default value: 0

Example

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

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

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

openGauss=# 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 Table 1.

Value range: The options of lc_time_names are as follows:

Default value: 'en_US'

Example

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

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

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

dolphin.default_database_name

Parameter description: Specifies the default database name used by dolphin protocol plugin.

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

Value range: a string

Default value: database_name in connection session while initialize dolphin protocol pulin at first time.

b_compatibility_mode

Parameter description: The parameter value is of the Boolean type. This parameter affects some conflicting functions and operators in the Dolphin plug-in. When this parameter is enabled, the compatibility logic is executed for these functions and operators. When this parameter is disabled, the original openGauss logic is retained.

Currently, the following operators are affected:

1. LIKE/NOT LIKE
2. Character type XOR ^
3. Numeric type XOR ^
4. &&
5. #

The following functions are affected:

1. LAST_DAY
2. TIMESTAMPDIFF
3. FORMAT
4. EXTRACT

Other affected parameters:

1. ?

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

Value range: Boolean

• on indicates that the new compatibility function is used.
• off indicates that the compatibility function is disabled and the original kernel functions are used.

Default value: off