SET

Function

SET modifies a run-time parameter.

Precautions

Most run-time 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 ::= SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT };

• Set the schema of the table.

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

• Set client encoding.

  Set ::= SET [ SESSION | LOCAL ] NAMES encoding_name;

• Set XML parsing mode.

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

• Set other run-time parameters.

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

Parameter Description

• 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 run-time 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, it will cause the CURRENT_SCHEMA value to be empty.

• SCHEMA schema

  Specifies the current schema. Here the schema is a string.

  Example: set schema 'public';

• NAMES encoding_name

  Specifies the client character encoding. This statement is equivalent to set client_encoding to encoding_name.

  Value range: a valid character encoding name. The run-time parameter corresponding to this option is client_encoding. The default encoding is UTF8.

• XML OPTION option

  Specifies the XML parsing mode.

  Value range: CONTENT (default) and DOCUMENT

• config_parameter

  Specifies the name of a configurable run-time parameter. You can use SHOW ALL to view available run-time parameters.

  NOTE: 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.
mogdb=# SET search_path TO tpcds, public;

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

Helpful Links

RESET and SHOW