Updated on 2025-07-22 GMT+08:00

SET

Description

Modifies GUC parameters.

Precautions

Most GUC parameters can be modified at runtime by using SET, but some parameters cannot be modified after a service 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-side encoding.
    SET [ SESSION | LOCAL ] NAMES encoding_name;
  • 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. If neither SESSION nor LOCAL appears, SESSION is the default value.

    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 effect will persist until the end of the session, unless the parameters are reset by another SET statement.

  • LOCAL

    Specifies that the specified parameters take effect for the current transaction. After COMMIT or ROLLBACK, session-level settings will take effect again.

    The effect of this command lasts only until the end of the current transaction, whether the transaction is 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 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 GUC 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 GUC parameter. You can use SHOW ALL to view the available GUC parameters.

    Some parameters viewed by SHOW ALL cannot be set by using 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.
openGauss=# SET search_path TO tpcds, public;

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

Helpful Links

RESET and SHOW