Differences Between GaussDB(DWS) and PostgreSQL

Differences Between GaussDB(DWS) and PostgreSQL

GaussDB(DWS) is a next-generation data warehouse with virtually unlimited scalability. It is fully compatible with the standard ANSI SQL 99 and SQL 2003, as well as the PostgreSQL and Oracle ecosystems.

PostgreSQL is an open source database system that uses and extends the SQL language combined with many features that safely store and scale complex data workloads.

GaussDB(DWS) is a next-generation data warehouse with virtually unlimited scalability. It is fully compatible with the standard ANSI SQL 99 and SQL 2003, as well as the PostgreSQL and Oracle ecosystems.

PostgreSQL is an open source database system that uses and extends the SQL language combined with many features that safely store and scale complex data workloads.

GaussDB(DWS) gsql and PostgreSQL psql

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.

Activities & Promotions Activities

Free Packages

Register now to take advantage of these incredible deals

Hot
Try Now
Huawei Cloud Blossom Program

Help your business bloom with cloud resource discounts, free trainings, and collaborative marketing opportunities

Hot
Try Now

About GaussDB(DWS) SQL

What Is SQL?

SQL is a standard computer language used to control the access to databases and manage data in databases.

SQL provides different statements to enable you to:

· Query data.

· Insert, update, and delete rows.

· Create, replace, modify, and delete objects.

· Control the access to a database and its objects.

· Maintain the consistency and integrity of a database.

SQL consists of commands and functions that are used to manage databases and database objects. SQL can also forcibly implement the rules for data types, expressions, and texts. Therefore, section "SQL Reference" describes data types, expressions, functions, and operators in addition to SQL syntax.

Supported SQL Standards

GaussDB(DWS) is compatible with Postgres-XC features and supports the major features of SQL2, SQL3, and SQL4 by default.
Supported by GaussDB(DWS)
Not Supported by GaussDB(DWS)

Data Types

· Numeric Types                · Monetary Types             · Boolean Type                     · Character Types

· Binary Data Types           · Date/Time Types             · Geometric Types                · Network Address Types

· Bit String Types               · Text Search Types           · UUID Type                         · JSON Types

· RoaringBitmap                · HLL Data Types              · Object Identifier Types        · Pseudo-Types

· Data Types Supported by Column-Store Tables         · XML

PostgreSQL data types not supported by GaussDB(DWS):

· Lines, a geometric type

· pg_node_tree

Functions

· Logical Operators                                                     · Comparison Operators

· Character Processing Functions and Operators            · Binary String Functions and Operators

· Bit String Functions and Operators                            · Pattern Matching Operators

· Numeric Functions and Operators                             · Date and Time Processing Functions and Operators

· Type Conversion Functions                                       · Geometric Functions and Operators

· Network Address Functions and Operators                · Text Search Functions and Operators

· UUID Functions                                                       · JSON/JSONB Functions and Operators

· Bitmap Functions and Operators                               · HLL Functions and Operators

· SEQUENCE Functions                                               · Array Functions and Operators

· Range Functions and Operators                                · Aggregate Functions

· Window Functions                                                   · Security Functions

· Set Returning Functions                                           · Conditional Expression Functions

· System Information Functions                                   · System Administration Functions

· Backup and Restoration Control Functions                 · Database Object Functions

· Residual File Management Functions                        · Data Redaction Functions

· Statistics Information Functions                                · Trigger Functions

· XML Functions                                                         · Call Stack Recording Functions

PostgreSQL functions not supported by GaussDB(DWS):

Enum support functions

Access privilege inquiry functions

· has_sequence_privilege(user, sequence, privilege)

· has_sequence_privilege(sequence, privilege)

System catalog information functions

· pg_get_triggerdef(trigger_oid)

· pg_get_triggerdef(trigger_oid, pretty_bool)

Line functions

pg_node_tree

FAQs

  • Is GaussDB(DWS) Compatible with PostgreSQL Stored Procedures?

    Yes.

    GaussDB(DWS) is compatible with PostgreSQL stored procedures. For details, see Stored Procedures.

  • Does GaussDB(DWS) Support Third-Party Clients and JDBC and ODBC Drivers?

    Yes, but GaussDB(DWS) clients and drivers are recommended. Unlike open-source PostgreSQL clients and drivers, GaussDB(DWS) clients and drivers have two key advantages:

    · Security hardening: PostgreSQL drivers only support MD5 authentication, but GaussDB(DWS) drivers support SHA256 and MD5.

    · Data type enhancement: GaussDB(DWS) drivers support new data types smalldatetime and tinyint.

    GaussDB(DWS) supports open-source PostgreSQL clients and JDBC and ODBC drivers.

    The compatible client and driver versions are:

    · PostgreSQL psql 9.2.4 or later

    · PostgreSQL JDBC Driver 9.3-1103 or later

    · PSQL ODBC 09.01.0200 or later

    For details about how to use JDBC/ODBC to connect to GaussDB(DWS), see Guide: JDBC- or ODBC-Based Development.

  • What Are the Differences Between GaussDB(DWS) and PostgreSQL?

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

    gsql provides additional functions based on psql.

    Learn more

  • Which PostgreSQL Features Are Unsupported by GaussDB(DWS)?

    · Table inheritance

    · Table creation features:

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

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

    · Define or change the security tag of an object.

    · User-defined C functions

    · Create, modify, and delete operators.

    · Create, modify, and delete operator classes.

    · Create, modify, and delete operator families.

    · Create, modify, and delete text search parsers.

    · Create, modify, and delete text search templates.

    · Create, modify, and delete collations.

    · Create and delete rules.

    · Register, modify, and delete languages.

    · Create, modify, and delete domains.

    · Define, modify, and delete the conversion of character set encoding.

    · Define and delete casts.

    · Define, modify, and delete user mapping.

    · Generate a notification.

    · Listen to a notification.

    · Stop listening to a notification.

    · Load or reload a shared library file.

    · Release the session resources of a database.

    · Move a cursor backward.

    The following features are disabled in GaussDB(DWS) for separation of rights:

    · TO PUBLIC of GRANT

    · COPY FROM FILE and COPY TO FILE of COPY

Learn More About GaussDB(DWS)

Learn the Basics

Explore More
查看更多 收起

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