AnalyticDB:Compatibility comparisons between AnalyticDB for PostgreSQL V4.3 and V6.0

Query optimizers

Both AnalyticDB for PostgreSQL V4.3 and V6.0 support Legacy and ORCA query optimizers. For more information about these query optimizers, see Choose a query optimizer.

Escape characters

• In AnalyticDB for PostgreSQL V6.0, backslashes (\) within strings are not used as escape characters.
• You can execute the following statement to use backslashes (\) as escape characters. However, we recommend that you do not perform this operation.

  set standard_conforming_strings = off;

  Note The preceding statement is available only for sessions. If you want to use this statement for AnalyticDB for PostgreSQL instances, submit a ticket and request technical support of AnalyticDB for PostgreSQL to configure the escape characters based on your requirements.

Data type conversion

• AnalyticDB for PostgreSQL V6.0 does not automatically convert a string of the YYYYMMDDHH24MISS format to a timestamp. To perform such conversion, you can use the to_timestamp/to_char built-in function.
• Compared with V4.3, AnalyticDB for PostgreSQL V6.0 does not implicitly convert numeric data types to TEXT. After you upgrade AnalyticDB for PostgreSQL from V4.3 to V6.0, you must add functions to the original SQL statements to convert numeric data types to TEXT. Examples:

  create or replace function substr(numeric, integer,integer)returns text as $$
  select substr($1::text,$2,$3);
  $$ language sql IMMUTABLE strict;

  create or replace function pg_catalog.btrim(str numeric) returns text as $$
  return $_[0];
  $$ language plperl IMMUTABLE strict;

  create or replace function to_date(timestamp, text) returns date as $$
  select to_date($1::text,$2);
  $$ language sql IMMUTABLE strict;

  create or replace function to_date(integer, text) returns date as $$
  select to_date($1::text,$2);
  $$ language sql IMMUTABLE strict;

• You must manually rewrite the SQL statements or functions in which the data type needs to be implicitly converted to TEXT.

Error logs of external tables

gp_read_error_log('$external_table')
gp_truncate_error_log('$external_table')

Data types

• If you change the storage format of NUMERIC files, the corresponding disk space is affected.
• If you change the MONEY data type from 32-bit to 64-bit, the corresponding disk space is affected.
• In AnalyticDB for PostgreSQL V6.0, you cannot use the following data types for distribution keys:
  • abstime
  • reltime
  • tinterval
  • money
  • anyarray

Keywords

AnalyticDB for PostgreSQL V6.0 adds, modifies, and deletes some keywords. Names of database objects cannot be the same as keywords.

The use of keywords varies between different categories. You can execute the following statement to view all keywords and their categories in both AnalyticDB for PostgreSQL V4.3 and V6.0:

select * from pg_get_keywords();

System tables

Some system tables differentiate between AnalyticDB for PostgreSQL V4.3 and V6.0. If your business logic references the following system tables, you must modify the referenced system tables to avoid errors.

Parameters of built-in functions

AnalyticDB for PostgreSQL V6.0 changes the parameters of some built-in functions.

Other comparisons