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¶
Type | Snytaxes 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:
| For details about the functions supported by GaussDB(DWS), see Functions and Operators. |
Access permission query functions:
| ||
System directory information functions:
| ||
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 the session resources of a database. (Cluster 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 Developer Guide > User-Defined Functions. |