Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ Differences Between GaussDB(DWS) and PostgreSQL
Updated on 2024-09-03 GMT+08:00
View PDF
Share

Differences Between GaussDB(DWS) and PostgreSQL

The differences between GaussDB(DWS) and PostgreSQL are sorted based on PostgreSQL 9.X. The differences are as follows:

Client Differences

GaussDB(DWS) gsql differs from PostgreSQL psql in that the former has made the following changes to enhance security:

  • User passwords cannot be set by running the \password meta-command.
  • The \i+, \ir+, and \include_relative+ meta-commands and the input and output parameter -k are added to encrypt imported and exported files.
  • Historical command lines cannot be printed to files using the \s meta-command.
  • SQL statements related to sensitive operations, such as those containing passwords, are not recorded. Users cannot see such records when they turn pages or press up or down arrow keys to view the SQL history.
  • After a connection is set up, a message is displayed to inform users of password expiration and to show version information.

gsql provides the following additional functions based on psql:

  • The output format parameter -r is added to allow you to adjust the focus by pressing the Tab key or arrow keys when entering commands.
  • The \parallel meta-command is added to improve execution performance.
  • The \set RETRY meta-command is added to support retry upon statement errors.
  • Slashes (/) are used as the default terminator at the end of PL/SQL statements CREATE OR REPLACE FUNCTION/PROCEDURE.

libpq:

During the development of certain GaussDB(DWS) functions such as the gsql client connection tool, PostgreSQL libpq is greatly modified. However, the libpq interface is not verified in application development. You are not advised to use this set of APIs for application development, because underlying risks probably exist. You can use the ODBC or JDBC APIs instead.

SQL Statement Differences

Table 1 PostgreSQL syntaxes not supported by GaussDB(DWS)

Type

Syntaxes Not Supported by GaussDB(DWS)

Description

Data

Geometric lines

For details about the data types supported by GaussDB(DWS), see Data Types.

pg_node_tree

Functions

Enumeration functions:

  • enum_first(anyenum)
  • enum_last(anyenum)
  • enum_range(anyenum)
  • enum_range(anyenum, anyenum)

For details about the functions supported by GaussDB(DWS), see Functions and Operators.

Access permission query functions:

  • has_sequence_privilege(user, sequence, privilege)
  • has_sequence_privilege(sequence, privilege)
System directory information functions:
  • pg_get_triggerdef(trigger_oid)
  • pg_get_triggerdef(trigger_oid, pretty_bool)

Geometric type conversion function:

line(point, point)

pg_node_tree

SQL syntax

CREATE TABLE clause:

INHERITS ( parent_table [, ... ] )

Inherits tables.

CREATE TABLE column constraints:

REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ]

Use REFERENCES reftable [ (refcolumn) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] to create a foreign key constraint for a table.

CREATE TABLE table constraints:

EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] )

Use EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) to create exclusion constraints for a table.

CREATE/ALTER/DROP EXTENSION

Loads, modifies, and deletes extensions.

CREATE/ALTER/DROP AGGREGATE

Defines, modifies, and deletes aggregate functions.

CREATE/ALTER/DROP OPERATOR

Creates, modifies, and deletes operators.

CREATE/ALTER/DROP OPERATOR CLASS

Creates, modifies, and deletes operator classes.

CREATE/ALTER/DROP OPERATOR FAMILY

Creates, modifies, and deletes operator families.

CREATE/ALTER/DROP TEXT SEARCH PARSER

Creates, modifies, and deletes text search parsers.

CREATE/ALTER/DROP TEXT SEARCH TEMPLATE

Create, modify, and delete text search templates.

CREATE/ALTER/DROP COLLATION

Creates, modifies, and deletes collation rules.

CREATE/ALTER/DROP CONVERSION

Defines, modifies, and deletes the conversion of character set encoding.

CREATE/ALTER/DROP RULE

Creates, modifies, and deletes rules.

CREATE/ALTER/DROP LANGUAGE

Registers, modifies, and deletes procedural languages (LANGUAGE).

CREATE/ALTER/DROP DOMAIN

Creates, modifies, and deletes domains.

CREATE/DROP CAST

Defines and deletes casts.

CREATE/ALTER/DROP USER MAPPING

Defines, modifies, and deletes user mapping.

SECURITY LABEL

Defines or changes the security tag of an object.

NOTIFY

Generates a notification.

LISTEN

Listens to a notification.

UNLISTEN

Stops listening to a notification.

LOAD

Loads or reloads a shared library file.

DISCARD

Releases session resources of a database. (Clusters of 8.2.0 and later versions support DISCARD.)

MOVE BACKWARD

Moves a cursor backward.

COPY FROM FILE and COPY TO FILE

To isolate permissions, COPY FROM FILE and COPY TO FILE is disabled in GaussDB(DWS).

Other

User-defined C functions

For details about user-defined functions supported by GaussDB(DWS), see User-Defined Functions.