Updated on 2026-10-27 GMT+08:00

GaussDB (PostgreSQL-Compatible Mode)

Setting Compatibility Modes

  1. If the source database type is PostgreSQL, syntax can also be migrated to GaussDB (PostgreSQL-compatible).
  2. Create a PostgreSQL-compatible database.
  1. Log in to the GaussDB instance as a user who has the permission to create databases.
  2. Create a PostgreSQL-compatible GaussDB database.

    create database databasename dbcompatibility = 'PG';

  3. Run the following command. If the value of datcompatibility is PG, the database is created successfully.

    select * from pg_database where datname = 'databasename';

Setting GUC Parameters

For a PostgreSQL-compatible GaussDB database, you can set GUC parameters listed in Table 1 to enhance compatibility.

GaussDB provides many parameters, which determine how the database system works. Before modifying these parameters, you need to understand the impact on databases. Otherwise, unexpected results may occur.

Table 1 GUC parameters of GaussDB (PostgreSQL-compatible mode)

PostgreSQL-Compatible Configuration Item

Compatibility Configuration Item

Description

Supported Database

Enabled

behavior_compat_options

display_leading_zero

Specifies how floating-point numbers are displayed. 0 before the decimal point is displayed.

Centralized/Distributed

Yes

bind_procedure_searchpath

Specifies the search path of the database object in a stored procedure for which no schema name is specified.

Centralized/Distributed

Yes

correct_to_number

Specifies the compatibility of the to_number() result.

Centralized/Distributed

Yes

return_null_string

Specifies how to display the empty result (empty string '') of the lpad() and rpad() functions.

Centralized/Distributed

Yes

allow_procedure_compile_check

Controls the compilation check of the SELECT and OPEN CURSOR statements in a stored procedure.

Centralized/Distributed

Yes

plsql_compile_check_options

plsql_expression_check

Specifies whether to enable the PL/SQL compilation check for GaussDB. If this parameter is set to plsql_expression_check, the system checks whether functions, stored procedures, and packages can be successfully compiled upon creation.

Centralized

Yes

Procedure

Centralized

  1. Connect to the GaussDB database and switch to the GaussDB installation user.

    su - omm

    In the preceding command, omm is an example. Change it to the actual installation user.

  2. Run the following commands to configure the parameters:

    gs_guc reload -Z coordinator -Z datanode -N all -I all -c "behavior_compat_options='display_leading_zero,bind_procedure_searchpath,correct_to_number,return_null_string,allow_procedure_compile_check'";

    gs_guc reload -Z coordinator -Z datanode -N all -I all -c "plsql_compile_check_options='plsql_expression_check'";

    • The preceding GUC parameter settings are applied to migration from PostgreSQL to GaussDB Centralized V2.0-8.200 Enterprise Edition. For GUC parameters of other migration scenarios, replace the content before and after the equal sign (=) in the preceding commands with parameters and values displayed in the pre-check function.
    • After schema migration, change the value (plsql_expression_check) of GUC parameter plsql_compile_check_options to an empty string.

    gs_guc reload -Z coordinator -Z datanode -N all -I all -c "plsql_compile_check_options=''";

Distributed

  1. Connect to the GaussDB database and switch to the GaussDB installation user.

    su - omm

    In the preceding command, omm is an example. Change it to the actual installation user.

  2. Run the following commands to configure the parameters:

    gs_guc reload -Z coordinator -Z datanode -N all -I all -c "behavior_compat_options='display_leading_zero,bind_procedure_searchpath,correct_to_number,return_null_string,allow_procedure_compile_check'";

    The preceding GUC parameter settings are applied to migration from PostgreSQL to GaussDB Distributed V2.0-8.200 Enterprise Edition. For GUC parameters of other migration scenarios, replace the content before and after the equal sign (=) in the preceding commands with parameters and values displayed in the pre-check function.