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 XML parsing mode.

      
           SET [ SESSION | LOCAL ] XML OPTION { DOCUMENT | CONTENT };

Set other GUC parameters.

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

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 this command is executed in a transaction, the effect of the command disappears after 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 this command 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
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 sql_compatibility is set to 'MYSQL', b_format_version set to '5.7', and b_format_dev_version set to 's2'.
  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 MYSQL-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 default value is UTF8.
XML OPTION option
Specifies the XML parsing mode.

Value range: CONTENT (default), DOCUMENT
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.

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;