SET

Description

Modifies a GUC parameter.

Precautions

Most GUC parameters can be modified by executing SET. Some parameters cannot be modified after a server or session starts.

Syntax

Set the system time zone.

SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT };

Set the schema of the table.

SET [ SESSION | LOCAL ] 
    {CURRENT_SCHEMA { TO | = } { schema | DEFAULT }
    | SCHEMA 'schema'};

Set client encoding.

SET [ SESSION | LOCAL ] NAMES {'charset_name' [COLLATE 'collation_name'] | DEFAULT};

Set other GUC parameters.

SET [ LOCAL | SESSION ]
    {config_parameter { { TO | = } { value | DEFAULT } 
                      | FROM CURRENT }};

Set parameters in mode B (sql_compatibility = 'B').

SET [ SESSION | @@SESSION. | @@]
      {config_parameter = { expr | DEFAULT }};

Set user-defined user variables.

SET @var_name := expr [, @var_name := expr] ...
SET @var_name = expr [, @var_name = expr] ...

Parameters

SESSION
Specifies that the specified parameters take effect for the current session. This is the default value if neither SESSION nor LOCAL appears.

If SET or SET SESSION is executed within a transaction that is later aborted, the effects of the SET statement disappear when the transaction is rolled back. Once the surrounding transaction is committed, the effects will persist until the end of the session, unless overridden by another SET.
LOCAL
Specifies that the specified parameters take effect for the current transaction. After COMMIT or ROLLBACK, the session-level setting takes effect again.

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not. A special case is SET followed by SET LOCAL within a single transaction: the SET LOCAL value will be seen until the end of the transaction, but afterward (if the transaction is committed) the SET value will take effect.
TIME ZONE timezone
Specifies the local time zone for the current session.

Value range: a valid local time zone. The corresponding GUC parameter is TimeZone. The default value is PRC.
CURRENT_SCHEMA schema
Specifies the current schema.

Value range: an existing schema name If the schema name does not exist, the value of CURRENT_SCHEMA will be empty.
SCHEMA schema
Specifies the current schema. Here the schema is a string.

Example: set schema 'public';
NAMES {'charset_name' [COLLATE 'collation_name'] | DEFAULT};
The COLLATE clause can be specified when the b_format_version is set to '5.7' and b_format_dev_version set to 's2' in B-compatible mode.
Sets the client character encoding, character set of a constant string, collation, and character set of the returned result.

It is equivalent to:
```
set client_encoding = charset_name; set character_set_connection = charset_name;
set collation_connection = collation_name; set character_set_results = charset_name;
```
Value range: character sets and collations supported in B-compatible mode. Currently, the database character set must be specified for charset_name.
The COLLATE clause cannot be specified in other scenarios.
Specifies the client character encoding.

It is equivalent to:
```
set client_encoding to charset_name
```
Value range: a valid character encoding name. The GUC parameter corresponding to this option is client_encoding. The default encoding is UTF8.
config_parameter
Specifies the name of a configurable GUC parameter. You can use SHOW ALL to view available GUC parameters.

Some parameters that viewed by SHOW ALL cannot be set by SET. For example, max_datanodes.
value
Specifies the new value of config_parameter. This parameter can be specified as string constants, identifiers, numbers, or comma-separated lists of these. DEFAULT can be written to indicate resetting the parameter to its default value.
SESSION | @@SESSION. | @@
The declared parameter takes effect in superuser or user mode, which can be determined by the context column in the pg_settings system view. If neither GLOBAL nor SESSION exists, SESSION is used as the default value. The value of config_parameter can be an expression.
1. SET SESSION is supported only in mode B (sql_compatibility = 'B') and the value of b_format_behavior_compat_options is set to 'enable_set_variables'.
2. When @@config_parameter is used for operator calculation, use spaces to separate them. For example, in the set @@config_parameter1=@@config_parameter1*2; command, =@@ is used as an operator. You can change =@@ to set @config_parameter1= @@config_parameter1 * 2.

var_name
User-defined variable name. A variable name can contain only digits, letters, underscores (_), periods (.), and dollar signs ($). If a variable name is quoted using single or double quotation marks, other characters can be used, such as 'var_name', "var_name", and `var_name`.
- User-defined variables can be set only in mode B (sql_compatibility = 'B') and the value of b_format_behavior_compat_options is set to 'enable_set_variables'.
- User-defined variables store only integers, floating point numbers, strings, bit strings, and NULL. The BOOLEAN, INT1, INT2, INT4, and INT8 types will be converted to the INT8 type, and the FLOAT4, FLOAT8, and NUMBERIC types will be converted to the FLOAT8 type for storage. Note that the precision of the floating-point type may be lost. The BIT type is stored in BIT, the VARBIT type is stored in VARBIT, and the NULL value is stored in NULL. If other types can be converted into character strings, they are converted into TEXT for storage.
- When @var_name is used for operator calculation, use spaces to separate them. For example, in the set @v1=@v2+1; command, =@ is used as an operator. You can change =@ to set @v1= @v2+1.
- When sql_compatibility is set to 'B' and b_format_behavior_compat_options is set to 'enable_set_variables', for the original @ expr of the database (see Numeric Operator), there must be a space between @ and expr. Otherwise, @ will be parsed into a user-defined variable.
- The value of an uninitialized variable is NULL.
- Character strings stored in user-defined variables in the PREPARE statement support only the SELECT, INSERT, UPDATE, DELETE, adn MERGE syntax.
- In consecutive value assignment scenarios, only @var_name1 := @var_name2 :=... := expr and @var_name1 = @var_name2 :=... := expr is supported. An equal sign (=) indicates a value assignment only when it is placed at the beginning, and other positions indicate comparison operators.
expr
Expression, which can be directly or indirectly converted to an integer, floating point, string, bit string, or NULL.

Do not use functions that contain sensitive information (such as passwords) in character string expressions, such as encryption and decryption functions gs_encrypt and gs_decrypt, to prevent sensitive information leakage.

Examples

-- Set the search path of a schema.
gaussdb=# SET search_path TO tpcds, public;

-- Set the date style to the traditional POSTGRES style (date placed before month):
gaussdb=# SET datestyle TO postgres,dmy;

--Set user-defined variables.
gaussdb=# CREATE DATABASE user_var dbcompatibility 'b';
gaussdb=# \c user_var
user_var=# SET b_format_behavior_compat_options = enable_set_variables;
user_var=# SET @v1 := 1, @v2 := 1.1, @v3 := true, @v4 := 'dasda', @v5 := x'41';

-- Query user-defined variables.
user_var=# SELECT @v1, @v2, @v3, @v4, @v5, @v6, @v7;

-- Use user-defined variables.
user_var=# SET @sql = 'select 1';
user_var=# PREPARE stmt as @sql;
user_var=# EXECUTE stmt;
user_var=# \q

-- Delete the database.
gaussdb=# DROP DATABASE user_var;

-- Set B-compatible parameters.
gaussdb=# CREATE DATABASE test_set dbcompatibility 'B';
gaussdb=# \c test_set
test_set=# set b_format_behavior_compat_options = 'enable_set_variables';

-- Set session variables.
test_set=# SET @@codegen_cost_threshold = 10000;
test_set=# SET @@session.codegen_cost_threshold = @@codegen_cost_threshold * 2;

-- Set global variables.
test_set=# \q

-- Delete the database.
gaussdb=# DROP DATABASE test_set;