Differences Between GaussDB(DWS) and PostgreSQL

Updated on 2024-09-03 GMT+08:00

View PDF

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