Compatibility comparisons between AnalyticDB for PostgreSQL V6.0 and V7.0

Data types

CREATE TABLE test(a INT, b UNKNOWN);
ERROR:  COLUMN "b" has pseudo-type UNKNOWN

-- string_agg() only accepts these signatures:
--   string_agg(text, text)
--   string_agg(bytea, bytea)

CREATE TABLE test(a INT, b INT);
SELECT a, string_agg(b, ',') FROM test GROUP BY a;
ERROR:  FUNCTION string_agg(INTEGER, UNKNOWN) does NOT exist

SQL syntax

--- V6.0 multi-value list syntax (supported)
INSERT INTO t1(c1, c2, c3) VALUES (1, 2, 3)
ON CONFLICT(c1) DO UPDATE SET (c2, c3) = (EXCLUDED.c2, EXCLUDED.c3);

--- V7.0 ROW() expression syntax (required)
INSERT INTO t1(c1, c2, c3) VALUES (1, 2, 3)
ON CONFLICT(c1) DO UPDATE SET (c2, c3) = ROW(EXCLUDED.c2, EXCLUDED.c3);

--- V6.0 integer offset for date columns (supported)
SELECT sum(c1) OVER (
    ORDER BY c2 DESC
    RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
);

--- V7.0 explicit interval type (required)
SELECT sum(c1) OVER (
    ORDER BY c2 DESC
    RANGE BETWEEN INTERVAL '1 day' FOLLOWING AND UNBOUNDED FOLLOWING
);

System tables

V7.0 modifies several system tables. If your application logic queries these tables directly, update the queries before upgrading.

Extensions

The following extensions are unavailable in V7.0. Extensions marked as Replaced have a supported alternative — update your code to use the replacement before upgrading. Extensions marked as Temporarily unavailable have no current alternative in V7.0.

Keywords

V7.0 adds, modifies, and removes specific keywords. Database object names cannot conflict with reserved keywords.

To query the keywords and their categories in your instance, run:

SELECT * FROM pg_get_keywords();

Keyword categories:

The following table lists all keyword changes between V6.0 and V7.0.

Functions

Renamed write-ahead log directories and functions

V7.0 renames the write-ahead log (WAL) directory from pg_xlog to pg_wal and the transaction status directory from pg_clog to pg_xact. All xlog references in SQL functions, tools, and options are renamed to wal. Update any scripts or automation that reference these names.

Changed substring() behavior

The SQL-style substring() function now uses standard-compliant greedy behavior. When a pattern matches in multiple ways, the initial sub-pattern matches the smallest possible amount of text rather than the largest. For example, the pattern %#"aa*#"% now selects the first group of a characters from the input instead of the last group.

CASE WHEN statements with Set-Returning Functions (SRFs)

V7.0 enforces stricter type consistency checks in CASE WHEN statements, requiring branch return values to be scalar types. SRFs (functions with a SETOF return type, such as generate_series, regexp_matches, and unnest) must be rewritten to return scalar output or replaced with equivalent logic.

In V6.0, some scenarios allow CASE WHEN branches to return multiple rows directly.

The following examples show common SRF rewrites:

• unnest: Move unnest outside the CASE WHEN block so that each branch returns an array type (scalar).

  --- V6.0 unnest inside CASE WHEN (supported)
  SELECT CASE c1
      WHEN 1 THEN unnest(array[1::bigint, 2::bigint])
      WHEN 3 THEN 4::bigint
      ELSE c1::bigint
  END FROM t1;
  
  --- V7.0 unnest moved outside (required)
  SELECT unnest(
      CASE c1
          WHEN 1 THEN array[1, 2]::bigint[]
          WHEN 3 THEN array[4]::bigint[]
          ELSE array[c1]::bigint[]
      END
  ) FROM t1;

• regexp_matches: For cases where regexp_matches(text, pattern) matches only once, replace it with substring(text from pattern), which returns a scalar string.

  --- V6.0 regexp_matches inside CASE WHEN (supported)
  SELECT CASE WHEN c1 IS NULL THEN NULL
      ELSE regexp_matches(c1, '[0-9]+[.][0-9]+')
  END FROM t1;
  
  --- V7.0 replaced with substring (required)
  SELECT CASE WHEN c1 IS NULL THEN NULL
      ELSE substring(c1 FROM '[0-9]+[.][0-9]+')
  END FROM t1;

• generate_series: Use UNION ALL to split the logic and avoid using SRFs inside CASE WHEN.

  --- V6.0 generate_series inside CASE WHEN (supported)
  SELECT CASE WHEN c1 = 1 THEN 1
      ELSE generate_series(1, 10)
  END AS c2 FROM t1;
  
  --- V7.0 rewritten with UNION ALL (required)
  SELECT 1 AS c2 FROM t1 WHERE c1 = 1
  UNION ALL
  SELECT generate_series(1, 10) FROM t1 WHERE c1 <> 1 OR c1 IS NULL;

Others

• Table name length limit:

  • V7.0 supports table names up to 128 characters, providing a more spacious namespace.

  • V6.0 supports table names up to 64 characters only. Names that exceed this limit are automatically truncated.

  In V6.0, when a table name exceeds 64 characters, the stored name is the truncated version, and queries using the original name are resolved to the truncated name, keeping both sides consistent. If you use the data migration feature to migrate such a table to V7.0, the longer name limit means the original full name cannot access the table stored under the truncated name.

  You can use ALTER TABLE ... RENAME TO to restore the truncated name to the original full name.

  Example:

  --- Table migrated from V6.0 has a truncated name in V7.0
  CREATE TABLE very_long_table_name_that_exceeds_sixty_four_characters_limi (
      c1 INT
  );
  
  --- Rename the truncated table name to the original full name
  ALTER TABLE very_long_table_name_that_exceeds_sixty_four_characters_limi
  RENAME TO very_long_table_name_that_exceeds_sixty_four_characters_limit;

• Password encryption:

  V7.0 uses SHA256 to encrypt database passwords instead of the MD5 algorithm used in V6.0. SHA256 provides stronger security.

  After migrating data to a V7.0 instance, reset all database passwords to apply SHA256 encryption.

What's next

Use the data migration feature to migrate data from a V6.0 instance to a V7.0 instance. For details, see Migrate data between AnalyticDB for PostgreSQL instances.