Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

SET

Updated on 2024-05-07 GMT+08:00

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 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 }}};
  • Set parameters in B-compatible mode (sql_compatibility set to '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 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 available GUC parameters.

    NOTE:

    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.

  • 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.

    NOTE:
    1. SET SESSION is supported only in B-compatible mode (sql_compatibility set to '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`.

    NOTE:
    • User-defined variables can be set only in B-compatible mode (sql_compatibility set to '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 NUMERIC 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.

    NOTE:

    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;
-- 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';

Helpful Links

RESET and SHOW

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback